# How to remove Circular reference (Need better formula)

Occasional Contributor

# How to remove Circular reference (Need better formula)

Dear All,

I need better formula to calculate the highlighted cells, which shows circular reference:

Many Thanks

Arvind

4 Replies

# Re: How to remove Circular reference (Need better formula)

@Arvind Kaushik C6 refers to C9 and vice versa. That indeed causes a circular reference.

Start by explaining what value you would expect in C9, and why.

# Re: How to remove Circular reference (Need better formula)

1. To Calculate Compensatory =CTC-BASIC-HRA-Conveyance-Bonus-Superannuation-PF
2.To Calculate PF=if BASIC<15000 then take minimum value between 1800 or sum(BASIC+Compensatry)*12%
here we are using PF in Point 1 & Point 2 hence circular ref. is coming, can we built a better formula to resolve this issue.

Thanks,
Arvind

# Re: How to remove Circular reference (Need better formula)

@Arvind Kaushik Well, you can't have C6 and C9 depend on one-another. Rather than repeating what is already obvious from the picture, what would you want to see in C6 and C9, given all the other numbers in C3, C4, C5, C7, C8, and C10, if you had to calculate it manually?

Edit: Unless, when you "Enable iterative calculation" in Options, Formulas.

# Re: How to remove Circular reference (Need better formula)

I would not enable Iterative Calculation.  As you said, you only need a better forumla.

According to your description, under the conditions that cause the circular calculation:

PF = (Basic + Comp)*12%

PF = (Basic + CTC - Basic - HRA - Convey - Bonus - Super - PF)*12%

PF = (CTC - HRA - Convey - Bonus - Super)*12% - PF*12%

PF*(1+12%) = (CTC - HRA - Convey - Bonus - Super)*12%

PF = (CTC - HRA - Convey - Bonus - Super)*12% / (1+12%)

So the formula in C9 should be:

=ROUND(IF(C3<15000, MIN(1800, (C10-C4-C5-C7-C8)*12% / (1+12%)), C3*12%), 0)

Caveat: Since you did not provide the formulas in a form that I can copy-and-paste, beware of typos.

-----
Because C10 and C5 are constants, it is prudent to enter the following into C9 (PF):

=ROUND(IF(C3<15000, MIN(1800, MAX(0, (C10-C4-C5-C7-C8)*12% / (1+12%))), C3*12%), 0)

Otherwise, C9 would be negative for C5 (Conveyance)>C10*67.5%, perhaps plus a little more due to rounding.

Similarly, it is prudent to enter the following into C6 (Compensatory):

=MAX(0, C10-SUM(C3:C5,C7:C9))

Otherwise, for C10>30000, C6 would be negative for C5 (Conveyance)>C10*11.5%, perhaps plus a little more due to rounding.