One of our engineers has created an Excel sheet where a Data Table is calculated from a group of cells
and then those cells calculate some other cells
one of which feeds back into the input cells that create the Data Table - like this
http://www.aemiller.net/data_table_loop.png
This is, of course, a positive feedback loop - which should create a circular reference.
http://en.wikipedia.org/wiki/Circular_reference
However, ... for some reason it doesn't.
After some searching around I found this article
http://msdn.microsoft.com/en-us/library/bb687891.aspx
which would seem to show that what he has actually done is find a little known
data table behaviour which would seem to be a piece of intended functionality
Recalculation of data tables is handled slightly differently:
“Circular references are tolerated.
If the calculation that is used to get the result depends on one or more values from the data table,
Excel does not return an error for the circular dependency.”
What I want to know is how from a programming point of view does Excel resolve an answer in this situation.
As I understand it a circular reference in Excel occurs because of livelock.
So to logically resolve this situation Excel must make a DECISION to end the calculation
by choosing one calculation route/process over another.
In other words Excel must BREAK THE LOOP of the circular reference by treating the two halves before and after the data table as independent of it. The only way I can see to do this is to give one part of the calculation process priority over the other.
But this raises the question : is the answer logical and do all the cells return the correct answers.
It is our experience that they DO but we do not understand WHY?
And what does "Circular references are tolerated" actually mean.
There is a subtle difference between "TOLERATED" and "RESOLVED"?
I notice this is a known issue isn Excel XLL development
http://msdn.microsoft.com/en-us/library/bb687841.aspx
Even if we can logically exploit this functionality to solve a logic problem is there a danger that it may simply be "fixed" in the next version of Excel and we'll have spreadsheets that are not forward compatible?
Is the fact we get the right answer situation specific or are there examples of people getting the wrong answer from circular reference tolerance in data tables?