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

Take a look he

http://www.mcgimpsey.com/excel/variablerate.html

In article ,
"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....

Rosie --
You can simplify your life with an 'if' statement, but it will be
complicated (good practice, though). You'll need to create an equation that
is a succession of "nested" if statements, with a new 'if' segment in the
false part of the previous one. Here's an example. Let's say that A2 is the
cell shoing how well the rep did, and A3 shows his annual bonus:

=if(A2<1,A3,=if(A2<1.05,a3*1.1,a3*1.25))

This statement says, "If A2 < 100%, return back A3. If it's less than 105%
(and we already know it's greater than 100%), then return back 110% of a3.
If not, then it must be greater than 105% and return back 125% of A3." You
can see why it's called a 'nested' set of 'if' statements, and you can make
it as complex as you want. You just have to go in a single direction --
lower to higher (or higher to lower) percentage of target.

I hope I didn't completely confuse the issue.

"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....
--
Thanks!
Rosie

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
__________________________

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