Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate comission graduated scale, $2000=20%, $4000=40%, etc?
I need to set up a spreadsheet in Excel 2003 to calculate comission of sales
on a graduated scale. Salesman sells $0-2000, he receives 20% commission; $2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is appreciated. Thank you. |
#2
|
|||
|
|||
.. I know it's simple ..
And it may not be as simple as it seems .. Try JE's page at: http://www.mcgimpsey.com/excel/variablerate.html which shows the way to handle it -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "kalsolelady" wrote in message ... I need to set up a spreadsheet in Excel 2003 to calculate comission of sales on a graduated scale. Salesman sells $0-2000, he receives 20% commission; $2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is appreciated. Thank you. |
#3
|
|||
|
|||
Hi!
Here's another method. Assume sale amount is in A1. =A1*LOOKUP(A1,{0,0.2;2000.01,0.4}) You use the lower boundary of each commission range. In the above formula any amount over 2000.01 will recieve a 40% commission. If you have another range, say, 4000.01 and greater @ 45%: =A1*LOOKUP(A1,{0,0.2;2001,0.4;4000.01,0.45}) Just add ranges as needed. Biff "kalsolelady" wrote in message ... I need to set up a spreadsheet in Excel 2003 to calculate comission of sales on a graduated scale. Salesman sells $0-2000, he receives 20% commission; $2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is appreciated. Thank you. |
#4
|
|||
|
|||
You may well be right, Biff, but "something" <g
hints to me that the comm calcs will probably follow these lines: Sales: $2,200 Comm from 1st tier: $2000 x 20% = $400 Comm from 2nd tier: $200 x 40% = $80 Total comm: $400 x $80 = $480 Rather than: Sales: $2,200 Comm from 2nd tier: $2,200 x 40% = $880 That's why I guessed it's not so simple .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Oops, typo in line:
Total comm: $400 x $80 = $480 should read as: Total comm: $400 + $80 = $480 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|