Office for Mac Excel 2004 can't add.

10/23/07 | me

[Dick Vomer]

Had a funny thing happen on a spread sheet and was wondering if there was anyone else out there with the same problem.

[Dick Vomer]

I was using Excel and was adding 3 columns together that referenced a prior cell and this is what I got.

19297.47 =cell L12

in cells L14 = L12 * .29 = 5596.27

L15= L12 * .06 =1157.85

L16= L12* .06=559.63

now the weird part

5596.27+1157.85+559.63=7313.74....

I've readjusted the rounding and it still comes out the wrong answer.

It looked funny and that's the only reason I rechecked the math. I always recheck figures out of habit but now I'm wondering how many math errors are put in with Excel in bank statements, tax returns and other businesses that use excel.

Any suggestions or do I just have the lone buggy software?

[Red Badger]

[NTHockey]

L15 & L16 are the same equation - yet you get different results???

Is this a typo or is the formula wrong or ........ Karl Rove at work to destroy Apple.

[stinkerpot65]

L15 = L12 * .06 = 1157.85

L16 = L12 * .06 = 559.63

Hmmmm .... You equations don’t make any sense. Is L12 a variable or the result of a summation, or both?

[chopperman]

I have Excel 2002 for Windows. There is an error in your equations. Specifically:

L16= L12* .06=559.63

The appropriate answer is the same as L15. Your answer is ten percent of L14.

[KayEyeDoubleDee]

now the weird part

5596.27+1157.85+559.63=7313.74....

I've readjusted the rounding and it still comes out the wrong answer.

Is the "weird part" that it summed to 7313.74 instead of 7313.75?

If so, then the problem might be that your columns are using "single precision" floating point numbers [32-bits], and if you could change the type of the values to "double precision" floating point numbers, then you might get better [i.e. less noticeable] rounding error.

[Note that there will ALWAYS be rounding error when working with floating point numbers - the only question is HOW BAD the rounding error will be.]

Beyond that, there are some known bugs with the mathematics package in Excel 2007:

Excel 2007 Multiplication Bug
Monday September 24, 2007
http://it.slashdot.org/article.pl?sid=07/09/24/2339203

[chopperman]

Modifying the multiplication equations as below can eliminate the hidden stuff and solve the penny problem:

L14=0.01*INT(100*L12*0.29+0.5)
L15=0.01*INT(100*L12*0.06+0.5)

etc.

[chopperman]

Even simpler:

L14=ROUND(L12* 0.29,2)
L15=ROUND(L12* 0.06,2)