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 ""
Evaluation Several Conditions



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
Thanks for the solution, I have attached the results, it seems it is not catching the value of "RA" several of the rows just say false instead of "RA"

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

Re: Evaluation Several Conditions
Columns U and S are blank in that workbook so I can't see where RA would apply.


Re: Evaluation Several Conditions
Here is the sample book with correct columns. The yellow area are what it should have returned correctly.

Re: Evaluation Several Conditions
Hello, I have added the Headers to the Columns, is that what you needed.

Re: Evaluation Several Conditions
I think you'll need to restate the conditions more clearly in plain English, rather than as a pseudo formula.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!