IF and Lookup issues

[jenm]

I am having difficulty with a formula. The spreadsheet I've created calculates sales commissions. Here are the rules.

commissions are tier based:
Tier Starts At Sales Of
4.0% $-ok to $500,000
6.5% $500,001
8.5% $750,001
10.0% $1,300,001

sales are cumulative. So in January a sales person sells $400k, the commission is 4 % of 500k. Then in february, the sales person sells another 400k, bringing his total sales to 900k.

That sales person gets 4% on the first $100k, 6.5% on the next 250k and 8.5% on the last $150k.

I have a spreadsheet that works just fine if i place the amounts sold in three separate line entries, but I'd like to just enter one line. Any advice? Right now the formula that works using three separate entries is

=LOOKUP(O8,{0,500001,750001,1300001;0.04,0.065,0.085,0.1})

With three separate columns one for the amount sold, one for total amount sold YTD and the final column showing commission.

[Domenic]

Assuming that A2 contains the total sales, try...

=SUMPRODUCT(--(A2>{0,500000,750000,1300000}),A2-{0,500000,750000,1300000},{0.04,0.025,0.02,0.015})

For an explanation of how the formula works, have a look here.

[jenm]

thanks for the quick reply and the link. I will try them!