How to remove Circular reference (Need better formula)

Occasional Contributor

Dear All,


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




Many Thanks 


4 Replies

@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.


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.


@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.



@Arvind Kaushik 

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.