Results 1 to 4 of 4

Thread: Need a Formula to avoid circular references

  1. #1

    Need a Formula to avoid circular references

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,
    Attached Files Attached Files

  2. #2

  3. #3
    Quote Originally Posted by martindwilson View Post
    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

  4. #4
    Good morning,

    Rather than reference TOTAL EARNINGS, use a sum of BASIC SALARY:MEDICAL REIMBURSEMENT. The reason you are getting a circular reference is that the special allowance is included in the sum. Every time TOTAL EARNINGS calculates SPECIAL ALLOWANCE increases, and the total will eventually be infinity.

    Hope this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts