"sum" returns spooky hidden decimal figures

hello,
I've had a look through posts and can't find reference to this, apologies if it's been dealt with previously. I am running microsoft office professional edition 2003 - original, not pirate.

I have a list of numbers, all 2 decimal place currency figures.

I sum the list and the answer is correct except there are numerous decimal entries 11 digits down. e.g what should be 0.00 is actually 0.0000000000010459.

I have checked the figures being summed and they have no irregularities.

I have checked any blank cells, again nothing.

I have copy and pasted as 'values only' in a new workbook, then re entered sum formulae - same peculier result

i even manually retyped all the figures in a new freshly opened workbook, so definitely only 2 decimal places, yet i still get the same result ?!?

any and all assistance gratefully received.

Josh

p.s. here are numbers entered top to bottom in one column, just in case anyone else gets the same result:

0, 0, 0, 0, blank cell, blank cell, 75491.48, 0, 0, 0, 0, 0, 0, 0, 0, 0, blank cell, blank cell, -4044.27, -553.32, 378, 0, 0, 0, blank cell, 0, blank cell, blank cell, blank cell, 0, 0, 0, blank cell, blank cell, -1207.4, -74284.08, -1260, blank cell, blank cell, blank cell, 0, 882, 0, 0, 0, 168.02, 0, 0, 0, 0, 0, 0, 0, 0, blank cell, blank cell, 0, 1204.40, 85.1, 0, 553.32, 0, 0, 2340, 243.75, blank cell, blank cell, 0, 0, 0, 0, 0, blank cell, 0, blank cell, 0, blank cell,

followed by a simple 'sum' formulae which ought to return 0, but returns:
-1.04591890703887E-11
displayed as -0.000000000010459189073877

Couldn't find any reference to this? Probably just didn't know what to search for, because this topic gets discussed quite a bit on. In a nutshell, computer's perform arithmetic on binary numbers. Most decimal fractions (includind most terminating decimal fractions) are nonterminating binary fractions. Most computer programs use double precision to store numbers, so these numbers are limited to about 15 digits. Basically what you are seeing is a result of that limit on how many digits are stored and carried while adding. Much more detail can be found at http://www.cpearson.com/excel/rounding.htm I'm pretty sure I saw a kb article at microsoft office website. Any numerical methods text (and I expect the idea should be introduced in a beginning programming class) should discuss in detail the effects of round off error. Try putting "rounding error" or "round off error" or something like that into your favorite search engine.

Note that If you multiply all the values by 100, then sum, the answer is exactly zero.

Thus entering the array style formula :

=SUM(.....)*100)/100 . . . . . . {press [ctrl shift enter]}

Will get rid of those little rounding errors.

Alternatively use:

ROUND(SUM(....),2)

Mark.

thankyou MrShorty,

links have given me something to think about and a slight headache this early in the morning !!

can just about see how excel has trouble storing non integers in byte form but not sure why this doesn't cause the same errors in every column that is totalling similar currency figures. Maddening thing is this error crops up amidst other very similar calculations that show no problems.

also, thankyou Mark,

tried your array formula solution but didn't have much joy (not used to using the parenthesis formulae, so I might just be being a bit of a dunderhead).

The way I typed it, the formula looked like {=sum(a1:a77)*100)/100} and excel didn't like it, which I understand due to the unmatched ')'.

I tried variations but couldn't get these to work although I can see how it should work as regards converting the fractions to integers and back again.

tempted to revert to the round function as a last resort as I'm feeling slightly dense now

will check back tomorrow in case you've got the patience / time to post anything else for me.

i might just use the 'precision as displayed' option as a simple solution as I'm only noticing these errors on standard accounting worksheets,

many thanks all,

finally got my head round it,

excel help article "KB 42980: (Complete) Tutorial to Understand IEEE Floating-Point Errors" was useful as were all three comments.

Little surprised computers have this problem though - you live 'n' learn !!

came across this page when looking up info:
http://www.ma.utexas.edu/~arbogast/disasters.html

puts my -0.000000000010459189073877 query into perspective !?!

Originally Posted by Mark@Work

Note that If you multiply all the values by 100, then sum, the answer is exactly zero.

Thus entering the array style formula :

=SUM(.....)*100)/100 . . . . . . {press [ctrl shift enter]}

Will get rid of those little rounding errors.

Alternatively use:

ROUND(SUM(....),2)

Mark.

Sorry, missed out a bracket, should have been

=SUM((.....)*100)/100

ie multiply everything by 100, then sum it, then divide by 100.

Mark.

I understand now,

i added the missing bracket the other day but forgot the parentheses,
just tried it again and it works,

thankyou