How to do Tier commission struction formulae in Excel

[tradertt123456]

Hi Guys,

I have a tiering system

0 - 20000 --> 2%
20000 - 50000 --> 3%
50000 and above --> 5%

How do I calculate it in excel?

So if a person gets 100000, first 20,000 will be based on 2% and the next 20000 to 50000 will be at 3% and the remaining 50,000 and above will be at 5%

How do I make excel calculate that?

 

[Aladin Akyurek]

Hi Guys,

I have a tiering system

0 - 20000 --> 2%
20000 - 50000 --> 3%
50000 and above --> 5%

How do I calculate it in excel?

So if a person gets 100000, first 20,000 will be based on 2% and the next 20000 to 50000 will be at 3% and the remaining 50,000 and above will be at 5%

How do I make excel calculate that?

See:

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

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H
1								Tiered Commission
2	Sales	Commission Due				Threshold	Rate	Differential Rate
3	100,000.00	3,800.00				0.00	2%	2%
4	100,000.00	3,800.00				20,000.00	3%	1%
5						50,000.00	5%	2%
6
1d

Excel 2003

Cell Formulas
Range	Formula
B3	=SUMPRODUCT(--(A3>$F$3:$F$5),A3-$F$3:$F$5,$H$3:$H$5)
B4	=A4*0.02+(A4-20000)*0.01+(A4-50000)*0.02
H3	=G3-N(G2)
H4	=G4-N(G3)
H5	=G5-N(G4)

You can try either of the suggestions or use Vlookup.