|
Nested if/and
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$7750 AND $C$13 = "Good" Then 100 Elself $B$7750 AND $C$13 = "Excel" Then 350 Elself $B$7750 AND $C$13 = "Outst" Then 400 Elself $B$7500 AND $C$13 = "Good" Then 100 Elself $B$7500 AND $C$13 = "Excel" Then 225 Elself $B$7500 AND $C$13 = "Outst" Then 275 Elself $B$7350 AND $C$13 = "Good" Then 100 Elself $B$7350 AND $C$13 = "Excel" Then 200 Elself $B$7350 AND $C$13 = "Outst" Then 250 Elself $B$7155 AND $C$13 = "Good" Then 50 Elself $B$7155 AND $C$13 = "Excel" Then 75 Elself $B$7155 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 |
Nested if/and
On Sat, 28 Nov 2009 15:31:59 -0500, "BillP" wrote:
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$7750 AND $C$13 = "Good" Then 100 Elself $B$7750 AND $C$13 = "Excel" Then 350 Elself $B$7750 AND $C$13 = "Outst" Then 400 Elself $B$7500 AND $C$13 = "Good" Then 100 Elself $B$7500 AND $C$13 = "Excel" Then 225 Elself $B$7500 AND $C$13 = "Outst" Then 275 Elself $B$7350 AND $C$13 = "Good" Then 100 Elself $B$7350 AND $C$13 = "Excel" Then 200 Elself $B$7350 AND $C$13 = "Outst" Then 250 Elself $B$7155 AND $C$13 = "Good" Then 50 Elself $B$7155 AND $C$13 = "Excel" Then 75 Elself $B$7155 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 |
Nested if/and
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 "Lars-Åke Aspelin" wrote in message ... On Sat, 28 Nov 2009 15:31:59 -0500, "BillP" wrote: 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$7750 AND $C$13 = "Good" Then 100 Elself $B$7750 AND $C$13 = "Excel" Then 350 Elself $B$7750 AND $C$13 = "Outst" Then 400 Elself $B$7500 AND $C$13 = "Good" Then 100 Elself $B$7500 AND $C$13 = "Excel" Then 225 Elself $B$7500 AND $C$13 = "Outst" Then 275 Elself $B$7350 AND $C$13 = "Good" Then 100 Elself $B$7350 AND $C$13 = "Excel" Then 200 Elself $B$7350 AND $C$13 = "Outst" Then 250 Elself $B$7155 AND $C$13 = "Good" Then 50 Elself $B$7155 AND $C$13 = "Excel" Then 75 Elself $B$7155 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 |
| All times are GMT +1. The time now is 10:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com