Avatar of sandramac
sandramac
 asked on

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 ""
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
sandramac

8/22/2022 - Mon
Jeff Darling

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

Jeff Darling

Sample attached
EE_ComplicatedDecision.xlsm
sandramac

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeff Darling

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

Jeff Darling

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

sandramac

ASKER
Getting some errors in the function it is not displaying some of the values  Attached is a sample sheet
Sample.xlsx
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sandramac

ASKER
Do you have any suggestions on why it is not catching all the values.
Jeff Darling

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.
sandramac

ASKER
I have attached a more complete sample, Still getting some error, should be seeing more "-RA"  Thank You
Sample2.xlsm
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jeff Darling

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
sandramac

ASKER
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.
sandramac

ASKER
Still not working, I have attached the workbook, the three cells in yellow should be -TSRA, not -RA
Sample03.xlsm
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Jeff Darling

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
sandramac

ASKER
I think that worked.  Testing it out now.
sandramac

ASKER
Thank You So Much