Hi,
I recently discovered a bug in the Excel Dynamic Array Calculation Engine. If you refer to a formula that has a potential circular reference in it, the dynamic array will create a circular reference when the dynamic array formula is referred to by yet another cell that potentially can be involved in the potential circ, regardless of whether there is an acutal circular reference in the current spreadsheet's state.
For example, if you have a formula that calculates average interest based on the beginning and ending balance of a piece of debt that depends on how much cash you have to pay off that debt and for the formula you use the following pseudo code: =IF(ALLOW_CIRCS=1, (do average interest calculation e.g. the circular reference calculation), (do beginning balance interest calculation - the non-circular calculaton)) and you refer to a cell with such a formula with a Dynamic Array (for example to determine how much cash you generate), then you refer to this Dynamic array by a cell that can be involved in any way to the potential circular reference (for example, this year's ending cash balance would have to connect to the change in cash). You will get a circular reference on the spreadsheet even if ALLOW_CIRCS=0. I can create a small spreadsheet to illustrate this if anyone is interested.
It is kind of hard to explain :). I screwed it up the first few times I contacted support. But I am almost positive this is a bug.
