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

Thanks for interpretation

The issue is associated with the circular reference, but not with its convergence.

In Calculate:Iterations: I have Nmax= 1000 % tolerance =0.00001

All formulas associated with the circular reference are correct to at least 11 significant decimals

The error in calculating cell O39 (=N39/N40 = 731.0004... / 975.0004... = 0.7497...)  is in error greater than 6% (0.6996... compared to correct 0.7497...)

However for cell O40, one row down, with exactly the same formula O40 =N39/N40 the number is correct to 11 significant figures

When I remove all circular references by substituting substituting the VALUE (137.452243) into M30 instead  formula =H30*M9 the cell O39 is correctly calculated to >11 significant figures.

It seems the algorithm now in excel for circular references does not correctly update cells from circular references which depend on the outcome of the circular reference. This is a major bug in the formulation and one I have not ever seen in my many years of using excel.

I'm still trying to figure a way to distribute the excel spreadsheet to this community.

Jim

Answer 2

Here are some screen shots that might help you create a link.

• Open OneDrive in a browser and navigate to the file
• Right click on the file and select SHARE from the drop-down menu

• Get a link

• Create Link after optionally change the Edit option to Read

You should then see a link with another option to shorten it (not necessary, but neater).  You can then copy/paste that link into a message here.

Answer 3

Thanks to all for response.

=> Bottom line is this is NOT an EXCEL BUG Bug but simply non-convergence of a circular reference.

I reduced the overall problem to a limited number of circular cells (4 cells).In doing this I found that the non-linear iteration was Not Converging even with (itt=20000)

It seems there are 2 roots to the solution of the circular equation.

For for every-other iteration the system was flopping from one branch to another.

This is similar to 2 root solution of 2nd order equation Dana put forth in a comments section.

However in my case, the excel solution was stably iterating between branches.

There were large changes between each iterations but every other iteration was exactly the same.

This is something I probably should have seen but it was hidden in all the clutter of my original sheet.

I sure wish excel would print out the convergence error or something to quantify how well it has converged.

In any case, Im going to  be much more careful of non-linear iterations for solutions of circular cells.

Ive used this for many years and now have more respect for what could go wrong.

For anyone wanting to reproduce this bizarre behavior I include pictures below with equations.

Jim

Below are 3 pictures with recalc-iteration set to 1.

This can be seen in cell B6 which increases itself by 1 for each iteration.

For odd iterations, 1,3,5,... one set of numbers is calculated

For even iterations 2,4,6,... a different set of numbers exist

Excel just goes from one to other each iteration with out converging to a solution.

ITT = 1

ITT=2

ITT=3 (SAME AS ITT=1)

Answer 4

> ... Not Converging even with (itt=20000)

Hi.  Here's just an observation.

Note that your equation in B8 has a root discontinuity at B10 = 24  (For excel, divide by zero error).

What this means is that as the value gets closer to 24, the limit approaches Infinity.

You want a solution near zero, but then you add 24 to bring the value back up close to the discontinuity.

If we only look at B8, we note that the equation is cycling in its iteration.  Here's a quick graph of B8 starting at 1.  Also note that the function will slowly diverge if the value is  over 24.

Now, when you take the Max of both equations, you are cycling between the two equations, and therefore, the following  numbers indefinitely...

24.06030917936133,

36.93241373430817,

24.06030917936133,

36.93241373430817

etc...

So, what you observe is correct.  It just depends on when it stops.

Using a discontinuous function like "Max" is always a "Red Flag" in iterative solutions because it's hard to track any "Jumps" in discontinuity. 

I would still suggest posting your equation that you are trying to solve, and avoid Worksheet iteration at all cost.

As a side note, are you trying to solve for a B10 value that makes both A7 & A8 equal?

That would be:    32.84664921314337...

= = = = = = = = = =

HTH   :>)


Dana


= = = = = = = = = =