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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NorieAnalyst Assistant Commented:
Looks like it's something to do with floating point arithmetic/rounding.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.