Share via

Simple Math ERROR in Excel when iterative calculation of sheet

Anonymous
2015-09-04T20:27:55+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

17 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2015-09-05T12:33:17+00:00

    Any chance you could upload sample file to OneDrive (or similar file share place)?

    best wishes

    0 comments
  3. Anonymous
    2015-09-05T13:36:07+00:00

    > Cell O39 == N39/N40 = 975.0004166/731.0004166

    Hi.  I can't follow too well, but If it makes a difference, note that N40 is 975, not 731.

    0 comments
  4. Anonymous
    2015-09-05T21:55:16+00:00

    Hi Bernard

    I have put excel_prob.xlsx on my onedrive area but don't know how to make so others can see it.

    Jim

    0 comments
  5. Anonymous
    2015-09-05T22:00:07+00:00

    Hi Dana

    Ya your are correct. Thanks for correction

    In post I typed in wrong formula but spread sheet is correct:

    Post should be:

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

    My bad.

    I'm still pretty shaken by this bug, since this is a numeric error intrinsic to Excel's cell calculation.

    Jim

    0 comments