Forum Discussion
IF Function
How to use a IF function to validate Text data in a range of cells? Please help.
Ideally, would like to get a result interms of a cell color 'RED' if either of the cell contains "NO"; 'AMBER' if either of the cell contains "PARTIAL" and 'GREEN' if either of the cell contains "YES".
Tried using below formula but could not get the result. Please help me with the nested IF function.
=IF(B2:B3="NO","NO","NA")
| Time | Cost |
| NO | NR |
| YES | NO |
Hello,
the IF-function can only be applied on a range as an array formula, e.g. {=IF(B2:B3="NO","NO","NA")} Note: for entering an array formula, enter =IF(B2:B3="NO","NO","NA") to the formula bar first and then press Ctrl+Shift+Enter. The brackets are automatically set by Excel.
However, simplier than an array formula would be to use the COUNTIF function, which you can use to search text in a range, check its result and then use the IF-function to validate. Please enter for example into cell C2 the formula:
=IF(COUNTIF(B2:B3,"NO")>0,"NO","NA").
If COUNTIF returns a value > 0 in this formula, then at least one of the cells in B2:B3 is set to NO. For nesting your three cases, you can use a formula such as this one:
=IF(COUNTIF(B2:B3,"NO")>0,"NO",IF(COUNTIF(B2:B3,"YES")>0,"YES",IF(COUNTIF(B2:B3,"PARTIAL")>0,"PARTIAL","NA")))
For setting the colors, you can use a conditional format, which sets the color based on the result of the nested formula.
Best regards,
Mourad
6 Replies
Hello,
the IF-function can only be applied on a range as an array formula, e.g. {=IF(B2:B3="NO","NO","NA")} Note: for entering an array formula, enter =IF(B2:B3="NO","NO","NA") to the formula bar first and then press Ctrl+Shift+Enter. The brackets are automatically set by Excel.
However, simplier than an array formula would be to use the COUNTIF function, which you can use to search text in a range, check its result and then use the IF-function to validate. Please enter for example into cell C2 the formula:
=IF(COUNTIF(B2:B3,"NO")>0,"NO","NA").
If COUNTIF returns a value > 0 in this formula, then at least one of the cells in B2:B3 is set to NO. For nesting your three cases, you can use a formula such as this one:
=IF(COUNTIF(B2:B3,"NO")>0,"NO",IF(COUNTIF(B2:B3,"YES")>0,"YES",IF(COUNTIF(B2:B3,"PARTIAL")>0,"PARTIAL","NA")))
For setting the colors, you can use a conditional format, which sets the color based on the result of the nested formula.
Best regards,
Mourad
- Thanks for your quick reply.
Also, when B2=No and B3=Yes, this seems to be failed as I want to write the formula where if B2:B3 has even one occurrence of "No" should result as "RED".
Pl help.
