Why the number 12,345,678,987,654,321 is not displaying in Excel

[Muhammad Zafeer]

When I take the square of # 111,111,111, It must be 12,345,678,987,654,321.
But MS Excel is showing as 12,345,678,987,654,300.
There is a difference of 21.
Please Explain

 

[Niek Otten]

Excel's precision is 15 decimal digits.
In fact most calculation software, not just Excel, has this limit.
Look here for more information:

http://www.cpearson.com/Excel/rounding.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| When I take the square of # 111,111,111, It must be 12,345,678,987,654,321.
| But MS Excel is showing as 12,345,678,987,654,300.
| There is a difference of 21.
| Please Explain

 

[Mike H]

Excel in common with other software has only 15 digits of precision, anything
above that is truncated. If you want to display larger numbers but not do
calculations on them precede the entry with an apostrophe.

Mike

 

[Rick Rothstein \(MVP - VB\)]

You are doing the squaring operation in VBA, correct? You can get the
accuracy you want by using Variants with a sub-type of Decimal. In this
case, you would do it like this...

Dim Answer As Variant
Answer = CDec("11111111111") * CDec("11111111111")
Range("A1").Value = "'" & Answer

Note that I did not use ^2 to raise the value to the power of 2. The Decimal
data sub-type can only used with simple math operations. Using more complex
operations or functions (such as the Sqr function) drop the calculation back
down to Double data types and their limitations are then in force. Below my
signature is a previous posting of mine showing how to maintain a higher
accuracy for the Factorial function and, in it, I discuss the use of the
Decimal data sub-type in a little more detail.

Also note the leading apostrophe being concatenated onto the beginning of
the Answer variable before being assigned to a cell's Value property... that
is so the "number" will not be rounded down as Mike points out in his
response.

Rick

You could cast (you can't Dim) a Variant variable as a Decimal type and get
some 28 or 29 digits of accuracy depending if there is a decimal in the
answer or not. Simply Dim a variable as Variant and CDec a number into it to
make it the Decimal data sub-type. Thereafter, that variable will track
28/29 digits of accuracy. For example the following function will calculate
factorials up to 29 digits of display before reverting to exponential
display.

Function BigFactorial(N As Long) As Variant
Dim X As Long
If N < 28 Then
BigFactorial = CDec(1)
Else
BigFactorial = CDbl(1)
End If
For X = 1 To N
BigFactorial = X * BigFactorial
Next
End Function

However, you have to watch out for overflows with Decimal data sub-types --
once over 28/29 characters, they will produce an overflow error. So, if you
tried to use the above function like this

Debug.Print 10 * BigFactorial(27)

you would get an overflow error but

Debug.Print 10 * BigFactorial(28)

would work fine (the difference being in the first case BigFactorial has a
Decimal sub-type and in the second case the subtype is a Double).