Sliding commission rates in excel

  • Thread starter Thread starter dene.coleman
  • Start date Start date
D

dene.coleman

I have tried to follow this thread and work through a similar
commission calculation. Can anyone write an excel formula for me to
help me stop losing any more hair?

Commission rate:
Billings up to $1500 at 45%
Billings between $1500 - 2000 at 50%
Billings above $2000 at 60%

Thanks
 
You could also set this up as a simple table to use with a VLOOKUP. This
would allow you to change steps pretty easily. The table would look like
this:

A B
1 $- 45%
2 $1,500.01 50%
3 $2,000.01 60%
4
5 [insert value here]
6 =VLOOKUP(B5,A1:B3,2)
 
Commission rate:
Billings up to $1500 at 45%
Billings between $1500 - 2000 at 50%
Billings above $2000 at 60%
Click to expand...

You might want to clarify what you mean.

As written (and interpreted by some responses), it surprises me that
you intend to pay (or receive) over $75 more just for being clever
enough to convince a customer to pay $2 more (the difference between
$1499 and $1501).

I wonder if you really mean is: 45% on the first $1500, 50% on the
amount over $1500 up to $2000, and 60% on the amount over $2000.

Also, phrases like "up to" and "between" are imprecise and error-
prone, at least as you used them. Clearly you intend for $2000 to be
the top end of the 50% bracket. (At least, that is what "above"
clearly means. Was that your intent?) Do you intend $1500 to be the
top of the 45% bracket likewise, or do you intend $1500 to be the
bottom of the 50% bracket?

You could write unambiguously (if this is your intent):

Billings below $1500 at 45%
Billings between $1500 - 2000 at 50%
Billings above $2000 at 60%

Or you could write (if this is your intent):

Billings up to $1500 at 45%
Billings above $1500 up to $2000 at 50%
Billings above $2000 at 60%


----- original posting -----
 
Back
Top