Forum Discussion
Solved
Limit in the precision of big integers as output of a formula
When I enter in a cell this formula "=900000000000000 +199999999999998", I get "1 100 000 000 000 000" instead of "1 099 999 999 999 998". I got the same result whatever the cell format including the scientific format with 16 decimals. (as a matter of fact the actual formula I use is "=10^(AD1/2+1)+10^(AD1/2)-2" where the value of the cell AD1 is "28" and I get the same mishandling of precision). Is there a way to get the right precision or is it a limit of excel?
(I use Excel for Mac Version 16.62)
Many thanks for your answers!
There is a lot of misinformation about this, so pay close attention. Anyone who says that Excel "stores" only 15 significant digits does not know what they are talking about -- and that includes MSFT(!).
(Note: Not intended to reflect on HansV. I had not seen his posting until after I submitted mine. Yes, I'm that slow. sigh.)
The fact is, =900000000000000 +199999999999998 does indeed result in 1099999999999998, as does your formula =10^(AD1/2+1)+10^(AD1/2)-2 with AD1=28.
To demonstrate, with that formula in A1, enter formula =SUM(A1,-1099999999999990). The result is 8.
(Caveat: In general, we cannot write simply =A1-1099999999999990 for others reasons. But actually, that does work as expected in this case, only by coincidence.)
But Excel formats only the first 15 significant digits (rounded), replacing any digits to the right with zero. So the result appears to be 1100000000000000.
Also, when we enter a number, Excel interprets only the first 15 significant digits, replacing any digits to the right with zero -- effectively truncating, not rounding.
Excel can exactly calculate all integers from 0 to 9007199254740992 (2^53).
It can also represent some larger integers exactly, namely any integer that can be represented as the sum of 53 consecutive powers of 2.
Everything else is approximated by the sum of 53 consecutive powers of 2.
But beware: just because the end result of a calculation might be within the limitations above mathematically, we might lose precision if any part of the calculation exceeds them.
Also note that most decimal fractions cannot be represented exactly as the sum of 53 consecutive powers of 2; so the binary representation is an approximation. Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).
That can affect calculations with decimal fractions that result in integers mathematically, but maybe not in Excel (and most applications). For example, 100*32.8 is not exactly 3280. That is, 100*32.8-INT(3280)=0 returns FALSE(!).
(Caveat: Although 100*32.8=INT(3280) returns TRUE for other reasons.)
There is a lot of misinformation about this, so pay close attention. Anyone who says that Excel "stores" only 15 significant digits does not know what they are talking about -- and that includes MSFT(!).
(Note: Not intended to reflect on HansV. I had not seen his posting until after I submitted mine. Yes, I'm that slow. sigh.)
The fact is, =900000000000000 +199999999999998 does indeed result in 1099999999999998, as does your formula =10^(AD1/2+1)+10^(AD1/2)-2 with AD1=28.
To demonstrate, with that formula in A1, enter formula =SUM(A1,-1099999999999990). The result is 8.
(Caveat: In general, we cannot write simply =A1-1099999999999990 for others reasons. But actually, that does work as expected in this case, only by coincidence.)
But Excel formats only the first 15 significant digits (rounded), replacing any digits to the right with zero. So the result appears to be 1100000000000000.
Also, when we enter a number, Excel interprets only the first 15 significant digits, replacing any digits to the right with zero -- effectively truncating, not rounding.
Excel can exactly calculate all integers from 0 to 9007199254740992 (2^53).
It can also represent some larger integers exactly, namely any integer that can be represented as the sum of 53 consecutive powers of 2.
Everything else is approximated by the sum of 53 consecutive powers of 2.
But beware: just because the end result of a calculation might be within the limitations above mathematically, we might lose precision if any part of the calculation exceeds them.
Also note that most decimal fractions cannot be represented exactly as the sum of 53 consecutive powers of 2; so the binary representation is an approximation. Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).
That can affect calculations with decimal fractions that result in integers mathematically, but maybe not in Excel (and most applications). For example, 100*32.8 is not exactly 3280. That is, 100*32.8-INT(3280)=0 returns FALSE(!).
(Caveat: Although 100*32.8=INT(3280) returns TRUE for other reasons.)
- Many thanks, Joe User ! Your answer is very clear and accurate! I've just to figure out how I'll be able to obtain a view with the right precision.
It's a limit of Excel - see Excel specifications and limits :
Feature
Maximum limit
Number precision
15 digits
(JoeUser will probably correct me and provide a more detailed answer)
