Forum Discussion
IF Function
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
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
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.
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
- 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.Hello,
the IF-function has a max number of three arguments: IF(Condition, Condition_Is_True, Condition_Is_False). So, if you use more than 3 arguments in the IF-Function, you'll get the error you experienced.
For nesting IF's, you should do something like this: =IF(Condition_1,Condition_1_Is_True, IF(Condition_2,Condition_2_Is_True, IF(Condition_3,Condition_3_Is_True,None_Of_Conditions_Are_Met))). Note the brackets which ensure that each IF has 3 arguments.
The condition in your formula is to check is COUNTIF returns a value greater than zero. Also, COUNTIF has some arguments where you have to apply some rules: =COUNTIF(Where do you want to look?, What do you want to look for?). So, you can't write COUNTIF((D4,D33)="NO". This will not work. You should write COUNTIF(D4:D33,"NO") > 0 in case you would check the full range.
Please have a look in the online help for both functions:
https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
Now, you would like to apply the formula only to two single cells which are not in a contineous range. In this case, there is no need to use COUNTIF, you can directly compare the cell value to the searched values. As you said, that if any occurence of "NO" leads to "RED", you can use an OR-Statement. Here a sample: =IF(OR(D4="NO",D33="NO"),"RED","OTHER_CHECKS")
For checking more conditions you can use: =IF(OR(D4="NO",D33="NO"),"RED", IF(OR(D4="YES",D33="YES"),"GREEN", IF(OR(D4="PARTIAL",D33="PARTIAL"),"AMBER","NA"))) In this case, the fomula checks for NO first. If at least one cell has a "NO", then it returns "RED". It does not matter, if the other cell has the value "X", "YES" or something else. If no "NO" are found, the formula checks for "YES", and so on.Best,
Mourad