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.
Bookmarks