| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IFs | Excel Worksheet Functions | |||
| nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
| Nested If with And | Excel Worksheet Functions | |||
| Nested IF | Excel Discussion (Misc queries) | |||
| What is quicker? Nested or non nested ifs | Excel Programming | |||
Linear Mode
