Commissions
Posted by Rick on January 11, 2002 3:48 AM
Help please?
What is the best way to set up a commission spreadsheet, which will say:
if sales are between 10,000 and 50,000 then charge .5%
then on sales between 50,001 and 100,000 charge 1.% and on the balance over charge 1.5%
thanks
rick
Posted by Troy on January 11, 2002 5:35 AM
try this fomula in D5 if value is in D4. I am assumming that if they are less than 10000 then there will be no commission.
=IF(AND(D4>=10000,D4<50000),D4*0.005,IF(AND(D4>=50000,D4<100000),D4*0.01,IF(D4>=100000,D4*0.015,0)))
Posted by Juan Pablo G. on January 11, 2002 5:44 AM
I'd suggest you create a new 2D table, first column for lower limit and the second one for the commission. Something like:
{"LowLimit","Commission";0,0;10000,0.005;50001,0.01;100001,0.015}
and name it "Commissions"
And then use a formula like:
=VLOOKUP(A1,Commissions,2)*A1
Where A1 is where your sales are. This way you can update your commissions table without any need to update your formulas.
Juan Pablo G.
Posted by Aladin Akyurek on January 11, 2002 5:57 AM
Juan --
What if A1= 50000.8 ?
Aladin
=======
Posted by Juan Pablo G. on January 11, 2002 6:00 AM
Well, that depends, doesn't it ?
Original text:
>then on sales between 50,001 and 100,000 charge 1.%
From 50,001 you get 1% (This is very "accountant" like"), i assumed that anything lower than 50,001 is 0.5%.
But.. of course, that depends...
Juan Pablo G.
Posted by Rick on January 11, 2002 7:05 AM
the figures will be rounded....but what if the amount is 732,000....
that would mean 1st 100,000 @.005 %
then 100,000 to 500,000 @ 1%
then remainder at 1.5%
your soloutions express the whole amount with the commission level
Posted by Aladin Akyurek on January 11, 2002 10:16 AM
Rick --
I'm now confused. Care to restate the question with 1 or 2 hand-calculated results plus if you need a between, tell exactly whether it's inclusive or exclusive at the boundaries?
Aladin
=========
Posted by Rick on January 14, 2002 2:39 AM
Say the boundaries are:
0 to 250,000 .50%
250,001 to 500,000 1.00%
500,001 to 1,000,000 2.00%
else 3.00%
That would mean sales of 329,933.76 would attract commissions of:
250,000 * .50% = 1,250.00
(329,933.76-250,000) * 1.00% = 799.33
Total Commissions = 2,049.33
Sales of 716,164.40 would give comms of:
250,000 * .50% = 1,250
(500,000 - 250,001) * 1.00% = 2,499.99
(716,164.40 - 500,001) * 2.00% = 4,323,27
Total Commissions = 8073,26
Posted by Aladin Akyurek on January 14, 2002 8:14 AM
Rick --
Your most recent post indicates that you will be using VBA for this. Not a bad idea at all. However, here is what I'd do:
Name a worksheet Admin.
In Admin, create in, say, A2:D6 the following:
{"Upper Com Limit",0.03,"","";
"GT","LE","Band","Comm";
0,250000,250000,0.005;
250000,500000,250000,0.01;
500000,1000000,500000,0.02}
In C4 enter: =B4-A4 and copy down this till C6.
Note that except for C4:C6, all aother cell contents are editable by the Administrator.
Name C4 "FirstBand", C5 "SecondBand", and C6 "ThirdBand" via the Name Box on the Formula Bar.
Name D4 "FirstCom",D5 "SecondCom", and D6 "ThirdCom" plus B2 "LastCom" via the Name Box on the Formula Bar.
Lets say that you have sales amounts in a different sheet in A2:A10.
In B2 enter: =IF(A2 >= FirstBand,FirstBand,A2)*FirstCom+IF(A2-FirstBand >= SecondBand,SecondBand,MAX(A2-FirstBand,0))*SecondCom+IF(A2-(FirstBand+SecondBand) >= ThirdBand,ThirdBand,MAX(A2-(FirstBand+SecondBand),0))*ThirdCom+IF(A2-(FirstBand+SecondBand+ThirdBand) < 0,0,A2-(FirstBand+SecondBand+ThirdBand))*LastCom
Copy down this as far as needed.
Aladin
========
