Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mac - 09 Apr 2008 09:59 GMT
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?
stew - 09 Apr 2008 10:23 GMT
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. . . .

> Hello,
> I am stuck with this: If I want cells to display like 20-digit numbers in
[quoted text clipped - 4 lines]
> 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?
Pete_UK - 09 Apr 2008 11:46 GMT
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

> Hello,
> I am stuck with this: If I want cells to display like 20-digit numbers in
[quoted text clipped - 4 lines]
> 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?
Dave Peterson - 09 Apr 2008 13:03 GMT
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.

> Hello,
> I am stuck with this: If I want cells to display like 20-digit numbers in
[quoted text clipped - 4 lines]
> 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?

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.