calculating commissions on ranges

[kim]

Hi

I've seen similar questions in this newsgroup, but the
answers don't quite seem to fit.

I am trying to calculate sales commission based on
ranges. If a rep sells anything less than 1,200,000, the
commissionn is 5 percent. If a rep sells anything between
1,200,000 and 1,599,999 the commission is 5 percent on the
first $1.2M then, 6 percent on the remaining 400,000. The
structure goes like this:

<$1.2M, commission is 5%
between 1.2M and 1.599; 6%
between 1.6 and 2M, 7%
$2M and over is 10%

Any ideas on a formula here? Many thanks!

 

[Michael J. Malinsky]

You could use a VLOOKUP function to do what you want. Suppose in A1 I enter
the sales dollars and in B1 I enter the following:

=VLOOKUP(A1,D1:E4,2)

In range D1:E4, I have the following table:

0 5%
1,200,000 6%
1,600,000 7%
2,000,000 10%

So if, for example, you enter 1,599,999 in A1, B1 will show 6%.

Or you could use:

=IF(A1<1200000,5%,IF(A1<1600000,6%,IF(A1<2000000,7%,10%)))

Assuming the sales number is in cell A1.

You may have to tinker with the dollar amounts (i.e., 1,999,999 instead of
2,000,000) depending on if the scenario is "less than" or "less than or
equal to."

HTH
Mike

 

[David W. Benson, Jr.]

Kim,

Try this (assuming the sales amount is in Cell A1):

=a1*.05+max(a1-1200000,0)*.01+max(a1-1600000,0)*.01+max(a1-2000000,0)*.03

David Benson

 

[David W. Benson, Jr.]

Michael,

I don't think your suggestions quite meet Kim's need. Your lookup table and
formula would return the correct incremental percentage, but doesn't account
for the fact that lower amount ranges should use a lower percentage.

For example, if a salesman sells $1.5M, his commission would be:

5% of the first $1.2M, or $60,000
6% of the amount over 1.2M, or 6% of 0.3M, or 18000
Total commission: $78,000

Your lookup and formula would return a percentage of 6% for an input of
1.5M -- but you can't apply the 6% to the entire amount (6% of 1.5M =
90,000).

David Benson

 

[J.E. McGimpsey]

Not quite - the OP wanted to calculate 5% on the 1st 1.2M, then 6%
only on the next 400K, not 6% on all 1.6M.