Need formula to avoid circular references

**Venkata Krishna** · 05-01-2014, 08:10 AM

Dear Experts and seniors,

Please find attached herewith an excel sheet containing the Salary Break up for Monthly Earnings and CTC (Cost to Company) for a particular associate based on our company policy. In this connection, I am able to bring the 90 % of the result which I desired. However, could not fetch the percentage of performance Incentive value which is @ 10 % on Gross earrings due to circular references.

Please find below the detailed desperation for the same.

Our Company CTC has been divided into different heads in keeping view of certain statutory compliances such as Income tax, Provident Fund etc and certain components which will be incurred from employer end like Insurance, Employer Contribution towards P.F. Here, our company will pay performance incentive to an associate based on his/her performance @ 10 % on gross earnings to the maximum extent which is also part of a CTC. In this regard, to arrive the figure I have simply kept a formula @ 10 % on monthly gross earrings in attached sheet. Nevertheless, getting a circular reference warning which says certain formula reference may not compute correctly, as the reference already fixed with formulas. In this connection, how can I remove the same and can fetch the result irrespective of circular reference.

Here I am aware that if want to remove the circular references, need to click the excel options and click the formulas tab and enable err active calculations. But I need a perfect formula for the same where can I get my desired result. In view of this, kindly have a look in attached sheet at circular reference number C11 which is filled with red colour for your ready reference.

In view of the above, can we fix the perfect formula regardless of circular reference in attached sheet where I need the figures in C19 as 10 % on C12?

Your kind support in this regard would be greatly appreciated.

Thanks in advance,

**martindwilson** · 05-01-2014, 08:44 AM

cross post @
http://www.excelguru.ca/forums/showt...lar-references

**Venkata Krishna** · 05-01-2014, 08:54 AM

Originally Posted by martindwilson

cross post @
http://www.excelguru.ca/forums/showt...lar-references

Hi Friend,

Thank you for notifying, I have posted in both with same content and Title only to get the solution, as I admire and follow both forms. Please let me know if it is not correct process I will rectify my self. Kindly note that once I get the answer from our friends and experts will mark post as solved.

Thanks in advance

**MrShorty** · 05-01-2014, 10:30 AM

In making the following suggestion, I recognize that, back in our teens, we all promised our algebra teachers in no uncertain terms that we would never in all our days use algebra in real life. These kind of circular reference questions, IMO, almost always come down to an algebra problem. It usually looks something like:

x=0.1*x+b -- solve for x.

So my usual suggestion for these kind of circular references is to step away from the computer, pull out some paper and a pencil, dust off those old algebra skills, and solve the problem algebraically. Then you will be able to come back to the spreadsheet and program in the formulas without the circular reference.