Solved: Need a Function or Vlookup to replace Nested If

[GoKats78]

My nested "If" has far too many variables to work (far exceeds the alloted 7)...and I am not sure this does everything anyway...

=IF(ISBLANK(B14),"",IF(B5=AA1,OR(B5=AA2,OR(B5=AA3,IF(B50>=1.67,AB8,IF(B50>= 133,AB9,IF(B50<1.33,AB10,if(b5=AA4,or(b5=aa5,if(b50=>1.33,AB8,if(b50=>1.00, K38,if(b50<1.00,AB10,AB11)))))))

I have 5 rannkings (S, R, A, B, and C) and 3 possible results in each option.


B5 is cell where user chooses the rank from a dropdown

AA1 = S
AA2 = R
AA3 = A
AA4 = B
AA5 = C

AB8 = Approved
AB9 = SPC must be used to control the process
AB10 = 100% Inspection Required
AB11 = Not Approved


What I have is a process capability spreadsheet.
The ranking are "special characteristic rankings and the results are Cpk numbers.

If the rank is S, R, or A, and the Cpk is >1.67 process is approved;
If the Cpk is between 1.67 and 1.33 the process must use SPC to control the process;
If the Cpk is <1.33 100% inspection must be used

If the ranks are B or C and the Cpk is >1.33 process is approved;
If the Cpk is between 1.33 and 1.00 the process must use SPC to control the process;
If the Cpk is <1.00 100% inspection must be used.

What is the best way to get this done?

[Bob Phillips]

Try

=IF(ISBLANK(B14),AB11,
IF(OR(B5=AA1,B5=AA2,B5=AA3),IF(B50>=1.67,AB8,IF(B50>=133,AB9,AB10)),
IF(OR(B5=AA4,B5=AA5),IF(B50>=1.33,AB8,IF(B50>=1,AB9,AB10)))))

[GoKats78]

xld...too many "Ifs"

Here is what I ended up using:

 
Function RankCpk(sRank As Range, cpk As Range) As String
    Select Case UCase(sRank)
        Case "S", "R", "A"
            If cpk < 1.33 Then
                RankCpk = "100% Inspection Required"
            ElseIf cpk <= 1.33 Then
                RankCpk = "SPC must be used to control the process"
            ElseIf cpk >= 1.67 Then
                RankCpk = "Approved"
            End If
        Case "B", "C"
            If cpk < 1# Then
                RankCpk = "100% Inspection required"
            ElseIf cpk < 1.33 Then
                RankCpk = "SPC must be used to control the process"
            ElseIf cpk >= 1.33 Then
                RankCpk = "Approved"
            End If
        Case Else
            RankCpk = "Char. Rank Not Specified"
    End Select
End Function

[mdmackillop]

What about Case A, cpk = 1.5?

[Bob Phillips]

xld...too many "Ifs"

Here is what I ended up using:

 
Function RankCpk(sRank As Range, cpk As Range) As String
    Select Case UCase(sRank)
        Case "S", "R", "A"
            If cpk < 1.33 Then
                RankCpk = "100% Inspection Required"
            ElseIf cpk <= 1.33 Then
                RankCpk = "SPC must be used to control the process"
            ElseIf cpk >= 1.67 Then
                RankCpk = "Approved"
            End If
        Case "B", "C"
            If cpk < 1# Then
                RankCpk = "100% Inspection required"
            ElseIf cpk < 1.33 Then
                RankCpk = "SPC must be used to control the process"
            ElseIf cpk >= 1.33 Then
                RankCpk = "Approved"
            End If
        Case Else
            RankCpk = "Char. Rank Not Specified"
    End Select
End Function

You jest surely? You can have 7 nested, that only as 6.