On Mon, 17 Oct 2005 13:26:04 -0700, "Rosie"
wrote:

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....


How about a Vlookup?

With the following in A1:B5

100 100
104.9 prorata
105 125
109.9 125
130 200

D1 contains the % of Budgeted Sales,

E1= the % of Bonus =
=IF(VLOOKUP(D1,$A$1:$B$5,2)="prorata",D1,VLOOKUP(D 1,$A$1:$B$5,2))

It wasn't clear what the Bonus % would be for say 120 % of Sales. Is
there a pro rate element or is it 125%. Change the lookup table
accordingly if so.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________