Forum Discussion

Copper Contributor

Apr 10, 2017

Solved

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 ...

Formulas & Functions

Need Help

Deleted
Apr 10, 2017
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

Deleted

Apr 10, 2017

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

Pravin Biradar
Copper Contributor
Apr 10, 2017
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.
- Deleted
  Apr 10, 2017
  Hello,
  
  I uploaded a sample file for you. I assume that you have an extra cell, where you apply the formula and this cell should be red, green, amber.
  
  Best,
  Mourad
  pravin-biradar.xlsx10 KB
  - Pravin Biradar
    Copper Contributor
    Apr 10, 2017
    Thanks!
    
    One more quick query.
    
    If I want to specify D4 and D33, two different cells in a range, and apply the same logic; how would it be?
    
    For eg:
    Tried using below formula but gives error- too many arguments for this function
    
    =IF(COUNTIF((D4,D33)="NO","RED",IF(COUNTIF((D4,D33),"PARTIAL","AMBER",IF(COUNTIF((D4,D33),"YES","GREEN","NA")))
    
    If any of the D4 or D33 cell value is "NO" then it should result in "RED" and so on...
    
    Pl help.