Simple Math ERROR in Excel when iterative calculation of sheet

Question

Bug :

MS Office Professional Excel 2010 V 10.0.7153.5000 (32-bit)

MS Excel for Mac 2011 14.5.4 (1050722)

I've produced a Excel sheet with a non-linear equation (i.e. circular) which requires iteration turned on to converge.

All seems to work correctly and formulas converges correctly.

However several cells not related to the non-linear equation are in error.

Below shows the problem with Iteration on (itt:100::tol:0.0001):

Red cells are wrong.

The Blue cells are what is used in the calculation

The green cell actually has non-linear interaction with some cells above (see 2nd figure) 

Cell O39 == N39/N40 = 975.0004166/731.0004166 = (Should be 0.7497.. but is shown as 0.6996..)

The bottom box shows the correct (0.7497..) using only numbers.

I generated the same formula above (O38 ERROR and below O40 CORRECT)

Go figure. Ive used excel's non-linear convergence for 20 years and have never experienced this problem.

Any ideas, Tried it in PC 2010 & Mac 2011 versions.

I'm pretty stumped by this one and pretty sophisticated excel user. Would hate to go back to 2003 versions.

If I turn off the iteration of course I get a circular reference as shown below the error is still there

I can remove the non-linear equation by copy/past cell M30 by its VALUE= 137.4...

And then the formula is computed correctly as shown below:

Go figure. Ive used excel's non-linear convergence for 20years and have never experienced this problem. Now Im wondering if Ive designed things with a flawed program

Answer 1

Right click on the file in your browser.  There should be a SHARE option.  Select that.  Then "Get a link" and post it here.

Answer 2

This may have to do with Excel's calculation sequence when Iteration is turned on.

Take a look at Charles Williams page on the Excel Calculation Process, and scroll down to the area on Iteration.

Answer 3

Hi Ron

Thanks for the pointer to Charles Williams page. From what I can infer it looks like EXCEL first updates all "non-circular" cells and then does Circular cells with iteration to convergence. Some how the newer excel then doesn't update "some" of the cells that were dependent on the circular cells output! This is not something I've never seen in previous version of excel. Im going to try and look up an old version of excel to see if old versions have similar bug. I found a similar bug in MATLAB C-code compiler many years ago when an "Improvement" led to an error in calculation. Near as I can tell this is a pretty big Excel bug but only for those who tend to do scientific calculations.

I still cant seem to actually get the excel spreadsheet up in any "public" way to allow someone to get it. I tried to "link" as you suggest on my mac but it simply brings up my work email which isn't where I'm connecting to Community. My local email is ******@gmail.com.

Jim

Answer 4

Jim wrote:

Thanks for the pointer to Charles Williams page. From what I can infer it looks like EXCEL first updates all "non-circular" cells and then does Circular cells with iteration to convergence. Some how the newer excel then doesn't update "some" of the cells that were dependent on the circular cells output!

I'm not sure that is the correct interpretation.

But definition, a system of circular references has no beginning or end.  But Excel must stop the iteration with some cell.  For some systems of CRs, that might result in some inconsistencies -- calculations based on a previous iteration's results.

The "change" between Excel versions might simply be a difference in where Excel chose to stop the iteration.  That might be due to subtle differences in the way the dependency graph is constructed or processed.  Or it might simply be "dumb luck".

Without seeing your system of CRs, I cannot say for sure that that explains what you see.  But it has explained CR inconsistencies that I helped people with in the past.