Excel formulas

[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?

 

[BoniM]

One way:
=C2*65+MAX((C2-29),0)*10+MAX((C2-49),0)*10+MAX((C2-99),0)*10

 

[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. Valko]

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

 

[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

 

[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