Commission based formula

[Mfiorello]

Hi,
I need a formula which will analyze a reps total gross commissions and determine what percentage they are eligible for commission payouts:
For example, a rep gets paid a percentage of their incoming commission based on YTD performance: 0 - 51999 is 50%, 52000 - 72999 is 52%, 73000 - 99999 is 55%, 100000 - 124999 at 60% and so on.

So if rep currently has 27000 in commission and new sale is for additional 50000 in commission this new commission would be paid on three levels: 24999 at 50%, 20999 at 52% and 4000 at 55% because the new commission spanned three payout levels.

I have used an array to easily find the total commission percent, however, I need to find a way to have it calculate and break it down into those three levels (or just one or two levels, whatever applies)

Any suggestions?

 

[jbeaucaire]

Like so...just extend the table in the upper left corner as long as necessary:

Excel Workbook
	A	B	C	D	E	F
1	YTD				Sales	Commission
2	0	50%			27000	13500
3	52000	2%			50000	25620
4	73000	3%
5	100000	5%
Sheet2

========
This is a tiered commission chart, so each new tier shows how much MORE commission to add to what was listed above, so it starts at 50%, then reads as +2%, +3%, etc.

 

[PaddyD]

more detail on the general approach:

http://www.mcgimpsey.com/excel/variablerate.html