2

Firstly I do not want to round my numbers, I am using base 10 figures so excel should have no bother doing simple add and subtract.

A|  665.110000000000000000000000000000
B|  4.990000000000000000000000000000
C|  =a1+b1
D|  670.790000000000000000000000000000
E|  1,335.890000000000000000000000000000
F|  =e1-(c1+d1)

I should get -5 however F1 returns

-4.999999999999770000000000000000

It is a financial document therefore I want to avoid rounding, more to the point. Why should I need to? I've put cell formatting to Currency with 2 decimal placed and I will get the correct display value but for all calculations I will still get -4.9999...7 which leads to further errors with conditional formatting etc.

4
  • 2
    lookup precision and representation Nov 2, 2010 at 23:35
  • 1
    What version of Excel? I'm getting -5 (as expected) with Excel 2003.
    – Callie J
    Nov 2, 2010 at 23:39
  • 1
    If you're worried about losing that ten billionth of a penny, call me up and I'll cover you for it. Nov 5, 2010 at 6:43
  • 1
    "I am using base 10 figures " - internally the computer is not! It only has ones and zeroes. Maybe the conversion from and to decimal is the cause of the "discrepancy"
    – BrianA
    Nov 5, 2010 at 8:46

3 Answers 3

6

The problem is due to the basic hardware of almost all computers. They are based on binary systems so they don't count from 0 to 9, instead they just count 0 and 1, or base 2.

This is fine unless as you've discovered you want high precision decimal numbers. Computers can only count in terms of fractions that are powers of a half, i.e. 1/2, 1/4, 1/8, 1/16, 1/32 etc. There is no easy way for a computer to represent some numbers such as 1/3 or 4.99 perfectly accurately without giving up a lot of performance.

Excel is quite good at hiding this fact, it's only if you turn on 20 or so decimal places do you see this error. It does this by rounding, but only rounding when the result is displayed.

You say this is for financial calculations, do you really need the final result displayed to 20 decimal places? Or do you just need to be sure that the calculation is done with enough precision that rounding errors won't occur. If it's the latter you may just have to trust that excel has been doing it's job for a few years for countless financial organizations, without problem. And that you need not worry about those rounding errors causing you problems.

3

This has been answered on here before.

Just because you are using a "Round" number in base 10, doesn't help--the computer stores numbers in binary. Your round base 10 number translates to a number in binary that ends up repeating, you lose information when converting that decimal back.

Computer users get around this with two methods:

1) do your math to a certain number of decimal places then round them to less decimal places. 2) use BCD or some other exact decimal representation.

To the best of my knowledge Excel does not use #2, so pick another option.

1

there is an option in excel "precision as displayed" which effectivly auto-rounds numbers to the precision you choose to display your numbers at.

http://support.microsoft.com/kb/78113

would not recommend that you do that though as that may introduce rounding errors into your calculation as you are telling excel to round to 2 decimal places whereas currently the rounding error is to 15 (ish) decimal places so will not affect your calculations at all.

1
  • 1
    And be aware that "precision as displayed" is a workbook setting, not per sheet. Jun 5, 2011 at 9:03

You must log in to answer this question.