0

I have a database which houses scaled integers, the longest being 10 digits long. I am attempting to convert these to decimal values in Excel, moving the decimal point left by 4 digits, i.e. dividing by 10000.

Given that these integers are currently under the 15-digit significant figure limit, and will remain so, is there a possibility that I can encounter rounding errors?

toolshed
  • 1,919
  • 9
  • 38
  • 50
  • 1
    Yes. See section 2 “Errors Designed Not To Be Found” in http://www.cs.berkeley.edu/~wkahan/Mindless.pdf – Pascal Cuoq Feb 20 '14 at 18:16
  • That article addresses convoluted and complex arithmetic in Excel. The division that I am attempting to perform is fraction-less. If I am dividing by 10,000, binary division should be possible, shouldn't it? – toolshed Feb 20 '14 at 18:32
  • Are you saying that all the integers you intend to divide by 10000 are multiples of 10000? If so, you should make that clearer in your question, and you can expect the result to be exact. – Pascal Cuoq Feb 20 '14 at 19:10
  • Any integer between 1 and 9999999999 divided by 10000 should yield a quotient without round-off error. – Gary's Student Feb 20 '14 at 19:23
  • @Gary'sStudent thank you. Pascal, the numbers are scaled integers. Therefore the value of `123456` in my database is, in reality, `12.3456`. – toolshed Feb 20 '14 at 20:25
  • Excel uses IEEE 754 double-precision, a **binary** format. The number `12.3456` is not representable in binary at any precision, as aren't most of the numbers that have a simple expression as decimal fractional numbers. So **yes, you will have rounding errors, plenty of weird rounding errors, as documented in Kahan's article**. – Pascal Cuoq Feb 21 '14 at 20:07

1 Answers1

0

is there a possibility that I can encounter rounding errors?

Strictly speaking I think yes. For example:

SO21916134 example

but what may be significant is that the discrepancy as shown (all formatted the same, the smaller black ones created by formula, the red ones by difference of those immediately above) is in the tenth decimal place, so hopefully not a problem.

pnuts
  • 58,317
  • 11
  • 87
  • 139