Circular Reference, for tiered commission structure

[cgately]

I've been working on this for a while. I will admit to ignorance and am asking for help. I am working on a spreadsheet that will allow me to put in the total commision check amount, have it select the right percentage break down for the agent based on cumulative company dollar in a given time frame. I think the spreadsheet will make sense when you see it. I'm ending up with a circular reference because it is trying to calculate which percentage to use based on a total that is yet to be generated.

Sales-Commission-By-Company-Dollar-Template.xlsx

I need help. Please.

[mcmahobt]

If I understood your problem correctly, try this in E9 and drag down:

Please Login or Register  to view this content.

[djapigo]

cgately,

Circular references are really tricky...

Look at this scenario...
The calculation steps for $25,000 are as follows:
1) $25,000 x 30% = $7,500 so, since $7,500 falls in the second tier... change 30% to 20%
2) $25,000 x 20% = $5,000 so, since $7,500 falls in the second tier... still 20%
3) calculation then stops

However, look at the calculation steps for $24,999
1) $24,999 x 30% = $7,499.70 so, since $7,499 falls in the second tier... change 30% to 20%
2) $24,999 x 20% = $4,999.80 so, since $4,999 falls in the first tier... change 20% to 30%
3) $24,999 x 30% = $7,499.70 so, since $7,499 falls in the second tier... change 30% to 20%
4) $24,999 x 20% = $4,999.80 so, since $4,999 falls in the first tier... change 20% to 30%
5) and this will go on forever! Excel calculation does not know when to stop...

If you look at the File, Options, Formulas... there is a section about Circular References...

Look into the "Enable iterative calculation"... this limits the number of iterations and maybe change the Maximum Change value to $10 or so...

Try that and good luck...

[cgately]

Thanks Mcmahobt. Its close.
I'm trying to make the commision rate be determined from of total company dollar. IE as company dollar goes up the commision rate goes down. Which is why I was coming up with a circular reference.

[djapigo]

cgately,

What answer do you want to see for cases like $24,999?

[cgately]

The total commission amount * Split% = Total Company Dollar
16666.98 * 30% = 5000
25000 * 20% = 5000
50000 * 10% = 5000

I'd like it to be like this
24,999 - 16,666.99 = 8332.01

First Tier 16666.98 * .3 = 5000
Second Tier 8332.01*.2 = 1666.402

And so on until we pass the 50,000 then its 0%

[shg]

So the more the company makes, the less the employee makes? That's very motivational.

[cgately]

Actually, the percentages are a decreasing split. As the agent makes more sales and increases their overall commission I take less from their check. the 30% is a 70/30 split where the agent gets 70% and I get 30% as broker. Next tier is 80/20 and then 90/10 and finally
they keep it all at 100/0.

So as the company makes more I make less with a cap of $15000. One of the lowest around.

[shg]

Ah. Maybe this, then:

Row\Col	A	B	C	D	E	F
1	Amount	Comm	Delta
2	$ -	30%	30%			C2: =B2 - N(B1)
3	$ 5,000	20%	-10%
4	$ 10,000	10%	-10%
5	$ 15,000	0	-10%
6
7	Date	Amount	Cumu	Broker	Agent
8		$ 1,000	$ 1,000	$ 300	$ 700	C8 and down: =SUM(C7,B8)
9		$ 1,000	$ 2,000	$ 300	$ 700	D8 and down: =SUMPRODUCT((C8 > $A$2:$A$5) * (C8 - $A$2:$A$5) * $C$2:$C$5) - SUM(D$7:D7)
10		$ 500	$ 2,500	$ 150	$ 350	E8 and down: =B8 - D8
11		$ 2,500	$ 5,000	$ 750	$ 1,750
12		$ 500	$ 5,500	$ 100	$ 400
13		$ 500	$ 6,000	$ 100	$ 400
14		$ 1,500	$ 7,500	$ 300	$ 1,200
15		$ 3,000	$ 10,500	$ 550	$ 2,450
16		$ 3,000	$ 13,500	$ 300	$ 2,700
17		$ 2,000	$ 15,500	$ 150	$ 1,850
18		$ 1,000	$ 16,500	$ -	$ 1,000
19		$ 3,000	$ 19,500	$ -	$ 3,000