Formula for Tiered Fee

[andrewc]

Hi,

I would like to create a formula that calculates the commission based on the following tiered schedule of sales and commission rates:

Please Login or Register  to view this content.

This data is in A1:B5.

I can put something together with lots of IF statements but I was wondering if there is a more elegant solution?

Thanks!

[Sam Capricci]

I'm a bit confused, at 54,999,999 you are at .30%, at 55,000,000 you are at .20% but then excel is .15%, what is excess? 55,000,001?

[Sam Capricci]

And not sure about my outstanding question, this might work for you, but you ought to consider what I put in post #2 as it might need to be changed...
=SUMPRODUCT(--(A2>{0,10000000,20000000,35000000,55000000})*(A2-{0,10000000,20000000,35000000,55000000}),{0.005,0.004,0.003,0.002,0.0015})