Complicated If Formula

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 ""
sandramacAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
I think this is what you are going for.  I tried doing it as a worksheet function, but got really buggered as I accidentally kept botching the command and had to start over.  Is a VBA solution acceptable?  

SAMPLE
Public Function ComplicatedDecision(S2, X2, U2)

Dim sOutput As String

If X2 = "LIQUID" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSRA"
        If U2 > 0.3 Then sOutput = "+TSRA"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-RA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "RA"
        If U2 > 0.3 Then sOutput = "+RA"
    End If
End If

If X2 = "FROZEN" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSSN"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSSN"
        If U2 > 0.3 Then sOutput = "+TSSN"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSRA"
        If U2 > 0.3 Then sOutput = "+TSRA"
    End If
End If
  
If X2 = "FREEZING" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSFZRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSFZRA"
        If U2 > 0.3 Then sOutput = "+TSFZRA"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-FZRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "FZRA"
        If U2 > 0.3 Then sOutput = "+FZRA"
    End If
End If

If S2 < 0.01 Then
 sOutput = ""
End If

ComplicatedDecision = sOutput

End Function

Open in new window

0
Jeff DarlingDeveloper AnalystCommented:
Sample attached
EE_ComplicatedDecision.xlsm
0
sandramacAuthor Commented:
Thanks for the solution, the only thing I was going with the formula is I need to do this same evaluation from row 2 to 47, I would need the output in column z, how would I adjust the function for that.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Jeff DarlingDeveloper AnalystCommented:
The function call expects 3 parameters.  You can put the output in any column.

ComplicatedDecision(S2, X2, U2)

If S2, X2 and U2 are in B5,C5 and D5 then put this in column Z.  then you can copy paste the function call.

=complicatedDecision(B5,C5,D5)

Open in new window

0
Jeff DarlingDeveloper AnalystCommented:
I just noticed I had the output incorrect for FROZEN

correction

Public Function ComplicatedDecision(S2, X2, U2)

Dim sOutput As String

If X2 = "LIQUID" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSRA"
        If U2 > 0.3 Then sOutput = "+TSRA"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-RA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "RA"
        If U2 > 0.3 Then sOutput = "+RA"
    End If
End If

If X2 = "FROZEN" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSSN"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSSN"
        If U2 > 0.3 Then sOutput = "+TSSN"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-SN"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "SN"
        If U2 > 0.3 Then sOutput = "+SN"
    End If
End If
  
If X2 = "FREEZING" Then
    If S2 > 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSFZRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSFZRA"
        If U2 > 0.3 Then sOutput = "+TSFZRA"
    Else
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-FZRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "FZRA"
        If U2 > 0.3 Then sOutput = "+FZRA"
    End If
End If

If S2 < 0.01 Then
 sOutput = ""
End If

ComplicatedDecision = sOutput

End Function

Open in new window

0
sandramacAuthor Commented:
Getting some errors in the function it is not displaying some of the values  Attached is a sample sheet
Sample.xlsx
0
sandramacAuthor Commented:
Do you have any suggestions on why it is not catching all the values.
0
Jeff DarlingDeveloper AnalystCommented:
The spreadsheet you gave does not have the macro function.

Which columns correspond to S2, X2 and U2?

ComplicatedDecision(S2, X2, U2)

I call tell that X2 is column E because it contains LIQUID, but not sure about the others.

If all the parameters are not specified, then the result is blank.
0
sandramacAuthor Commented:
I have attached a more complete sample, Still getting some error, should be seeing more "-RA"  Thank You
Sample2.xlsm
0
Jeff DarlingDeveloper AnalystCommented:
I noticed that you changed the U2 and S2 if statements.

since that is what you are needing, then I think the first S2 if statement needs to be less than instead of greater than.

Public Function ComplicatedDecision(S2, X2, U2)

Dim sOutput As String

If X2 = "LIQUID" Then

    If S2 < 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSRA"
        If U2 > 0.3 Then sOutput = "+TSRA"
        Else
        If S2 >= 0.01 And S2 <= 0.1 Then sOutput = "-RA"
        If S2 >= 0.1 And S2 <= 0.3 Then sOutput = "RA"
        If S2 > 0.3 Then sOutput = "+RA"
    End If
End If

If X2 = "FROZEN" Then
    If S2 < 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSSN"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSSN"
        If U2 > 0.3 Then sOutput = "+TSSN"
    Else
        If S2 >= 0.01 And S2 <= 0.1 Then sOutput = "-SN"
        If S2 >= 0.1 And S2 <= 0.3 Then sOutput = "SN"
        If S2 > 0.3 Then sOutput = "+SN"
    End If
End If
  
If X2 = "FREEZING" Then
    If S2 < 0.01 Then
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSFZRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSFZRA"
        If U2 > 0.3 Then sOutput = "+TSFZRA"
    Else
        If S2 >= 0.01 And S2 <= 0.1 Then sOutput = "-FZRA"
        If S2 >= 0.1 And S2 <= 0.3 Then sOutput = "FZRA"
        If S2 > 0.3 Then sOutput = "+FZRA"
    End If
End If

If S2 < 0.01 Then
 sOutput = ""
End If

ComplicatedDecision = sOutput

End Function

Open in new window

Sample03.xlsm
0
sandramacAuthor Commented:
I was playing with the function to see if I was able to get it to do what I need it to do.  I will try your recommendation.  Thanks.
0
sandramacAuthor Commented:
Still not working, I have attached the workbook, the three cells in yellow should be -TSRA, not -RA
Sample03.xlsm
0
Jeff DarlingDeveloper AnalystCommented:
If those are -TSRA then all the others will be blank.  I think we have some specification problems here.

It seems that if U2 meets the conditions, then you want a TSRA type otherwise if S2 meets the conditions an RA Type.

I don't know what TSRA or RA mean, I'm just trying to interpret the specs you have given and produce code that meets the conditions.

Here is another approach using a true false boolean to toggle where the RA test is done.

If X2 = "LIQUID" Then

        sOutput = ""
        bRule1 = False
   
        If U2 >= 0.01 And U2 <= 0.1 Then sOutput = "-TSRA"
        If U2 >= 0.1 And U2 <= 0.3 Then sOutput = "TSRA"
        If U2 > 0.3 Then sOutput = "+TSRA"
        
        If sOutput = "" Then bRule1 = True
   
        If bRule1 = True Then
          If S2 >= 0.01 And S2 <= 0.1 Then sOutput = "-RA"
          If S2 >= 0.1 And S2 <= 0.3 Then sOutput = "RA"
          If S2 > 0.3 Then sOutput = "+RA"
        End If
        
End If

Open in new window

Sample04.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sandramacAuthor Commented:
I think that worked.  Testing it out now.
0
sandramacAuthor Commented:
Thank You So Much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.