Evaluation Several Conditions

    I have an Excel sheet trying to build a formula for the following. If s2>.01 then if x2="LIQUID" then if u2>=.01<=.1, "-TSRA", if u2>.1<.3,"TSRA", if u2>.3,"+TSRA", else if s2>=.01<.1,"-RA",s2>.1<=.3,"RA",s2>.3,"+RA", if x2="FROZEN" then if u2>=.01<=.1, "-TSSN", if u2>.1<.3,"TSSN", if u2>.3,"+TSSN", else if s2>=.01<.1,"-SN",s2>.1<=.3,"SN",s2>.3,"+SN", if x2="FREEZING" then if u2>=.01<=.1, "-TSFZRA", if u2>.1<.3,"TSFZRA", if u2>.3,"+TSFZRA", else if s2>=.01<.1,"-FZRA",s2>.1<=.3,"FZRA",s2>.3,"+FZRA", else if s2<.01, then ""

    Re: Evaluation Several Conditions


    Hello,
    the formula will be
    [COLOR="#0000CD"]=IF(S2>0.1,IF( X2="LIQUID",IF(AND(U2>=0.01,U2<=0.1),"-TSRA",IF(AND(U2>=0.1,U2<=0.3),"TSRA",IF(U2<0.3,"+TSRA",IF(AND(S2>=0.01,S2<=0.1),"-RA",IF(AND(S2>=0.1,S2<=0.3),"RA",IF(U2>0.3,"+RA","")))))),IF( X2="FROZEN",IF(AND(U2>=0.01,U2<=0.1),"-TSSN",IF(AND(U2>=0.1,U2<=0.3),"TSSN",IF(U2>0.3,"+TSSN",IF(AND(S2>=0.01,S2<=0.1),"-SN",IF(AND(S2>=0.1,S2<=0.3),"SN",IF(S2>0.3,"+SN","")))))),IF( X2="FREEZING",IF(AND(U2>=0.01,U2<=0.1),"-TSFZRA",IF(AND(U2>=0.1,U2<=0.3),"TSFZRA",IF(U2>0.3,"+TSFZRA",IF(AND(S2>=0.01,S2<=0.1),"-FZRA",IF(AND(S2>=0.1,S2<=0.3),"FZRA",IF(S2>0.3,"+FZRA","")))))),""))))[/COLOR]
    better and easier to manage if you make a loop up table

    Re: Evaluation Several Conditions


    Hello,


    Pike is absolutely right ... :wink:


    Whenever you are facing a great deal of Ifs ... you are much better off with a LookUp table ... with which you can more explicitly monitor your rules ...


    Could you create yours in your sample workbook ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Evaluation Several Conditions


    Columns U and S are blank in that workbook so I can't see where -RA would apply.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Evaluation Several Conditions


    I think you'll need to restate the conditions more clearly in plain English, rather than as a pseudo formula.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login