Aug 29 2022 03:37 AM
Dear All,
I need better formula to calculate the highlighted cells, which shows circular reference:
Many Thanks
Arvind
Aug 29 2022 04:27 AM
@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.
Aug 29 2022 04:41 AM
Aug 29 2022 04:49 AM - edited Aug 29 2022 04:57 AM
@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.
Aug 29 2022 11:24 AM
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.