Aug 09 2022 04:45 PM
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?
Aug 09 2022 07:10 PM
Aug 09 2022 11:43 PM
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.
Aug 10 2022 05:33 PM
@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"
Aug 10 2022 08:23 PM - edited Aug 10 2022 08:26 PM
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.)