Tiered Cumlative Commisions By Month

[ben]

This one has about driven me insane. What I am trying to do is write a quick
script that will calculate a tier structure for a salesmen with a cumlative
total by month for the whole year. I will try to explain here (and if anyone
requests an .xls I can e-mail you what I have.)
Tier 1 - $0 - $200,000 7%
Tier 2 - $200,000.01 - $250,000 8%
Tier 3 - $250,000.01 - $300,000 9%
Tier 4 - $300,000.01 - $350,000 10%
etc etc in that fashion
The problem I'm running into is it's cumlative not just flat per month. Ie
Let's say this is his sales for the first 4 months of the year

J - $120,000
F - $150,000
M - $17,000
A - $50,000

He would make a flat 7% in january because he is still in the first Tier BUT
in febuary he hit $270,000 "total" sales which means he should get 7% for
$80,000 ($200,000 - $120,000 from january), + 8% for $50,000 (tier 2) + 9%
for $20,000 (total sales febuary - $130,000 for sales already commisioned)
for a total of $11,900 total commisions in Febuary. Then in march he would
get 9% on $17,000 because his total is only $287,000 and he hasn't hit the
10% tier yet. However in April he will get the remainder of Tier 3 commision
for $13,000 + Tier 4 commision for the remaining $37,000.


I know this is confusing.. Believe me I'm about to lose my mind trying to
figure out a way to do it.

 

[Patrick Molloy]

one way

function tiervalue(total as double) as double
select case total
case <= 200000
result = total * 7%
case <= 250000
result = 14000 + (total - 250000) * 8%
case <= 300000
result = 14000 + (50000*8%) + (300000-250000) * 9%
and so on

end select
tiervalue = total
end function

another way would be to use a table with appropriate formula in a sheet

This one has about driven me insane. Wh

at I am trying to do is write a quick