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

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?
0
MAC7203 (64)
4/9/2008 8:59:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
435 Views

Similar Articles

[PageSpeed] 13

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

Mac wrote:

> 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?
0
4/9/2008 9:23:08 AM
I think Stew is right. You could have a formula like this:

=3DTEXT("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

On Apr 9, 9:59=A0am, Mac <M...@discussions.microsoft.com> wrote:
> 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 i=
s
> 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 t=
he
> 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?

0
pashurst (2576)
4/9/2008 10:46:59 AM
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.

Mac wrote:
> 
> 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?

-- 

Dave Peterson
0
petersod (12005)
4/9/2008 12:03:01 PM
Reply:

Similar Artilces:

cell
Are there any way to add text and a function in the same cell? For example Hello =sum(XX:XX) Try something like ="Hello "&SUM(A1:A10) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "danne" <modig123@hotmail.com> wrote in message news:0a7f01c36335$498056f0$a401280a@phx.gbl... > Are there any way to add text and a function in the same > cell? For example Hello =sum(XX:XX) Sure, for example: ="Hello = "&SUM(A1:B1) For more info, try MVP Debra Dalgleish's &...

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Validate the format of a number
I need to determine that an entered serial number is valid. It must check that it is 11 characters and follows the format as follows: a letter, followed by a number, followed by 2 letters, followed by 6 numbers, and ending with a letter. For example, the user enters D7PM234567B and the cell next to it would indicate 'valid' or something similar. If 87PM2345674 was entered, it was indicate 'invalid' next to it or something similar to alert the user it is not in the correct format. Thank you very much in advance. Steve This formula =AND(LEN(A1)=11,CODE(...

page numbering #2
I'm working on a landscape Excel document...the document will become part of a booklet,and I need the page numbers at the bottom of the page, but in portrait. Any help? ...

Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of them. Based on the comparisons I'll need to update the original cell with one of those adjoining cell values. After I finish with one column then I need to repeat the procedure on the column to the LEFT of the original column. I know IF, THEN and ELSE statesments but I don't know VB for Microsoft Office products. Range could be all 65,000+ rows on a workseet Start on ColumnJ, Row2 If ColumnJ, Row2 is Null _ If ColumnK, Row 2 is Not Null _ If ColumnJ, Row 1 is Not Null _ ColumnJ, Row2 Value is ...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

XML formatting to HTML...beginner question
Hi, I apologize if I am posting this in the wrong forum. I am creating a .net 2.0 website using vb.net. I have an XML file with the following general format: <LearningObjectives> <lo number = '1'> <Title>First Learning Objective. Click for more details</Title> <details>These are the details for LO 1</details> </lo> <lo number = '2'> <Title>Second Learning Objective. Click for more details</Title> <details>These are the details for LO 2</details> </lo> <lo number = '3'> <Title>Thir...

Date chart formula question?!?1
Hi all, In a very old spread sheet i was using this formula =IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for dates pre 2009. However, i now wish to use it for dates between 2010 - 2011. Each column represent a week and the rows will indicate and project or task. can anyone help? There is nothing it that formula that will make it work for pre-2009 and not for later years. Perhaps you need to look at your data values? If you have a problem, you could tell us what values are being fed into the formula when it works...

IF Formula #6
Does anyone know a way of returning a picture instead of a value in an IF formula? eg =IF(A1=JIM, <picture>, 0) Any help very much appreciated!! Cheers in advance! Hi do you need the pircote or would a hyperlink also suffice?. In this case use something like =IF(A1=JIM,HYPERLINK("C:\temp\test.jp"), "") -- Regards Frank Kabel Frankfurt, Germany Jamie Bishop wrote: > Does anyone know a way of returning a picture instead of a value in > an IF formula? > > eg =IF(A1=JIM, <picture>, 0) > > Any help very much appreciated!! > Cheers in advan...

how do I find an average number of specific words in a column
I am attempting to calculate a number of specific word occurrences. In example, I have a column with yes in certain cells, and no in the others. How can I display the total number of yes and no occurrences? I am fairly new to Excel and know very little about coding in it. Hi you can use the countif function to return the numbers of "yes" and "no" e.g. =COUNTIF(A1:A100,"yes") will count the number of "yes" answers in the range A1 to A100 likewise, =COUNTIF(A1:A100,"no") will count the number of "no" answers in the same range H...

Add disclaimer to all email
Is there anyway to add a disclaimer to internal and external mail sent via Outlook 2000/XP/2003 clients in an Exchange 2003 environment? All my research has led me to 3rd party apps that do this but, of course, at a cost. My CIO is asking me to find a "free" solution. But he requires both internal and external mail to be stamped. GFI Mail Essentials will do this if you download the 30 day trial version, after the trial ends the Disclaimer portion wil still work. Otherwise you can write an event sink, http://support.microsoft.com/Default.aspx?kbid=317680 or http://www.ms...

Multiple Cell Formats
Is it possible for a column to have more than one format, but for those formats to be similar. Eg. I would like to have a multi-format date column. The format can either be Month-Year (Jan-99) or Year only (1999). (I would even like to have something like Jan/Feb-99, but I can live without that.) Any help would be appreciated. Thanks. Hi With true Excel dates in column A e.g. 01 Feb 07, in column B =A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07 or =TEXT(A1,"mmm-yy") =A1 Format>Cells>Number>Custom> yyyy will give 2007 or =TEXT(A1,"yy...

Maximum number of SMTP addresses per user
Hi we use an antispam filter that using ldap to determine if an email address is valid before it reaches our exchange servers. This is very effective and reduces the ammount of spam we receive. The system does not send bounce backs if an address is not valid. What I have been asked to do is to send find a way to send a message back to the sender when someone emails an address of an employee that has left the company to inform them to contact our customer services department. The only way I can think of doing this is to create a mailbox that has multiple smtp addresses on it that has an...

Formatting with CONCATENATE
I would like to use the TODAY() function with a leading word, in this case "Per ". The formula ="Per "&TODAY() produces "Per 38238" and I can't see any way to format the "38238" to make today's date readable. It's easy to do it by using 2 columns, one for "Per" and one for the date, but is there any way to do this in one column? Hi, Try some variation of the following: ="Per "&TEXT(TODAY(),"MM-DD-YY") >-----Original Message----- >I would like to use the TODAY() function with a leading word, in...

Conditional Formatting Text!!
Hi I want to conditionally format some text in a spreadsheet using formula: I have column A1 with: 4a Be 3a To 4c To 4b Be 4c Be 5c Be 5c To 6a Be etc... What I want to do is for all cells which contain "5a Be" "5b Be" 5c Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a formula to put all of them into one formula - If("5a Be" or "5b Be) etc.. Thanks Kiran "Kiran" <kiran.vithal@gmail.com> wrote in message news:1143053337.964222.63000@t31g2000cwb.googlegroups.com... > Hi > ...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

Conditional Formating Help
I would love your help with a conditional formatting goal. I would like to color a cell if it is part of a formula in another cell. Below is an example: A B C 1 12 13 =B1/A1 I then want A1 and B1 colored blue 2 12 13 =B1 I then want A1 and B1 colored blue 3 ...

help with formula #24
$40.50 when S is $795.00 I need help figuring this formula out where am I going wrong? I keep getting the wrong figures. This is the formula: =IF(S118<=25, 0.0525*s118) + IF(AND(s118 >25, S118<=1000), 1.3125 + 0.0275*(S118 -25)) + IF(S118>1000, 28.125 + 0.015*(S118-1000)) Up to $25 will be charged 5.25% , 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance for $25.01 up to $1000, over $1000.01 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value balance (...

Is there an add-in that will lock the cells like later versions of Excel?
I'm using 97 and for 99.9% of everything I do I works fine except I can't lock cell format so there can only be data entry. I would be nice if I could do that. Marc Hi Marc, Can you be more specific about what you want and don't want. "Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote in message news:QThPf.1161$sL2.501@newsread2.news.atl.earthlink.net... > I'm using 97 and for 99.9% of everything I do I works fine except I can't > lock cell format so there can only be data entry. I would be nice if I > could do that. > > Marc > > ...

Receipt with Serial Number
I need a receipt template that will print the serial number on it. I have checked out Customer Source, and can't find one. Could someone send one if they have it. If not, can you tell me how to modify an existing receipt to show the serial number? Thanks! ...

Number of cells that have same values
Imagine there are two columns of cells, each row of two cells are corresponding to each other. How to find out the number of rows where the two corresponding cells have the same value? Assume your Columns are A and B, and your rows go down to 20, Array enter this formula (CTRL+SHIFT+ENTER): =SUM(IF(A1:A20=B1:B20,1,0)) Change the column and row references to fit your data. -- Regards, Dave "Lingyan Hu" wrote: > Imagine there are two columns of cells, each row of two cells are > corresponding to each other. How to find out the number of rows where the two > corres...

How do I lock all cells in Excel except 2 which I need unlocked?
Alex, By default all cells in excel are protected or locked, select the cells you want to unlock and go to format, cells, protection and uncheck locked, the go to tools, protection, and protect sheet, enter a password if you want, now only the cells that you unlocked can be edited. Be aware that this protection is very easy to break, the code to do so can be found very easy, but it will work for most people . If you only need a few locked I would select them all first, Ctrl A, then go to format, cells, protection and uncheck locked, then select the cells you want to lock and go ...

html fonts too big
Using Microsoft OfficeXP. Fonts are too big on websites and Oulook where there is HTML. The fonts not only display to large on the monitor, but are also printed out WAY TO BIG! I am having to write this post on a separate computer since the microsoft website cannot be displayed on the computer in question. Leads me to think that the problem is in internet explorer. I have checked Accessilibity settings; the fonts settings under Edit in Internet Explorer; Themes. Nothing has worked! I have uninstalled and then installed OfficeXP. I'm up-to-date on the service packs, too. Please he...

Conditional Formatting
Apologies in advice for this being an easy one, but I seem to be having a dumb day! I have a conditional format in cell M17 which is a 'Use a formula to determine which cells to format' =$M17<$K17 full red This works fine. I am then trying to copy this condition down several hundred lines, but it copies it exactly as =$M17<$K17 rather than changing the row number each time it is copied. How do I copy it so that it changes the line number every time? Take out the $ signs. If my comments have helped please hit Yes. Thanks. "The Rook" wr...