Range Formula

[Hamed parhizkar]

I need help, I am trying to do a bonus calculation for Budget and Atual sales.

In b26 i have actual sales and in b27 budget.

Here is what i am trying to do.

Budgeted Unit sales bonus

-15 to -11= $1000
-10 to -4 = 1500
-5 to -1= $1750
0 to +4= $2000
+5 to +9= $2250
+10 to +14= $2500
+15 to +19= $3000
+20 to +24= $3250
+25 to +29= $3500
+30 to +34= $4000
+35 and over = $4500

So the above is saying for example if actual sales is 15 to 19 units above
budget then the payout is $3000.

Please help me.

Thanks,

Hamed Parhizkar

 

[Joel]

Use a VBA function like the one below
call with
=bonus(b26,b27)

Function bonus(budget, actual)

Select Case (actual - budget)

Case -15 To -11
bonus = 1000
Case -10 To -4
bonus = 1500
Case -5 To -1
bonus = 1750
Case 0 To 4
bonus = 2000
Case 5 To 9
bonus = 2250
Case 10 To 14
bonus = 2500
Case 15 To 19
bonus = 3000
Case 20 To 24
bonus = 3250
Case 25 To 29
bonus = 3500
Case 30 To 34
bonus = 4000
Case 35 To 1000
bonus = 4500
End Select


End Function

 

[Hamed parhizkar]

What do I need to put in the cell. I have only used VBA for macros with
buttons, I am not that familiar with it otherwise. What do I put in the cell
and how do I go to vba without clicking on a button?

 

[Jefgorbach]

Start the visual basic editor and copy/paste the above function.
You now have a new "excel function" called Bonus useable in your
sheets, so any cell you need the Bonus calculated, enter:
=Bonus(Actual,Budget) where Actual and Budget are their
corresponding cells/values.

ie: A B C
1 Actual Budget Bonus
2 3,015 3,000 =Bonus(A2,B2)

 

[Hamed parhizkar]

I did what you said but I get a VBA error saying compile error???

 

[Hamed parhizkar]

If I wanted to do a seperate bonus calcualtion with different amounts and
ranges, how would I setup another function without interfering with the one I
already have???