Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Nov 13, 2023

IF FORMULA - Help Needed please

Hi   Im trying to write an IF formula and its erroring    Basically I this formula worked but i needed the 'not completed' part added!   =IF(L4>TODAY(),"Compliant", IF(P4>TODAY(),"Pending",...
Excel
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor

SGeorgie Seeing that image does not allay my questions about the basic design....that aside, I'm wondering if you could just employ the IFS function, rather than nesting IFs several levels deep. The hyperlink here will take you to a resource that describes IFS.

SGeorgie's avatar
SGeorgie
Brass Contributor
Nov 14, 2023
i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple
  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 14, 2023

    SGeorgie 

    i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple

     

    Those are only examples, not the only possible ways to construct the conditions in an IFS formula.

     

    The key point, as contrasted with IF, is that in IFS you have to enter the conditions in an order/sequence in recognition of the fact that:

    • The IFS function does not go through each and every condition.
    • The IFS function stops evaluating as soon as it meets a condition/consequence in which the condition is satisfied. 

    Also important, once you've got it working, IFS is a lot more "readable" to the human eye and brain, than a deeply nested set of IF conditions.

     

    You can easily construct something like

    =IFS(AND(condition1,condition2),consequenceA,OR(condition3,condition4),consequenceB,.......)

    making it as complicated with those AND / OR combinations as the situation warrants.

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 14, 2023
      so my formula which works for the most part is

      =IFS(OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",OR(K4>=TODAY()),"Compliant",OR(AND(K4<TODAY(),OR(O4<TODAY(),ISBLANK(O4)),OR(U4<TODAY(),ISBLANK(U4)))),"Expired",OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",OR(AND(K4<TODAY(),U4>=TODAY())),"Pending")

      but it not showing Expired for any row
      and if there is a completed expiry date in the past AND a pending in the future it says Compliant but it should say Pending

      Please can you help get this formula right Mathetes and/or Oliver
      • mathetes's avatar
        mathetes
        Silver Contributor
        Nov 14, 2023

        SGeorgie 

         

        so my formula which works for the most part is

        but it not showing Expired for any row
        and if there is a completed expiry date in the past AND a pending in the future it says Compliant but it should say Pending

         

        I would say, based on those concluding lines, that it's NOT working for the most part.

         

        I truly don't have time to go through in depth and try to reconstruct. What I'd recommend is that you take each condition separately and make sure it is working before you string them together.

        That is to say, look at each line in the formula as it's arrayed below. Work on each one separately, each in its own column. Combine them after each one is working.

        I can tell you just looking at that first line under IFS, the one I've underlined, where you've put an AND condition inside an OR, that the OR is not working. Have you learned to count the pairs of parentheses? Count 1 for each open paren, subtract 1 for each closing paren. The count goes back to zero before any alternative (OR) condition is described..and then there's the "Not Completed" consequence.

        =IFS(

        OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",

        OR(K4>=TODAY()),"Compliant",

        OR(AND(K4<TODAY(),OR(O4<TODAY(),ISBLANK(O4)),OR(U4<TODAY(),ISBLANK(U4)))),"Expired",

        OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",

        OR(AND(K4<TODAY(),U4>=TODAY())),"Pending"

        )

Resources