Use Vlookup

1) Create a lookup table (your post isn't 100% clear on the proper values
used, so I'm sure my example below is flawed).
2) Make the table a NamedRange called BonusLookup:

Budgeted SalesPct
Low High Bonus
0.000(?) 1.000 1.00
1.010 1.049 1.05
1.050 1.099 1.25
1.100 1.299 1.50 (?)
1.300 Infinity 2.00

3) For each salesman, use a Vlookup formula to get their BonusProRata:
= VLookup(SalesmanBudgetedSalesPct, BonusLookup, 3)
(SalesmanBudgetedSalesPct is whatever calculation you use to determine that
value)

In this case we are having Vlookup search for approximate matches in column
1. If it can't find an exact match it will use the next lowest value as the
"correct" row and return a value from the specified column (i.e., 3rd
column). So, any value = 1.01 but < 1.05 will return the Bonus factor from
the 2nd row of 1.05.

(Note: The "High" column isn't used by the formula. It is there strictly as
a visual aid for the user's convenience. It can be removed if desired, but
change the formula argument as well.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Rosie" wrote in message
...
I have to pay at the end of this year the bonus to my external reps. Their
bonuses are paid on a range of values and I need to adjust the % that they
will be paid at the end of the year in accordance to this range. How can
I
do this without actually going one by one and modifying the amount?

ex: if rep has 100% of budgeted sales: he gets 100% of his yearly bonus,
if
rep has 100,1%,-104.9%: prorata of bonus, 105%-109,9%=125% of bonus....the
maximum is +130%: 200% of bonus.

Is it possible to this? I succeed partially with =if....
--
Thanks!
Rosie