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 ""

Sample attached

EE_ComplicatedDecision.xlsm

EE_ComplicatedDecision.xlsm

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.

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(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)`

I just noticed I had the output incorrect for FROZEN

correction

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
```

ASKER

Getting some errors in the function it is not displaying some of the values Attached is a sample sheet

Sample.xlsx

Sample.xlsx

ASKER

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

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.

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.

ASKER

I have attached a more complete sample, Still getting some error, should be seeing more "-RA" Thank You

Sample2.xlsm

Sample2.xlsm

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.

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
```

Sample03.xlsm
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.

ASKER

Still not working, I have attached the workbook, the three cells in yellow should be -TSRA, not -RA

Sample03.xlsm

Sample03.xlsm

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

I think that worked. Testing it out now.

ASKER

Thank You So Much

Open in new window