Why the number 12,345,678,987,654,321 is not displaying in 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

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

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

"Muhammad Zafeer" <Muhammad wrote in message
...
| 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

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

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

"Muhammad Zafeer" wrote:

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

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

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).




"Muhammad Zafeer" <Muhammad wrote in
message ...
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