|
Excel formulas
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 a 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? |
Excel formulas
One way:
=C2*65+MAX((C2-29),0)*10+MAX((C2-49),0)*10+MAX((C2-99),0)*10 "frostct" wrote: 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 a 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? |
Excel formulas
On Tue, 22 Apr 2008 08:41:03 -0700, frostct
wrote: 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 a 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,Commiss ion_Table,3) +VLOOKUP(A1,Commission_Table,2) --ron |
Excel formulas
"Ron Rosenfeld" wrote in message
... On Tue, 22 Apr 2008 08:41:03 -0700, frostct wrote: 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 a 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,Commiss ion_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(--(A1C1:C4),(A1-C1:C4),E1:E4) http://www.mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP |
Excel formulas
On Tue, 22 Apr 2008 16:50:40 -0400, "T. Valko" wrote:
"Ron Rosenfeld" wrote in message .. . On Tue, 22 Apr 2008 08:41:03 -0700, frostct wrote: 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 a 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,Commiss ion_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(--(A1C1: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 |
Excel formulas
On Tue, 22 Apr 2008 08:41:03 -0700, frostct
wrote: 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 a 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 |
| All times are GMT +1. The time now is 10:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com