 # 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

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"

## Files

• 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

Here is the sample book with correct columns. The yellow area are what it should have returned correctly.

## Files

• Re: Evaluation Several Conditions

Hello, I have added the Headers to the Columns, is that what you needed.

## Files

• 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!