Calculating Commission rates in Excel - Formula question

[Rick1991]

I've seached high and low and cant seem to get this to calculate correctly so i'm hoping someone on here can help figure this out!

My company uses a tiered commission system that pays out as follows.
$0-500,000 0.91% commission
$500,000-800,000 1.35%
$800,000-1,000,000 1.86%
$1,000,000+ 2.4%

Every formula I've discovered seems to recalculate the amount before it at the new rate once it reaches the next tier (eg: 550,000 will calculate 500,000 at 0.91% and 550,000 at 1.35% when it should only calculate 50,000 at 1.35%)

Thanks!

 

[AhoyNC]

This link should help.
http://www.mcgimpsey.com/excel/variablerate.html

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	550,000.00	5,225.00		Brackets	Rates	Delta
2				0	0.91%	0.91%
3				500,000	1.35%	0.44%
4				800,000	1.86%	0.51%
5				1,000,000	2.40%	0.54%
6
6a

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(A1>Brackets),A1-Brackets,Delta)
F2	=E2-N(E1)

Named Ranges
Name	Refers To	Cells
Brackets	='6a'!$D$2:$D$5
Delta	='6a'!$F$2:$F$5

or
=SUMPRODUCT(--(A1>{0;500000;800000;1000000}),A1-{0;500000;800000;1000000},{0.0091;0.0044;0.0051;0.0054})

 

[DRSteele]

Nice one, Dave.

Here is a more long-winded explanation in a youtube video. https://www.youtube.com/watch?v=eFj1MxraX1Q

 

[Rick1991]

Holy, that was fast. You guys are awesome!

Thanks all!