Hi there
Please can someone advise the best way of having a cell automatically turn true or false depending on other cells.
I need cell A5 to contain TRUE, FALSE or a nil value
If cells B5:D5 contain NA or Yes, I need cell A5 to state TRUE
If any of cells B5:D5 contain NO, then I need cell A5 to contain FALSE
Many thanks
how about =IF(COUNTIF(B5:D2,"na")+COUNTIF(B5:D5,TRUE)>0,TRUE,FALSE)
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
hi there - unfortunately that does not quite work
The default value needs to be blank
If YES or NA on all cells, then the value needs to be true
If any cells contain NO then regardless of the other values it needs to be FALSE
please see example of the combinations and the answer would need a formula to come back with - many thanks
This formula returns the results that you have indicated for the criteria in your example. I'm not sure if there are more combinations possible that you haven't shown.
Formula:
=IF(AND(B5="",C5="",D5=""),"",IF(AND(COUNTIF(B5:D5,"Yes")>=1,COUNTIF(B5:D5,"")>=1),"",IF(COUNTIF(B5:D5,"Yes")+COUNTIF(B5:D5,"NA")=3,TRUE,FALSE)))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Many thanks - that all appears to work!
You're welcome. Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi there - Sorry I need a slight adjustment to this, The TRUE/FALSE needs to go in D4, butt heer are more criteria from D9:d14, and simply changing the cell codes does not seem to work.
Can you upload another workbook with a mock-up with the new requirements? Indicate the anticipated answer where you want the answers to be.
If possible rather than TRUE/FALSE, can I have PASS/FAIL??
Thanks so much in advance, it is much appreciated
And a nice learning curve!
I take it that you want to evaluate the columns from row 9 to row14 and have the evaluation in row 4 from D4:J4
Enter this in D4 and fill across
Formula:=IF(COUNTIF(D9:D14,"-")=6,"",IF(COUNTIF(D9:D14,"Yes")+COUNTIF(D9:D14,"NA")=6,"Pass","Fail"))
Pass Fail Fail Pass Pass Fail q1. yes Yes No Yes Yes - No q2 yes Yes Yes NA Yes - no q3 yes Yes Yes NA NA - no q4 yes Yes No NA Yes - no q5 yes Yes No NA YES - no q6 yes No No NA YES - no
Apologies, almost perfect, just one scenario I omitted. If any cells in D9:D14 contain a " - " then I need D5 to default to " - "
** DOES NOT WORK, AND CANNOT DELETE MY OWN POST! **
Dont worry, just solved it I think ... =IF(COUNTIF(M9:M14,"-")>0,"",IF(COUNTIF(M9:M14,"Yes")+COUNTIF(M9:M14,"NA")=6,"Pass","Fail"))
Originally Posted by surk
Last edited by surk; 01-18-2016 at 04:00 PM.
This will put the - in for columns with -Formula:
=IF(COUNTIF(D9:D14,"-")=6,"-",IF(COUNTIF(D9:D14,"Yes")+COUNTIF(D9:D14,"NA")=6,"Pass","Fail"))
I have uploaded an updated book version with your formulas in row 4, and what I need the answers to be in row 3
Many thanks
This might be "sneaking up" on the answer
Formula:=IF(COUNTIF(D9:D14,"-")>=5,"-",IF(AND(COUNTIF(D9:D14,"-")=3,COUNTIF(D9:D14,"Yes")+COUNTIF(D9:D14,"NA")=3),"-",IF(COUNTIF(D9:D14,"Yes")+COUNTIF(D9:D14,"NA")=6,"Pass","Fail")))
yes Yes No Yes Yes - No - yes - no Yes yes no yes Yes Yes NA Yes - no - na - yes - yes no yes Yes Yes NA NA - no - na - yes - - - yes Yes No NA Yes - no - na yes yes - - - yes Yes No NA YES - no - na - yes - - - yes No No NA YES - no - na - yes - NA -
Brilliant, I think that solves all possibilities
Thank you for the feedback.
How about this formula?
=IF(COUNTIF(D9:D14;"no")>0;"Fail";IF(COUNTIF(D9:D14;"-")>0;"-";"Pass"))
Last edited by popipipo; 01-18-2016 at 05:21 PM. Reason: attachment
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks