Forum Discussion

Grayson Le's avatar
Grayson Le
Copper Contributor
Jan 10, 2018
Solved

Really weird decimal amounts

Hi   I was disturbed with a very strange small decimal amounts (0.0000000000011) somehow added "from the sky" to the result of my formulas . The problem started from the cell highlighted in red i...
excel
Grayson Le's avatar
Grayson Le
Copper Contributor
Jan 19, 2018

Thank you all very much.

 

I got the explanation and solution. Perfect.

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Jan 19, 2018
    Hi Grayson

    Great to hear. If you can mark the solution (unless you got the answer from somewhere else), then other forum users will know what the answer is!

    All the best pal.

    Cheers
    Damien
    • Grayson Le's avatar
      Grayson Le
      Copper Contributor
      Jan 19, 2018

      Below is the solution (extracted from the blog, hopefully no copyright problem).

      ------------------------------------------

      . . . 

      Correcting Precision Errors

      Let us go back to my very first example where my conditional formatting seemingly did not work. I know now that was due to the fact that the numbers I was using to calculate the absolute difference did not have exact binary equivalents. This resulted in 1.3240 – 1.3190 = 0.0049999999999999.

      There are two basic ways in which you can compensate for some of the errors due to floating point calculation. The first method is to use the ROUND() function. The ROUND() function can be used to round the numbers to the number of decimal places that is required in your calculations. For my absolute difference column, I only require 4 decimals of precision. So I change the formula in the absolute difference column from:

      =ABS(A2-B2)

      http://officeblogs.net/excel/04-10-08_image6.png

      To:

      =ROUND(ABS(A2-B2),4)

      http://officeblogs.net/excel/04-10-08_image7.png

      My conditional formatting rule works as expected now since 0.0049999999999999 has been rounded to 0.0050.

      The second method to prevent rounding errors from affecting your work is by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:

      1. Click Microsoft Office Button -> Excel Options -> Advanced
      2. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.
        http://officeblogs.net/excel/04-10-08_image8.png
      3. Click OK.

      Going back to my absolute difference example, I set the number format to show four decimal places, and then I turn on Precision as displayed option. Since the display value is the actual value in the cell now, my conditional formatting works properly!

      http://officeblogs.net/excel/04-10-08_image9.png

      It is important to note that once the workbook is saved, all accuracy beyond four decimal places will be lost. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data so save your workbook prior to enabling this option.  This option is generally not recommended unless you are sure more precision will not ever be needed for your situation.

       

Resources