Excel formulas

F

frostct

I have commission payout levels with four ranges. Each range represents the
total amount of items sold. Each range pays a certain $ of commission
increasing as you go up the scale. The four ranges are: 1-29 pays $65 per
unit, 30-49 pays $75 per unit, 50-99 pays $85 per unit, 100 + pays $95.

SO if AE sells 55 units then commission = $3,895. (Each of the units 1-29
pay $65 each totalling $1885. Units 30-49 pay $75 each totalling $1500.
Units 50-55 pay $85 each totaling $510. Add $1885 + $1500 + $510 = $3,895 in
commission.

How do I generate this formula so I can enter the units sold and a value
then appears?
 
R

Ron Rosenfeld

I have commission payout levels with four ranges. Each range represents the
total amount of items sold. Each range pays a certain $ of commission
increasing as you go up the scale. The four ranges are: 1-29 pays $65 per
unit, 30-49 pays $75 per unit, 50-99 pays $85 per unit, 100 + pays $95.

SO if AE sells 55 units then commission = $3,895. (Each of the units 1-29
pay $65 each totalling $1885. Units 30-49 pay $75 each totalling $1500.
Units 50-55 pay $85 each totaling $510. Add $1885 + $1500 + $510 = $3,895 in
commission.

How do I generate this formula so I can enter the units sold and a value
then appears?

One very flexible method is to set up a table someplace in your workbook

NAME it Commission_Table
Commission Table
0 0 $65
29 $1,885 $75
49 $3,385 $85
100 $7,720 $95

Note that you can use a formula in column 2:

Assume table starts in H2:

I3: =I2+(H3-H2)*J2

and fill down.


Then, with the number of units sold in A1, use this formula:

=(A1-VLOOKUP(A1,Commission_Table,1))*VLOOKUP(A1,Commission_Table,3)
+VLOOKUP(A1,Commission_Table,2)

--ron
 
T

T. Valko

Ron Rosenfeld said:
One very flexible method is to set up a table someplace in your workbook

NAME it Commission_Table
Commission Table
0 0 $65
29 $1,885 $75
49 $3,385 $85
100 $7,720 $95

Note that you can use a formula in column 2:

Assume table starts in H2:

I3: =I2+(H3-H2)*J2

and fill down.


Then, with the number of units sold in A1, use this formula:

=(A1-VLOOKUP(A1,Commission_Table,1))*VLOOKUP(A1,Commission_Table,3)
+VLOOKUP(A1,Commission_Table,2)

--ron

Your formula returns the correct result for up to 99 units sold. At >=100
it's off by 10. If units sold was 100 the correct result is 7730. Your
formula returns 7720.

A1 = units sold

Table:

........C.....D.....E.....
1.....0....65.....=D1
2...29....75.....=D2-D1
3...49....85.....=D3-D2
4...99....95.....=D4-D3

=SUMPRODUCT(--(A1>C1:C4),(A1-C1:C4),E1:E4)

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

Ron Rosenfeld

Your formula returns the correct result for up to 99 units sold. At >=100
it's off by 10. If units sold was 100 the correct result is 7730. Your
formula returns 7720.

A1 = units sold

Table:

.......C.....D.....E.....
1.....0....65.....=D1
2...29....75.....=D2-D1
3...49....85.....=D3-D2
4...99....95.....=D4-D3

=SUMPRODUCT(--(A1>C1:C4),(A1-C1:C4),E1:E4)

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

The formula is correct, but there's a typo in the table:

Commission Table
0 0 $65
29 $1,885 $75
49 $3,385 $85
99 $7,635 $95
--ron
 
R

Ron Rosenfeld

I have commission payout levels with four ranges. Each range represents the
total amount of items sold. Each range pays a certain $ of commission
increasing as you go up the scale. The four ranges are: 1-29 pays $65 per
unit, 30-49 pays $75 per unit, 50-99 pays $85 per unit, 100 + pays $95.

SO if AE sells 55 units then commission = $3,895. (Each of the units 1-29
pay $65 each totalling $1885. Units 30-49 pay $75 each totalling $1500.
Units 50-55 pay $85 each totaling $510. Add $1885 + $1500 + $510 = $3,895 in
commission.

How do I generate this formula so I can enter the units sold and a value
then appears?

As Biff pointed out, there is a typo in the table I initially posted. The
table should read:

Commission Table
0 0 $65
29 $1,885 $75
49 $3,385 $85
99 $7,635 $95
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top