[ipsych]

Hello,

I tried to calculate the numbers of grains of wheat on the 64th square of a chessboard in Numbers by writing 1 in cell B2, then =B2*2 in the cell below and then pulled it down until I reached the 64th cell. Since Numbers begins to use E+11, E+12 ... I checked the "1,0" box which displayed the values without scientific notation. However, when I compare the values displayed in cell 64 of 9.223.372.036.854.780.000(,00) with the value that it should be 9.223.372.036.854.775.808(,00) it seems that numbers rounds the last five digits.

Did I made a mistake and more importantly, how can I get Numbers to display the real value?

Thanks in advance,

Daniel

[miawj]

No, you have not made a mistake. And you can't get Numbers to display the real value. Although you probably know that fractions and irrational numbers lead to approximations you may wonder why binary computers can't double integers accurately.

There are 64 'bits' on a chessboard. Computers (typically) are accurate (with floating points) to 53 bits (not 64) because they need to use the other bits to store the sign and the position of the floating point. Although you know you are only using integers spreadsheets use floating points and will be accurate to only 52/53 bits (or 15/16 decimal significant figures.)

What Every Computer Scientist Should Know About Floating-Point Arithmetic - David Goldberg
Floating points - Wikipaedia

What worries me is that Excel ends up with a different approximation!

[ipsych]

Hello miawj,

thank you very much for your answer ... that it can display only 15/16 decimal significant figures would explain why the first rounding 'error' occurs on the transition of square 50 (562.949.953.421.312 = 15 digits) to 51 (1.125.899.906.842.620 = 16 digits).

Hmmm ... there goes my confidence in the accuracy of computers ...

Thanks again and best regards

Daniel

[P]

What is described here is so called double-precision math. If you need more accuracy, you can use quad-precision, or in this case, simple integer math. One way to get this is to use the command-line app bc, which lets us you set the required precision manually. I don't recall if it ships with OS X, but you can certainly install it.

I wonder if apps like bash (the shell in Terminal) that lack floating point capabilities may not get the answer correct in this case, as they default to integer math. You could also use Applescript or write a C app to do integer math, I guess.

[TETENAL]

Of course computers can calculate in higher precision than what is supported by the hardware. Otherwise programs like Mathematica couldn't exist. Numbers doesn't seem to support this. Maybe it (and Calculator.app) should.

Arbitrary-precision arithmetic - Wikipedia, the free encyclopedia

[mduell]

Using doubles instead of big ints... ugh, Apple.

[P]

Just like MS does for Excel. Just like apps like Wingz and Clarisworks did back in their day. Really, is there a spreadsheet that will use long longs in that situation?

BTW, bc is installed by default, and entering 2^63 will give you the right answer to the last digit.

[TETENAL]

Originally Posted by P 

BTW, bc is installed by default, and entering 2^63 will give you the right answer to the last digit.

Maybe, but it's the right answer to the wrong question. The right question would be 2⁶⁴-1.

[P]

Actually...

I tried to calculate the numbers of grains of wheat on the 64th square of a chessboard

Which is 2^63. 2^64-1 is the total number of grains on the entire chessboard (and bc gets that right as well).