+ Reply to Thread
Results 1 to 4 of 4

Need formula to avoid circular references

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    28

    Need formula to avoid circular references

    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
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Need formula to avoid circular references

    cross post @
    http://www.excelguru.ca/forums/showt...lar-references
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Need formula to avoid circular references

    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
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Need formula to avoid circular references

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  2. [SOLVED] How to avoid formula circular references and hiding and locking the formula
    By Venkata Krishna in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 06-21-2013, 08:17 AM
  3. =formula to copy values only to avoid circular reference
    By dainova in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2013, 07:29 PM
  4. IF formula turning up circular references
    By arpa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2007, 06:31 PM
  5. Need to avoid a circular reference...
    By Denise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1