Excel can't sum up properly?!

Hello all!
I've been working with Excel for almost two decades and I've never seen anything like this.  When summing up a series of numbers all with only two decimals, the result returns a value of 3 in the billionth position.  How is such a thing possible?  Has anyone ever seen anything like this before?  I could replicate this bug with Excel 2003 and Excel 2010.  Same result happens if each number is added up separately. Thanks.
Wow.xlsx
Wow.jpg
Francis LarocqueAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Looks like it's something to do with floating point arithmetic/rounding.
0
 
Martin LissOlder than dirtCommented:
You're probably using the Double data type which is floating point. If you are using it for money change it to the Currency type, otherwise use Long.
0
 
Martin LissOlder than dirtCommented:
Format your data using Currency as the format or define a Number format with as many decimals as you need.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Francis LarocqueAuthor Commented:
Guys, the value returned is incorrect.  Even if I use the currency format, if I copy paste value the formula the value is wrong! It is 727282,410000003 instead of 727282,41.
0
 
Martin LissOlder than dirtCommented:
When you choose Currency you can set the number of decimals.
0
 
Francis LarocqueAuthor Commented:
But the format doesn't change anything to the actual cell value!
0
 
Martin LissOlder than dirtCommented:
I changed it to 4.
2018-03-01_09-56-23.png
0
 
Francis LarocqueAuthor Commented:
Martin, you don't understand the problem.
0
 
Martin LissOlder than dirtCommented:
What evidence do you have that it doesn't?
2018-03-01_09-59-27.png
0
 
Martin LissOlder than dirtCommented:
Martin, you don't understand the problem.
Ok then please explain in more detail.
0
 
Francis LarocqueAuthor Commented:
Here.  The cell value is just wrong
2018-03-01-13-02-02.png
0
 
Martin LissOlder than dirtCommented:
You need to also format the data in rows 1 to 10 and it doesn't look like you have.
0
 
Francis LarocqueAuthor Commented:
That doesn't change anything when you copy paste value the result.
0
 
Martin LissOlder than dirtCommented:
At the moment I don't know what to do about that. I'll take a look again later today unless someone else solves it first.
0
 
Francis LarocqueAuthor Commented:
0
 
Ejgil HedegaardCommented:
You are right, it is mathematically wrong.

But not really an error as the article you have found describe.
Excel use a precision of 15 significant digits, but due to the floating point arithmetic, there can be an error on the last digit.
Sometimes the precision is even less than that, due to truncation of all digits after 15.

You can't do anything about it, except changing the displayed format, or rounding in the formula.
1
 
Martin LissOlder than dirtCommented:
...or if you have control or influence over the source, change the source format to Currency.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.