# Evaluated result is incorrect due to circular reference

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.  Register To Reply

2. ## 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.  Register To Reply