Forum Discussion

thorsonm's avatar
thorsonm
Copper Contributor
Aug 09, 2022

IF Formula does not return corresponding "true" value

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?

excel
Formulas and Functions

4 Replies

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Aug 10, 2022

    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.

     

    • thorsonm's avatar
      thorsonm
      Copper Contributor
      Aug 11, 2022

      JoeUser2004  here is my entire formula and spreadsheet:

       

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

      • JoeUser2004's avatar
        JoeUser2004
        Bronze Contributor
        Aug 11, 2022

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

Resources