Forum Discussion
Problem to get correct sum of simple plus and minus
Hi
I can not get excel to plus and minus correct.
See excel document for more info
It gives me
- 0,000000000000113686837721616
- -0,000000000000454747350886464
From same numbers
What is worng?
- I'm pretty sure Mikki is user #752188140. Anyeay, in general, when you expect a calculation to be accurate to n decimal places, explicitly round to that number of decimal places. Do not round to an arbitrary number of decimal places like 10, as many people suggest. For example, if your formula is =SUM(A1:A100), change it to =ROUND(SUM(A1:A100);2) if want the sum to be accurate to 2 decimal places. FYI, the root cause of the problem is: more decimal fractions cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses to represent numbers. For example, 124.44 is exactly 124.439999999999,99772626324556767940521240234375. (I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel displays (rounded).
5 Replies
- I'm pretty sure Mikki is user #752188140. Anyeay, in general, when you expect a calculation to be accurate to n decimal places, explicitly round to that number of decimal places. Do not round to an arbitrary number of decimal places like 10, as many people suggest. For example, if your formula is =SUM(A1:A100), change it to =ROUND(SUM(A1:A100);2) if want the sum to be accurate to 2 decimal places. FYI, the root cause of the problem is: more decimal fractions cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses to represent numbers. For example, 124.44 is exactly 124.439999999999,99772626324556767940521240234375. (I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel displays (rounded).
Sorry about the several typos. New to this particular forum. "Anyeay" should be "anyway". "More decimal fractions" should be "most decimal fraction". I deprecate the use of "precision as displayed" for many reasons. But if you want to experiment, be sure to save a backup copy of the Excel file first. PAD can change some constant values permanently.
Mikki,
congratulations, you are #752188136 who discovers the floating point error.
https://www.youtube.com/watch?v=PZRI1IfStY0
Hi i know what floating points is i can not se how plus and minus numbers with no more than 2 decimals as i am not working with big numbers or really smal numbers.
My point is that it is to small a set of data to get that error, i have done a lot of math in python and PHP, and know how to handel it there, but how can i make excel more precise, (can i change the folting point precision)?
The size of the data set is not relevant.
You can choose for rounding or "precision as displayed" in the options.
