We help IT Professionals succeed at work.

Complicated If Formula

119 Views
Last Modified: 2018-02-02
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 ""
Comment
Watch Question

Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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 DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
Sample attached
EE_ComplicatedDecision.xlsm

Author

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.
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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 DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Getting some errors in the function it is not displaying some of the values  Attached is a sample sheet
Sample.xlsx

Author

Commented:
Do you have any suggestions on why it is not catching all the values.
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
I have attached a more complete sample, Still getting some error, should be seeing more "-RA"  Thank You
Sample2.xlsm
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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

Author

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.

Author

Commented:
Still not working, I have attached the workbook, the three cells in yellow should be -TSRA, not -RA
Sample03.xlsm
Developer Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I think that worked.  Testing it out now.

Author

Commented:
Thank You So Much

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions