Formula based on income for percentage splits in a range

Hi everyone,

Been searching all over for this and can;t find anything.

Here's the situation:

I'm projecting income for a budget for 2015. The income is for the company and brokers. So it is commission based.

Here's the example: Broker 1 assumes he will make 125k this coming year. Which is 10416.67 avg a month. 10416.67*2=20833.33 for feb. Then 10416.67*3=31250.00 for march etc.
The company has splits where
0-49999.99 the broker makes 70% and the house takes 30%.
50000-69999 broker makes 70% house makes 29% and support staff makes 1%
70000+ Broker makes 89% house 10% staff 1%.
So essentially im trying to make a formula that will return the amount each month broker and house will make each month based on the total earned.

The hard part to consider is when it reaches the point of a cell where one month its 45000 then the next is 55000. That 10000 needs to be split by 5000 to 70/30 and 5000 to 70/29/1.

CHALLENGE!

Thanks all

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordongly.

Thanks.

Much better, thank you.

Row\Col	A	B	C	D	E	F	G	H
1		Cumu
2		$ -		70%	30%	0%
3		$ 50,000.00		70%	29%	1%
4		$ 70,000.00		89%	10%	1%		B2:F4: Input
5
6	Month	Earn	Cumu	Broker	House	Staff
7	Jan	$ 10,584.48	$ 10,584.48	$ 7,409.14	$ 3,175.34	$ -		B7:B17: Input
8	Feb	$ 12,530.87	$ 23,115.35	$ 8,771.61	$ 3,759.26	$ -		C7 and down: =SUM(B7,C6)
9	Mar	$ 10,063.22	$ 33,178.57	$ 7,044.25	$ 3,018.97	$ -		D7 and across and down: =SUMPRODUCT(($C7 > $B$2:$B$4) * ($C7 - $B$2:$B$4) * (D$2:D$4 - D$1:D$3)) - SUM(D$6:D6)
10	Apr	$ 24,475.51	$ 57,654.08	$ 17,132.86	$ 7,266.11	$ 76.54
11	May	$ 9,023.06	$ 66,677.14	$ 6,316.14	$ 2,616.69	$ 90.23
12	Jun	$ 8,363.70	$ 75,040.84	$ 6,812.35	$ 1,467.71	$ 83.64
13	Jul	$ 9,584.06	$ 84,624.90	$ 8,529.81	$ 958.41	$ 95.84
14	Aug	$ 8,864.56	$ 93,489.46	$ 7,889.46	$ 886.46	$ 88.65
15	Sep	$ 8,846.22	$ 102,335.68	$ 7,873.14	$ 884.62	$ 88.46
16	Oct	$ 12,829.61	$ 115,165.29	$ 11,418.35	$ 1,282.96	$ 128.30
17	Nov	$ 9,834.71	$ 125,000.00	$ 8,752.89	$ 983.47	$ 98.35	Check
18	Total	$ 125,000.00		$ 97,950.00	$ 26,300.00	$ 750.00	$ 125,000.00

Staff isn't getting rich ...

You're welcome.