Excel failing at simple math?

If "a picture is worth a thousand words", an Excel file is worth a million.

Attach an example Excel file (redacted) that demonstrates the problem. Unfortunately, that is not as easy in this forum as it should be. Click Go Advanced, then scroll down and click Manage Attachments. The Attachments icon on the toolbar does not work.

Since the "obvious" solution (Automatic calculation mode) does not fix the problem, my wild guess is: there is a series of circular references. Since a circle has no beginning, no end, Excel cannot always ensure that all of the cell values in the series are consistent.

That is starting to sound like your iterative algorithm is diverging or oscillating and not converging onto an appropriate value. I think the next debugging step I would try is to set max iterations to 1, then repeatedly press F9 and see if the cells involved in the iterative calculation are oscillating (or otherwise doing something unexpected). If it turns out that they are, then you will need to investigate further as to why the algorithm is not converging.

Originally Posted by Andrew337

I am purposely running a series of circular references, and reiterate calculations is on.

My previous "wild guess" seems to apply. But it might not have been clear, due to many typos. I meant to write:

``my wild guess is: there is a series of circular references. Since a circle has no beginning, no end, Excel cannot always ensure that all of the cell values in the series are consistent.``

This might be even more true if the circular references run through several worksheets (you wrote: ``I have a sheet for every day of the week``).

I used to have a simple example to demonstrate this behavior; cannot find it. I would not call it "misbehavior" by Excel. Again, Excel cannot ensure the consistency of interdependent circular references, by definition. It might have worked before only by coincidence; and something might have changed in Excel to reduce the serendipitous result.

The take-away is: designs with interdependent circular references are inherently flawed. They are a "bad practice", IMHO.

I can usually avoid circular references by adding helper cells. If necessary, I resort to using a VBA function (UDF) to do the iteration.

Or better: Sometimes I can find a different way to solve the problem by spending more time on the mathematics of the problem.