none
Decimal Value looks funny RRS feed

  • 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

    Friday, January 23, 2015 9:59 AM

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)

    Friday, January 23, 2015 11:44 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 non-displayed digits will be lost.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 23, 2015 1:54 PM
  • 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)

    Monday, January 26, 2015 9:35 PM

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)

    Friday, January 23, 2015 11:44 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

    Friday, January 23, 2015 1:33 PM
  • 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 non-displayed digits will be lost.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 23, 2015 1:54 PM
  • 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

    Friday, January 23, 2015 3:55 PM
  • I'd have to see an example to know what's going on.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 23, 2015 3:58 PM
  • Did you format the cell to show 10 decimal digits? You really need that?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Friday, January 23, 2015 4:05 PM
  • Hi Hans How do I post you my sample in excel file ?

    Cheers

    Friday, January 23, 2015 4:34 PM
  • Hi Felipe,

    No, I don't It show me alone the 15 digit after the copy value !!!

    Sincerily

    Friday, January 23, 2015 4:35 PM
  • Could you create a stripped-down 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)

    Friday, January 23, 2015 4:58 PM
  • Hi Hans,

    Ok, I'll do it on Monday morning.

    I left my office now.

    Have a nice week-end

    Sincerily

    Friday, January 23, 2015 5:38 PM
  • Hi Hans,

    Here is the file : the file

    In hope you find what is wrong with me.

    Cheers

    PS



    Monday, January 26, 2015 6:30 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)

    Monday, January 26, 2015 8:38 PM
  • 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

    Monday, January 26, 2015 8:59 PM
  • 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)

    Monday, January 26, 2015 9:35 PM
  • Hi,

    So that the way I'll use it..

    The subject is closed by the way.

    Still thanks for this end.

    Sincerily

    Merci beaucoup

    PS

    Monday, January 26, 2015 10:20 PM