Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying a % as a number | Excel Discussion (Misc queries) | |||
Currency format for Excel 2007 not displaying actual number | Excel Discussion (Misc queries) | |||
displaying complex number in Excel | Excel Discussion (Misc queries) | |||
Displaying large number of pictures in EXCEL | Excel Discussion (Misc queries) | |||
Excel formula not displaying a number | Excel Programming |