Answered by:
Decimal Value looks funny
Question

Hi,
I've a group of cell format Number 2 decimal.
Sample: 409.45, 1000,00, 398.22, 110.53, 0.43, 168.46
I've another cell which is the sum of those cells, in the same format : equal to 87.09 (at the screen)
To see what I have really besides the presentation I(ve done a copy / special paste (choice is value) the cell of the sum into another cell.
If my sample the sum is and the marvellous number is : 87.0900000000001, even is the format is the same of the other cells.
What is the big problem, is that I have a lot of number and If I summ those all results, the new result becomes False !!!
Sincerily
Answers

Excel stores and processes numbers in binary format. Since most decimal numbers cannot be represented in binary format (with a finite number of digits), very small rounding errors occur; these can accumulate in calculations.
So the very small rounding error of 0.0000000000001 is nothing to worry about. If you want to be certain that all your numbers are accurate to (for example) 2 decimal places, you can use the ROUND function. E.g., instead of
=SUM(A1:A6)
use
=ROUND(SUM(A1:A6),2)
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM

As I mentioned, you can use the ROUND function to round the result of calculations to the desired number of decimal places.
There is one other option: select File > Options, then click Advanced.
Scroll down to the section 'When calculating this workbook'.
Tick the check box 'Set precision as displayed' and confirm. Then click OK.
Warning: this will store the values of all cells in this workbook as displayed. So if you set the number format to display 2 decimal places, the cell value will be stored with 2 decimal places. All nondisplayed digits will be lost.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by ryguy72 Saturday, January 24, 2015 7:27 PM
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM

As I tried to explain in my first reply, Excel doesn't store numbers with 2 decimal places, it stores numbers in binary format (a series of 0 an 1). During the translation from decimal to binary and back, tiny errors are inevitable. In most situations, you don't notice them, but they are present, as you have found. This is not a bug in Excel, but an inevitable byproduct of the fact that we humans use decimal numbers and Excel uses binary numbers.
If you need to avoid the effects of these tiny rounding errors, you must round the results of your calculations to the desired number of decimal places. That's the way it is...
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM
All replies

Excel stores and processes numbers in binary format. Since most decimal numbers cannot be represented in binary format (with a finite number of digits), very small rounding errors occur; these can accumulate in calculations.
So the very small rounding error of 0.0000000000001 is nothing to worry about. If you want to be certain that all your numbers are accurate to (for example) 2 decimal places, you can use the ROUND function. E.g., instead of
=SUM(A1:A6)
use
=ROUND(SUM(A1:A6),2)
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM

Hi Hans,
I really worry about the rounding error 'cause my all my number is quite lons as 0.00000000000001
Even if the seen is 2 digits.
When I calculate the sum (really a great long list of number) the round gives to me a wrong result.
As example I've have a lot of 0.000000051 etc... or 1.000000032 or ...
I can found or 1 or 0 as result.
If I am near 1 I can found 2.
Pass from 1 to 2 : The double is inaceptable to me
PS

As I mentioned, you can use the ROUND function to round the result of calculations to the desired number of decimal places.
There is one other option: select File > Options, then click Advanced.
Scroll down to the section 'When calculating this workbook'.
Tick the check box 'Set precision as displayed' and confirm. Then click OK.
Warning: this will store the values of all cells in this workbook as displayed. So if you set the number format to display 2 decimal places, the cell value will be stored with 2 decimal places. All nondisplayed digits will be lost.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by ryguy72 Saturday, January 24, 2015 7:27 PM
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM

Hi Hans,
I did what you wrote.
Yes it has some effect but in one of my case I got the wrong answer.
I have a table which have all cell of value mount with 2 decimal.
I want to calculate a percent of those number by row.
I copy the data in another sheet. With the Value option, and the calcul in percent (i.e: cell1=cell1/sum(row1))
Notihing wrong until now.
I Copy/paste a second time in a third sheet with value option.
And I found in my cells some resulets as 10.0005689414 ....
Where Am I wrong ?
Cheeres





Could you create a strippeddown copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.
Regards, Hans Vogelaar (http://www.eileenslounge.com)


Hi Hans,
Here is the file : the file
In hope you find what is wrong with me.
Cheers
PS
 Edited by GestionControleur Monday, January 26, 2015 8:59 PM

Ah  in a pivot table you have no control over the way the numbers are calculated. Instead of a pivot table, you can use formulas.
In cell B2 on Feuil3, enter the formula
=ROUND(SUMIF(Feuil1!$F$2:$F$21369,A2,Feuil1!$O$2:$O$21369),2)
In French, probably
=ARRONDI(SOMME.SI(Feuil1!$F$2:$F$21369;A2;Feuil1!$O$2:$O$21369);2)
Fill down to B14. To make the formula work in B14, enter the formula ="" in A14.
Regards, Hans Vogelaar (http://www.eileenslounge.com)

Hi Hans,
Really nice of you to answer so quickly.
But What you told me it's to round my sum.
Yes I can ear this song, but I really don't want to do that, just because my numbers starts with 2 decimal and somewhere it's just a copy of those numbers. So why do I have to control them by rounding.
Just 'cause I hav no control in any manner in a pivot table. Hmmm, yes I can ear again but again it's just somme numbers with 2 decimal...
Anything I've missed with Excel !!!
Cheers
PS

As I tried to explain in my first reply, Excel doesn't store numbers with 2 decimal places, it stores numbers in binary format (a series of 0 an 1). During the translation from decimal to binary and back, tiny errors are inevitable. In most situations, you don't notice them, but they are present, as you have found. This is not a bug in Excel, but an inevitable byproduct of the fact that we humans use decimal numbers and Excel uses binary numbers.
If you need to avoid the effects of these tiny rounding errors, you must round the results of your calculations to the desired number of decimal places. That's the way it is...
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, January 30, 2015 6:46 AM
