Nested if/and

[BillP]

trying to exceed the if statement max in excel 2000. I don't have the
experience necessary to write a function in vba. Can someone please help me.
This is what I am trying to accomplish.
I have 40 worksheets set up as bonus reviews. The review bonus dollar
amounts are based on the number of hours worked and the performance score.
Here is a shot in the dark at what I need in the vba module.
If $B$7 >1000 AND $C$13 = "Good" Then
200
ElseIf $B$7 >1000 AND $C$13 = "Excel" Then
400
ElseIf $B$7 >1000 AND $C$13 = "Outst" Then
500
Elself $B$7>750 AND $C$13 = "Good" Then
100
Elself $B$7>750 AND $C$13 = "Excel" Then
350
Elself $B$7>750 AND $C$13 = "Outst" Then
400
Elself $B$7>500 AND $C$13 = "Good" Then
100
Elself $B$7>500 AND $C$13 = "Excel" Then
225
Elself $B$7>500 AND $C$13 = "Outst" Then
275
Elself $B$7>350 AND $C$13 = "Good" Then
100
Elself $B$7>350 AND $C$13 = "Excel" Then
200
Elself $B$7>350 AND $C$13 = "Outst" Then
250
Elself $B$7>155 AND $C$13 = "Good" Then
50
Elself $B$7>155 AND $C$13 = "Excel" Then
75
Elself $B$7>155 AND $C$13 = "Outst" Then
100
Else
0
End If
===================
$B$7 contains the hours and $C$13 contains the performance rating.
Thank you.
Bill

 

[Lars-Åke Aspelin]

trying to exceed the if statement max in excel 2000. I don't have the
experience necessary to write a function in vba. Can someone please help me.
This is what I am trying to accomplish.
I have 40 worksheets set up as bonus reviews. The review bonus dollar
amounts are based on the number of hours worked and the performance score.
Here is a shot in the dark at what I need in the vba module.
If $B$7 >1000 AND $C$13 = "Good" Then
200
ElseIf $B$7 >1000 AND $C$13 = "Excel" Then
400
ElseIf $B$7 >1000 AND $C$13 = "Outst" Then
500
Elself $B$7>750 AND $C$13 = "Good" Then
100
Elself $B$7>750 AND $C$13 = "Excel" Then
350
Elself $B$7>750 AND $C$13 = "Outst" Then
400
Elself $B$7>500 AND $C$13 = "Good" Then
100
Elself $B$7>500 AND $C$13 = "Excel" Then
225
Elself $B$7>500 AND $C$13 = "Outst" Then
275
Elself $B$7>350 AND $C$13 = "Good" Then
100
Elself $B$7>350 AND $C$13 = "Excel" Then
200
Elself $B$7>350 AND $C$13 = "Outst" Then
250
Elself $B$7>155 AND $C$13 = "Good" Then
50
Elself $B$7>155 AND $C$13 = "Excel" Then
75
Elself $B$7>155 AND $C$13 = "Outst" Then
100
Else
0
End If
===================
$B$7 contains the hours and $C$13 contains the performance rating.
Thank you.
Bill

Try this formula:

=IF(ISERROR(MATCH($C$13,{"Good","Excel","Outst"},0)),0,

INDEX({0,0,0,50,75,100,100,200,250,100,225,275,100,350,400,200,400,500},

3*MATCH($B$7,{0,156,351,501,751,1001},1)-3+MATCH($C$13,{"Good","Excel","Outst"},0)))

Hope this helps / Lars-Åke

 

[BillP]

Hello Lars-Åke,

Thank you for the quick and ACCURATE reply, works very well. I do not know
what the formula is performing but it is accurate.

Much appreciated.

Thanks,

Bill