Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Copying formulas from Excel 2003 to Excel 2007 | Excel Discussion (Misc queries) | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
Excel formulas | Excel Worksheet Functions | |||
Excel formulas | Excel Worksheet Functions |