circular reference - query

[fayez_MrExcel]

In formula cell D2, I want to get the actual % of (35000 / 109221.12) which is 32.05% , but since the Basic is referenced to C2,D2,E2, i am having circullar reference. Appreciate if someone help me correct the formula in D2.

A​	B​	C​	D​	E​	F​
1​	Employee	Total	Housing	Other	Trans	Basic
2​	Emp 1	170,462.32	35,000.00	14,624.37 (expected result)	10,922.12	109,221.23

My Formula

• Cell B4: IS FIXED AMOUNT
• Formula C2: =IF($B$2<=215988,35000,IF($B$2>=243000,MAX(($B$2-18000)/(1+25%+10%)*25%,($B$2/(1+SUM(10%,25%,13.33%)))*25%),($B$2/(1+SUM(10%,25%,13.33%)))*25%))
• Formula D2: =($B$2/(1+SUM(10%,(Housing/Basic),13.33%)))*13.33%
• Formula E2: =IF(((B2-SUM(C2,D2))/(1+10%)*10%)>18000,18000,IF(((B2-SUM(C2,D2))/(1+10%)*10%)<7800,7800,((B2-SUM(C2,D2))/(1+10%)*10%)))
• Formula F2: =B2-SUM(C2:E2)

 

[fayez_MrExcel]

In formula cell D2, I want to get the actual % of (35000 / 109221.12) which is 32.05% , but since the Basic is referenced to C2,D2,E2, i am having circullar reference. Appreciate if someone help me correct the formula in D2.

A​	B​	C​	D​	E​	F​
1​	Employee	Total	Housing	Other	Trans	Basic
2​	Emp 1	170,462.32	35,000.00	14,624.37 (expected result)	10,922.12	109,221.23

My Formula

• Cell B4: IS FIXED AMOUNT
• Formula C2: =IF($B$2<=215988,35000,IF($B$2>=243000,MAX(($B$2-18000)/(1+25%+10%)*25%,($B$2/(1+SUM(10%,25%,13.33%)))*25%),($B$2/(1+SUM(10%,25%,13.33%)))*25%))
• Formula D2: =($B$2/(1+SUM(10%,(Housing/Basic),13.33%)))*13.33%
• Formula E2: =IF(((B2-SUM(C2,D2))/(1+10%)*10%)>18000,18000,IF(((B2-SUM(C2,D2))/(1+10%)*10%)<7800,7800,((B2-SUM(C2,D2))/(1+10%)*10%)))
• Formula F2: =B2-SUM(C2:E2)

Correction on the Table

	A	B	C	D	E	F
1	Employee	Total	Housing	Other	Trans	Basic
2	Emp 1	170,462.32	35,000.00	14,624.37 (expected result)	10,922.12	109,221.23

 

[myall_blues]

I don't get a circular reference, but then again I don't get the same answers as you in columns D,E or F.
What does Housing/Basic represent - is that the same as C2/F2?

Book1
	A	B	C	D	E	F	G
1	Employee	Total	Housing	Other	Trans	Basic
2	Emp 1	170,462.32	35,000.00	14,624.37 (expected result)	10,922.12	109,221.23
3			35000	5120.239	12314.7564	124,540.20	<==
Sheet3

Cell Formulas
Range	Formula
C3	C3	=IF($B$2<=215988,35000,IF($B$2>=243000,MAX(($B$2-18000)/(1+25%+10%)*25%,($B$2/(1+SUM(10%,25%,13.33%)))*25%),($B$2/(1+SUM(10%,25%,13.33%)))*25%))
D3	D3	=($B$2/(1+SUM(10%,(C2/E2),13.33%)))*13.33%
E3	E3	=IF(((B2-SUM(C2,D2))/(1+10%)*10%)>18000,18000,IF(((B2-SUM(C2,D2))/(1+10%)*10%)<7800,7800,((B2-SUM(C2,D2))/(1+10%)*10%)))
F3	F3	=B2-SUM(C2:E2)

 

[fayez_MrExcel]

I don't get a circular reference, but then again I don't get the same answers as you in columns D,E or F.
What does Housing/Basic represent - is that the same as C2/F2?

Book1
	A	B	C	D	E	F	G
1	Employee	Total	Housing	Other	Trans	Basic
2	Emp 1	170,462.32	35,000.00	14,624.37 (expected result)	10,922.12	109,221.23
3			35000	5120.239	12314.7564	124,540.20	<==
Sheet3

Cell Formulas
Range	Formula
C3	C3	=IF($B$2<=215988,35000,IF($B$2>=243000,MAX(($B$2-18000)/(1+25%+10%)*25%,($B$2/(1+SUM(10%,25%,13.33%)))*25%),($B$2/(1+SUM(10%,25%,13.33%)))*25%))
D3	D3	=($B$2/(1+SUM(10%,(C2/E2),13.33%)))*13.33%
E3	E3	=IF(((B2-SUM(C2,D2))/(1+10%)*10%)>18000,18000,IF(((B2-SUM(C2,D2))/(1+10%)*10%)<7800,7800,((B2-SUM(C2,D2))/(1+10%)*10%)))
F3	F3	=B2-SUM(C2:E2)

Hi myall_blues,
First it should be C2/F2 which represent the % of Housing against the Basic
Second maybe you are not having circular reference because your formula is in row 3. However, if you put C2/F2 directly in cell D2, then circular reference will occur

 

[Cubist]

You can't have it both ways. You'd need an alternative formula for Basic and calculate that first before the Other.

 

[myall_blues]

Hi myall_blues,
First it should be C2/F2 which represent the % of Housing against the Basic
Second maybe you are not having circular reference because your formula is in row 3. However, if you put C2/F2 directly in cell D2, then circular reference will occur

Yes sorry you are correct of course. It was late...

 

[RoryA]

You should know by now:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

circular reference

In formula cell D2, I want to get the actual % of (35000 / 109221.12) which is 32.05% , but since the Basic is referenced to C2,D2,E2, i am having circullar reference. Appreciate if someone help me correct the formula in D2.874111 My Formula: Cell B4: IS FIXED AMOUNT Formula C2...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[fayez_MrExcel]

Noted this. I'm sorry for this. I'll void this in the future

 

[fayez_MrExcel]

You should know by now:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

circular reference

In formula cell D2, I want to get the actual % of (35000 / 109221.12) which is 32.05% , but since the Basic is referenced to C2,D2,E2, i am having circullar reference. Appreciate if someone help me correct the formula in D2.874111 My Formula: Cell B4: IS FIXED AMOUNT Formula C2...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Noted this. I'm sorry for this. I'll void this in the future