Formatting cells for big numbers and formulas ...without the formu

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

Hello,
I am stuck with this: If I want cells to display like 20-digit numbers in
numerical form (that is, not the xxx+EXX way), formatting them as a text is
okay. But then I need to add formulas and want the results to be displayed.
But as the cells are text formatted, the result in the cell is of course the
text of the formula plus the number. So my question is - in a cell I want
e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the
resulting number, not the formula itself. How do I achieve this?
 
The maximum precision for a number is 15 digits according to Excel
help (Search for "Excel specifications and limits", and look at
"calculation specifications").

You can change the format of the cell to be numeric to display all
digits, however Excel does round the final five digits.

I am therefore guessing it is not possible. . . .
 
I think Stew is right. You could have a formula like this:

=TEXT("12345678901234567890" - A1 - B1,"0")

but before the calculation is carried out the 20-digit number will be
converted to floating point format (15-digit precision), and so your
resulting answer will not give you the precision you require. If you
had different (small) numbers in A1 and B1 you would still get
12345678901234500000 as the answer.

Hope this helps.

Pete
 
If you don't have many variations of your formula and VBA is ok, you could use a
User Defined Function.

If you want to try:

Option Explicit
Function myMath(myStr As String, rng1 As Range, rng2 As Range) As Variant

Dim myNum As Variant 'decimal/String or error

If IsNumeric(myStr) _
And IsNumeric(rng1.Value) _
And IsNumeric(rng2.Value) Then
myNum = CDec(myStr)
myNum = "" & myNum - rng1.Value - rng2.Value
Else
myNum = CVErr(xlErrRef)
End If

myMath = myNum

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=mymath("12341234123412341234",A1,A2)

The real problem becomes when the formulas change. It can be very unwieldy to
support lots of variations--parsing the formula is a real pain.
 
Back
Top