PDA

View Full Version : Solved: Need a Function or Vlookup to replace Nested If



GoKats78
02-16-2010, 04:58 AM
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
02-16-2010, 05:10 AM
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
02-16-2010, 09:55 AM
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
02-16-2010, 10:11 AM
What about Case A, cpk = 1.5?

Bob Phillips
02-16-2010, 11:53 AM
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.