Alternative ways to calculate circular references?

**ancog** · 07-18-2021, 06:31 AM

The most common way to calculate a formula involving a circular reference is by using iterative calculations. One example of an iterative calculation is to start with a random value for x_0, then use that value to calculate f(x_0), then set x_(n+1)=f(x_n) until f(x_n) is close enough to x_n.

In the excel file I have attached I tried enabling iterative calculations to calculate a circular reference group on Sheet1, and found that when I just have it filled to the third row it already gives wildly inaccurate results, even after thousands of iterations, and you can test that by copying the formula from a cell inside the circular reference group, pasting it in a word document, copying the formula from the word document, pasting it into another cell, and then finding that the ratio between the values in the cell that is inside the circular reference group, and the cell that is outside the circular reference group are not necessarily close to 1.

If you try to fill down from the third row to the 100th row on Sheet1 then once again the values in the cells that are within the main circular reference group on Sheet1 give wildly inaccurate results.

Is there another way to go about finding the right values for the circular reference group if normal iterative calculations don't work?

Also the cells with values in Sheets 2 through 4 have the function of converting random numbers, that I copy from a real random number generator, into initial values on sheet1. If the initial values below the left most red bar in sheet1 are making it so that the circular reference group has no self consistent solutions then I am curios as to whether there's a way to adjust some of the formulas in Sheet2 so that the cells below the left most orange bar in sheet1 can only have values that allow for self consistent solutions in the circular reference group on sheet1 without making all the values in the cells below the left most orange bar 0.

**MrShorty** · 07-18-2021, 11:09 AM

The spreadsheet is large enough that I did not take the time to reverse engineer the whole thing, but I expect that answering some of this involves a solid understanding of the algorithm you are trying to use. Since I don't understand the details of the algorithm, I can only speak in generalities.

found that when I just have it filled to the third row it already gives wildly inaccurate results, even after thousands of iterations

This sounds like the algorithm is diverging or oscillating rather than converging. Most if not all numerical, iterative algorithms like this have cases where they converge beautifully and others where they fail to converge for one reason or another. It is up to you as the programmer to know what scenarios lead to convergence/divergence so you can tell Excel how to look for divergence and what it should do when a given problem diverges. Off the top of my head, I see three generic directions to go when a problem diverges:
1) The divergence indicates that no solution exists. In which case, stop trying, output "no solution," and move on to a problem that has a solution.
2) The divergence indicates that I started with a bad seed value(s)/initial guess(es). In which case, come up with a better seed value(s) and try again.
3) The divergence indicates that the algorithm is poorly suited to the problem. In which case, find a better, more stable algorithm.
Since I do not understand the details of your problem or algorithm, it is difficult to know which of these three cases is at play in your example, and therefore, I cannot recommend a path forward.