IF Formula does not return corresponding "true" value

New Contributor


I want my formula to return "Every 5 Years" if my logical test is true and "Annually" if my logical test is false.

But instead of returning "Every 5 Years" it defaults to returning "FALSE", how do I change this?

4 Replies
Post few of your sample data then show desired output.



Please post the entire formula, at the very least.


Presumably, the formula is, in part:


=IF(B6<6, IF(B7<6, IF(B8<6, "Every 5 Years", "Annually....)....), "Annually")


But obviously, we cannot see the part where I wrote "....".


So we cannot explain why the formula returns FALSE, except to state the obvious, namely:  presumably, at least one of the nested IF's is missing a "value if false" part.


@Joe User  here is my entire formula and spreadsheet:



I have cell C8 selected; it is supposed to be returning "Annually" but instead it returned "False"



So if Excel considers B6 > 6 and B7 > 6, the formula returns FALSE because you do not have a "value if false" part for the expression
IF(B7<6, IF(....) [, missing value-if-false part here]).


Perhaps you want:
IF(B6<6, IF(B7<6, IF(...), "Annually"), "Annually")


or more simply:

IF(AND(B6<6, B7<6, B8<6), "Once Every 5 Years", "Annually")




My guess is:  B6 is text, not numeric.  Confirm with =ISTEXT(B6).  Looks can be deceiving, and the format of the cell does not matter.


It is also possible that B7 is text.  Again, confirm with =ISTEXT(B7).  But even if B7 is numeric, it appears to be the number 7.  So B7<6 would indeed be false.


(Excel considers all text to be greater than any numeric value.)