IF Formula does not return corresponding "true" value

New Contributor

thorsonm_0-1660088539909.png

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.

@thorsonm 

 

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:

 

thorsonm_0-1660177941888.png

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

@thorsonm 

 

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")

 

Nevertheless....

 

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