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 Dana

You are right, the MAX() is problematic and removal allows either eqn to converge.

These two non-linear equations, each with 2 roots causes non-convergence when combined with max.

The automatic iterative solution ability of excel to solve circular equations is one of Excel's most powerful constructs and, to my knowledge, not found in other languages without additional programming. I have used it in a number of very sophisticated Excel/VB programs. I will have to be a bit more careful in the future with its use.

For those wanting to duplicate the results, the equations are actually shown in the spreadsheet screen captures. I never really figured a way to get the actual spread sheet into the system.

Thanks again

Jim

Answer 2

Hi.  Just a general comment.   To me, the equation you are trying to solve is not clear.

> ... These two non-linear equations, each with 2 roots causes...

Your first equation:  (I'll use x for B10)

117/(576 + x^2)

By "Finding a 'Root" I assume you mean the zero crossing Roots since this is not an equality?

Note that this never crosses 0, so there are No roots in general.

And when you add 24 either at the end (or in input.. I can't tell), then neither of these have roots either.

24 + 117/(576 + x^2)

117/(576 + (24 + x)^2)

I am not sure why one is using Max(B7, B8) 

Since you used the terms Roots (assume zero roots)

did you mean both are =0, and hence B7 = B8?

A zero-root solution would most likely be = B8-B7 and not MAX( )

I guess we can agree that when Excel's iteration is converging on one solution, we don't want to use MAX( ) and have it jump to another totally different equation.

I'd be curious to know what function you are trying to solve for.  I'm sure someone can give a simple vba solution that will avoid the complexities of worksheet iteration.