2

If I paste the number 1542819813881128.5 into a cell of Number format in Excel the pasted value is 1542819813881120.00.

Why do these applications round down the 8.5? Is there a way of preventing this?

I say Reinstate Monica
  • 25,487
  • 19
  • 95
  • 131
MM01
  • 133
  • 1
  • 5
  • You can only configure Excel to ROUNDUP or ROUNDDOWN, Excel only supports 15 significant digits, so a number in Excel will be rounded in one direction no matter what. – Ramhound Nov 21 '18 at 17:44
  • See also [Adding more than 15 digits in Excel](https://superuser.com/questions/373997/adding-more-than-15-digits-in-excel). One of the answers suggest to use an addin for large number maths. – Doc Brown Nov 22 '18 at 07:08

1 Answers1

3

Spreadsheets are not designed for highly accurate calculations. The IEEE 754 specification for floating point math calls for only the 15 most significant digits to be stored. If the integer portion of a number is 15 places or longer, the mantissa or fractional part will be dropped.

The solution is to perform your calculations in something other than a spreadsheet, which is vexing for us who have gotten really good at spreadsheets.

K7AAY
  • 9,512
  • 4
  • 33
  • 62
  • 1
    Mind blown. This is an antiquated constraint. – MM01 Nov 21 '18 at 17:51
  • https://softwarerecs.stackexchange.com/questions/53292/any-spreadsheet-provide-more-then-double-precision – K7AAY Nov 21 '18 at 18:03
  • 2
    @MM01 , no, it is very much current, ubiquitous even in high end computers, and for 99.9999% of all users sufficient. There is very little in this world outside bleeding edge research where so many digits make any sense. – Aganju Nov 21 '18 at 20:10