+ Reply to Thread
Results 1 to 2 of 2

Evaluated result is incorrect due to circular reference

  1. #1
    Registered User
    Join Date
    06-30-2020
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    1

    Evaluated result is incorrect due to circular reference

    In the attached file there are two examples. Example 1 is the actual problem I am trying to solve, Example 2 is basically the same formulas with the Z variables modified slightly. I want Example 1 to operate like Example 2 when inputting values into X.
    As shown in the attached file, The variable "X" can range from 1 to 10. When inputting numbers into X in "Example 2" you will see that as the variable X increases, it is allocated to the various Y variables (Y1, Y2, Y3, & Y4) in such a way that it lowers Y1*Z1 until it equals Y2*Z2. Once Y1*Z1=Y2*Z2, it then allocates X so that Y1*Z1 & Y2*Z2 become lower until they are both equal to Z3*Y3. Example 2 operates correctly, the difference between Examples 1 and 2 are the Z variables. I have found through trial and error that when Z1>Z2>Z3>Z4 the sheet works correctly. In order to solve the problem, the Z variables must be equal as shown in Example 1.
    When entering increasingly large X values into Example 1 it can be noted that when X is 1,2, or 3 it works correctly but when X is set equal to 4 it no longer works as intended. When an evaluation is ran on Cell E6, when X=4, it shows that the Cell E6 should equal 3 but instead the result is 4 (screenshot shown in attached image). I realize there is a circular reference, and am guessing that is what is causing the issue. I would like any input to help so that as X increases, the larger Y*Z variables become equal to the smaller Y*Z variables until eventually equaling 0.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-22-2019
    Location
    Los Angeles
    MS-Off Ver
    2013
    Posts
    30

    Re: Evaluated result is incorrect due to circular reference

    Your circular reference for example 1 comes from E7 which contains E6 in the function. J7, therefore, has a reference to E6. From here you are utilizing J7 as a precedent to E6.
    You can fix this (circular will still exist) by placing the output of E6 in another cell to call in E7. I've placed the output of E6 into E3 and used that in E7. Seems to be working, so let me know if that is what you needed. If you can avoid circular reference in the future since it could lead to issues that aren't apparent. Although, in this case Evaluate Formula states it's a circular reference, Error Checking doesn't output anything. Perhaps someone else can chime in.

    As a note, you also have circular references in Example 2.
    Attached Files Attached Files
    Last edited by mrbarba; 07-01-2020 at 04:05 PM.

+ 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: 03-19-2018, 09:16 PM
  2. [SOLVED] Incorrect Circular Reference
    By bgreeson in forum Excel General
    Replies: 19
    Last Post: 01-21-2016, 03:56 PM
  3. Replies: 12
    Last Post: 08-23-2014, 08:44 PM
  4. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  5. Replies: 1
    Last Post: 05-18-2012, 03:22 PM
  6. [SOLVED] Excel 2007 : Incorrect Circular Reference with TODAY() function
    By qaliq in forum Excel General
    Replies: 5
    Last Post: 03-13-2012, 06:48 AM
  7. Incorrect reference cells result
    By jinny in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2005, 02:55 AM

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