Percentage Question

[iddon17]

I'll try to keep it simple:

I have people doing work for me. Clients pay them directly and then they pay me a fee.

They pay 20% on the first 10 clients
10% on clients 11-20
Then 5% on any over 20

How do I get excel to calculate this for me.

I obviously need to calculate how many clients they have which I can do. Then calculate the %.

Its the change from 20% to 10% I can't do.

 

[VBA Geek]

so if you have 30 clients you should have 12 + 11 + 10.5 of fees? or 31.5?

 

[Aladin Akyurek]

We have 15 clients. What is the expected percentage?

 

[iddon17]

So lets say 15 clients all paying my guy £100

My guy then has to pay me 20% for the 1st 10 clients so 20% of £1000 is £200

Then 10% on the other 5 so £50

Just struggling to get it to know when my guys go over 10 clients and reduce the % for those over 10

 

[VBA Geek]

Here is a simple clear way for you to set it up

formula in E2: =MAX(0,H$1-$A2-SUM(E3:E$5)) and drag down

formula in F2: =E2*$H$2*C2 and drag down

formula in H3: =SUM(F2:F4)

then you just need to change cells H1 and H2 and you get the results update in H3

Excel 2010
	A	B	C	D	E	F	G	H	I
1	L	H	Perc		BreakDown	Fees		15	Number of Clients
2	0	10	20%		10	£ 200.00		£ 100.00	Each Client Pays
3	10	20	10%		5	£ 50.00		£ 250.00	Total Fee to Pay to me
4	20	~	5%		0	£ -
Sheet5

 

[VBA Geek]

fyi: There is also a way to do it all in 1 formula. you can do (based upon above layout)

=SUMPRODUCT(FREQUENCY(ROW(INDIRECT("1:"&H1)),{10,20}),{0.2;0.1;0.05})*$H$2