formula field: don't display anything when value is zero (using ;"

hello, I read this under the topic "How to get a formula field to total an 
entire table column, even if some cells in the column contain text or are 
blank" on the MVP-site and can't get it to work. I'm interested in the final 
bit, using ;"" 
Does anyone know how to do this?

<start quotation>...

Then in the total cell, press Ctrl+F9, and within the field braces {}, 
insert the following formula:

{ SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }

“Table1” in the formula refers to the name of the bookmark you've marked the 
main table with. The formula, being in a different table from the column it's 
totalling, wouldn't know which table you were referencing otherwise. The F:F 
bit means the 6th column from the left. 

The bit at the end of the formula: 

;''" 

.... means that if there are no values in column F, don't display anything 
(otherwise it would display 0.00). I learnt that trick from Word MVP Cindy 
Meister. 

....<end quotation>

-- 
A professional template builder
0
Utf
12/5/2009 2:12:01 PM
word.docmanagement 5542 articles. 2 followers. Follow

5 Replies
2446 Views

Similar Articles

[PageSpeed] 38

You probably confused ;''" at the end (semi-colon, two single quotation 
marks, double quotation mark) with ;"" (semi-colon, two double quotation 
marks).

-- 
Stefan Blom
Microsoft Word MVP



"JOHABE" <JOHABE@discussions.microsoft.com> wrote in message 
news:0E9AF40B-1AE8-4274-95AC-0F5574BBEC08@microsoft.com...
> hello, I read this under the topic "How to get a formula field to total an
> entire table column, even if some cells in the column contain text or are
> blank" on the MVP-site and can't get it to work. I'm interested in the 
> final
> bit, using ;""
> Does anyone know how to do this?
>
> <start quotation>...
>
> Then in the total cell, press Ctrl+F9, and within the field braces {},
> insert the following formula:
>
> { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }
>
> "Table1" in the formula refers to the name of the bookmark you've marked 
> the
> main table with. The formula, being in a different table from the column 
> it's
> totalling, wouldn't know which table you were referencing otherwise. The 
> F:F
> bit means the 6th column from the left.
>
> The bit at the end of the formula:
>
> ;''"
>
> ... means that if there are no values in column F, don't display anything
> (otherwise it would display 0.00). I learnt that trick from Word MVP Cindy
> Meister.
>
> ...<end quotation>
>
> -- 
> A professional template builder 


0
Stefan
12/5/2009 2:33:19 PM
If you are *working in the table*, the following will work

{ =SUM(F:F) \# ",0.00;-,0.00;"}

If you are *working outside the table*, display the formatting by clicking 
the � button
At the end of each row of the table (outside the table) there is a cell end 
character. Put the cursor between one of these characters and the table (it 
doesn't matter which) and insert a bookmark - Table1

The following will then work

{ =SUM(Table1 F:F) \# ",0.00;-,0.00;"}


-- 
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


"JOHABE" <JOHABE@discussions.microsoft.com> wrote in message 
news:0E9AF40B-1AE8-4274-95AC-0F5574BBEC08@microsoft.com...
> hello, I read this under the topic "How to get a formula field to total an
> entire table column, even if some cells in the column contain text or are
> blank" on the MVP-site and can't get it to work. I'm interested in the 
> final
> bit, using ;""
> Does anyone know how to do this?
>
> <start quotation>...
>
> Then in the total cell, press Ctrl+F9, and within the field braces {},
> insert the following formula:
>
> { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }
>
> "Table1" in the formula refers to the name of the bookmark you've marked 
> the
> main table with. The formula, being in a different table from the column 
> it's
> totalling, wouldn't know which table you were referencing otherwise. The 
> F:F
> bit means the 6th column from the left.
>
> The bit at the end of the formula:
>
> ;''"
>
> ... means that if there are no values in column F, don't display anything
> (otherwise it would display 0.00). I learnt that trick from Word MVP Cindy
> Meister.
>
> ...<end quotation>
>
> -- 
> A professional template builder 


0
Graham
12/5/2009 2:45:44 PM
Thanks!
I got it working now, you were right about my confusion...
is this a hidden option of the \# option?
Do u know of a site/article that discusses in-depth word-formulas?
Would be interested in that.
-- 
A professional template builder


"Stefan Blom" wrote:

> You probably confused ;''" at the end (semi-colon, two single quotation 
> marks, double quotation mark) with ;"" (semi-colon, two double quotation 
> marks).
> 
> -- 
> Stefan Blom
> Microsoft Word MVP
> 
> 
> 
> "JOHABE" <JOHABE@discussions.microsoft.com> wrote in message 
> news:0E9AF40B-1AE8-4274-95AC-0F5574BBEC08@microsoft.com...
> > hello, I read this under the topic "How to get a formula field to total an
> > entire table column, even if some cells in the column contain text or are
> > blank" on the MVP-site and can't get it to work. I'm interested in the 
> > final
> > bit, using ;""
> > Does anyone know how to do this?
> >
> > <start quotation>...
> >
> > Then in the total cell, press Ctrl+F9, and within the field braces {},
> > insert the following formula:
> >
> > { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }
> >
> > "Table1" in the formula refers to the name of the bookmark you've marked 
> > the
> > main table with. The formula, being in a different table from the column 
> > it's
> > totalling, wouldn't know which table you were referencing otherwise. The 
> > F:F
> > bit means the 6th column from the left.
> >
> > The bit at the end of the formula:
> >
> > ;''"
> >
> > ... means that if there are no values in column F, don't display anything
> > (otherwise it would display 0.00). I learnt that trick from Word MVP Cindy
> > Meister.
> >
> > ...<end quotation>
> >
> > -- 
> > A professional template builder 
> 
> 
> .
> 
0
Utf
12/6/2009 8:01:01 PM
Hi JOHABE,

For an in-depth discussion of field calculations in Word, check out my Word Field Maths Tutorial, at:
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party

After you've digested that, you might also want to check out my Word Date Calculation Tutorial, at:
http://lounge.windowssecrets.com/index.php?showtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party

Do read both documents' introductory material.


-- 
Cheers
macropod
[Microsoft MVP - Word]


"JOHABE" <JOHABE@discussions.microsoft.com> wrote in message news:1EA256FA-0C45-4C03-AB6D-791A70E188E7@microsoft.com...
> Thanks!
> I got it working now, you were right about my confusion...
> is this a hidden option of the \# option?
> Do u know of a site/article that discusses in-depth word-formulas?
> Would be interested in that.
> -- 
> A professional template builder
> 
> 
> "Stefan Blom" wrote:
> 
>> You probably confused ;''" at the end (semi-colon, two single quotation 
>> marks, double quotation mark) with ;"" (semi-colon, two double quotation 
>> marks).
>> 
>> -- 
>> Stefan Blom
>> Microsoft Word MVP
>> 
>> 
>> 
>> "JOHABE" <JOHABE@discussions.microsoft.com> wrote in message 
>> news:0E9AF40B-1AE8-4274-95AC-0F5574BBEC08@microsoft.com...
>> > hello, I read this under the topic "How to get a formula field to total an
>> > entire table column, even if some cells in the column contain text or are
>> > blank" on the MVP-site and can't get it to work. I'm interested in the 
>> > final
>> > bit, using ;""
>> > Does anyone know how to do this?
>> >
>> > <start quotation>...
>> >
>> > Then in the total cell, press Ctrl+F9, and within the field braces {},
>> > insert the following formula:
>> >
>> > { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }
>> >
>> > "Table1" in the formula refers to the name of the bookmark you've marked 
>> > the
>> > main table with. The formula, being in a different table from the column 
>> > it's
>> > totalling, wouldn't know which table you were referencing otherwise. The 
>> > F:F
>> > bit means the 6th column from the left.
>> >
>> > The bit at the end of the formula:
>> >
>> > ;''"
>> >
>> > ... means that if there are no values in column F, don't display anything
>> > (otherwise it would display 0.00). I learnt that trick from Word MVP Cindy
>> > Meister.
>> >
>> > ...<end quotation>
>> >
>> > -- 
>> > A professional template builder 
>> 
>> 
>> .
>>
0
macropod
12/7/2009 4:44:08 AM
For more on the "numeric picture" switch, see this article:

Numeric Picture (\#) field switch
http://office.microsoft.com/en-us/word/HP051862261033.aspx?pid=CH061047321033

-- 
Stefan Blom
Microsoft Word MVP



"JOHABE" <JOHABE@discussions.microsoft.com> wrote in message 
news:1EA256FA-0C45-4C03-AB6D-791A70E188E7@microsoft.com...
> Thanks!
> I got it working now, you were right about my confusion...
> is this a hidden option of the \# option?
> Do u know of a site/article that discusses in-depth word-formulas?
> Would be interested in that.
> -- 
> A professional template builder
>
>
> "Stefan Blom" wrote:
>
>> You probably confused ;''" at the end (semi-colon, two single quotation
>> marks, double quotation mark) with ;"" (semi-colon, two double quotation
>> marks).
>>
>> -- 
>> Stefan Blom
>> Microsoft Word MVP
>>
>>
>>
>> "JOHABE" <JOHABE@discussions.microsoft.com> wrote in message
>> news:0E9AF40B-1AE8-4274-95AC-0F5574BBEC08@microsoft.com...
>> > hello, I read this under the topic "How to get a formula field to total 
>> > an
>> > entire table column, even if some cells in the column contain text or 
>> > are
>> > blank" on the MVP-site and can't get it to work. I'm interested in the
>> > final
>> > bit, using ;""
>> > Does anyone know how to do this?
>> >
>> > <start quotation>...
>> >
>> > Then in the total cell, press Ctrl+F9, and within the field braces {},
>> > insert the following formula:
>> >
>> > { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" }
>> >
>> > "Table1" in the formula refers to the name of the bookmark you've 
>> > marked
>> > the
>> > main table with. The formula, being in a different table from the 
>> > column
>> > it's
>> > totalling, wouldn't know which table you were referencing otherwise. 
>> > The
>> > F:F
>> > bit means the 6th column from the left.
>> >
>> > The bit at the end of the formula:
>> >
>> > ;''"
>> >
>> > ... means that if there are no values in column F, don't display 
>> > anything
>> > (otherwise it would display 0.00). I learnt that trick from Word MVP 
>> > Cindy
>> > Meister.
>> >
>> > ...<end quotation>
>> >
>> > -- 
>> > A professional template builder
>>
>>
>> .
>> 


0
Stefan
12/7/2009 7:23:45 PM
Reply:

Similar Artilces:

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

link custom field to resource names
hello, i have created a custom field "Primary contact" is there a way to link it to the list of Resource names? maybe turn it into a drop down list? thanks "greg" <greg@nospam.com> wrote in message news:%23hSQ6HJsEHA.3396@tk2msftngp13.phx.gbl... > hello, > i have created a custom field "Primary contact" > is there a way to link it to the list of Resource names? > maybe turn it into a drop down list? > > thanks > > ...

Use Copied Outlook PST file as default...How?
If I copy a PST file on my PC, how can I configure my Laptop Outlook to use that copied file as its default. Is it possible to copy new Defined Views in Contacts from one computer to another. Dmahanay <anonymous@discussions.microsoft.com> wrote: > If I copy a PST file on my PC, how can I configure my > Laptop Outlook to use that copied file as its default. Outlook version? > Is it possible to copy new Defined Views in Contacts from > one computer to another. I think views are registry items and not kept in the PST. -- Brian Tillman My outlook version is 2002. >--...

Create SQL temp table then open it using Dex?
I'm wanting to have a stored proc create a temp table and insert records. Then I'd like to use that temp table in Dexterity. This wouldn't involve report writer, it's all code. Here's my thoughts on the code: * call stored proc from dex * the stored proc puts data in a temp table and puts the temp table name in an OUT param * dex uses the value in the OUT param to do a 'open table BLAH with name [param value]' Is something like this possible? Thanks. Bryce This is possible, but because Dex is fussy about it's table structure, it is actually better to let...

can I add a link in Publisher to display, "join our email list"?
I am creating a fairly simply website and need to add a button for visitors to join our email list. Is this possible in Publisher? Please post your question in the web group and we will try to help you there: news://msnews.microsoft.com/microsoft.public.publisher.webdesign or http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.publisher.webdesign&lang=en&cr=US Always include the version of Publisher you are using, the URL of your site and as much detail as possible. DavidF "mfreecox" <mfreecox@discussions.microsoft.com> wr...

Fire a Workflow using a Callout
Hi, I would like to fire a WorkFlow using a callout. I got problems to do that. Can you tell me how I can do that? I tried it like that: CrmService.ExecuteWFProcessRequest wf = new LetterSalutation.CrmService.ExecuteWFProcessRequest(); wf.ProcessId = GetWorkflowProcessId(workflowName, entityContext); myService.Execute(wf); But I get an error: Server was unable to process request. Can you help me? Simon ...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

how to route messages using the internet and not the VPN c
Hi, we are currently in the process of evaluating exchange 2003 in the past we used 3rd party POP3 mail servers for each office, so each office also had it's own MX record no problem that way..BUT now we implemented AD and one of the exch2k3 server is the US is holding the Primary dns MX record for the company the same MX record that we want everyone else to use ( i.e someone@company.com ) regardless to which office they are located in. when the mail is being intercepted on the primary mail server, it's routing itself via the AD GC's servers internally on the vpn connection, becasu...

Portfolio does not Display
Besides buying Money 2007, is there anything else I can try to get my Portfolio View back? I have 2005 Premium. When I click on the Portfolio button, the page displays but none of my data is displayed. But if I go to Accounts they are there. I've tried changing the View in Select View, and the dates but still nothing. BTW, this was working great for more than a 18 mos, then what seemed like all of a sudden, it stopped working. My data file is about 12M in size if that matters. I have XP on a Pentium Dual if that matters. Any help would be appreciated. Tony I also just fou...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

Database Field Sizes.
I have two small problems, firstly Can the Matrix Description field size be increased to 40/60 chars at present too small. or is there a bigger field i can use eg the extended description field size increased. We need a way that when a saleperson inputs a sale order at the POS and the item needs to be special order the following information is automatically passed to the purchased order for the supplier. 1, supplier Ref number for item. 2, Supplier Colour (four suppliers have 56 colours each) 3, Size of Dress etc 4, delivery date required 5, Order confirmation date and number from suppli...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Can we use WINCE 6.0 R2 or R3 to build Windows Phone OS Image??
Hi, Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone OS Image?? If yes Which option I need to select while building the OS Image?? Since the Windows Phone 7 core is Windoes CE 6.0.I am curious to know whether Windows Phone 7 OS Image can be built using Platform builder. TIA, Nithin On 29 June, 10:29, Nithin <nithin.papd...@gmail.com> wrote: > Hi, > > Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone > OS Image?? > > If yes Which option I need to select while building the OS Image?? > > Since the Win...

Free Quantities Using Extended Pricing
Hi, I would like to know if there is a way to enter a promotion using the Extended Pricing as "buy 5 get 1 free", all what I figured that you can build "buy 1 get 1 free" but my customer case is making it in layers each 5 Units with one free. How can I build that? Thanks in advance, ...

search a matrix for values
OK, this is a bit of a tricky problem.... i have a 7*7 matrix in excel, which is full of numbers... i want to search in only half the matrix, where half is from cell1-1, to cell 7-1 (i.e. top left to bottom right). what i want to search is for numbers a certain value, say X. what i then want to do is tablulate these numbers in one column, and then tablue late their position as a reference to the matrix (i.e. their matrix position). how on earth can i do this? Phil I have assumed that the matrix is in A1:G7 In A10 I entered =INDEX($A$1:$H$7,ROW(A1),ROW(A1)) and copied down to A16 In...

Any idea why this is giving me a #Value! Error?
Function FrstLtrs(MyStr As String) As String Dim temp Dim i As Long TmpStr = Split(Trim(MyStr)) 'MsgBox "String" + TmpStr For i = 0 To UBound(TmpStr) If Not (UCase(TmpStr) = "OF") And Not (UCase(TmpStr) = "FOR") And Not (UCase(TmpStr) = "THE") And _ Not (UCase(TmpStr) = "AND") And Not (UCase(TmpStr) = "A") Then If Asc(Left(TmpStr(i), 1)) >= 65 And _ Asc(Left(TmpStr(i), 1)) <= 90 Then FrstLtrs = FrstLtrs & Left(TmpStr(i), 1) End If End If Next End Function Find the mo...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

how to trend a value every 5 minutes
I have a valve linked to excell and would like to chart it to a line graph every 5 minutes Andrew, 1) Format cell A1 to Time (13:30) in the menu. 2) Format cell B1 to Custom (hh:mm;@) in the menu. 3) In cell A2, type"=SUM(A1+$B$1) This will add the start time to whatever value you have in cell B2, right now you want 5 minutes. 4) Copy and Paste A2 down the column until you have enough data. 5) If you decide to change the interval, all you ahve to do is change the time interval in cell B2. "andrew" wrote: > I have a valve linked to excell and would like to ch...

mySQL datetime field reports as Long Integer with ODBC
I'm using Access 2003 as a reporting tool against a mySQL database, connecting with the mySQL ODBC driver 3.51.17. The ODBC driver reports every datetime field as a Long Integer in Access. This makes date operations problematic. I notice an interesting post in this forum from 2005 that would work fine ("Convert UNIX time to windows general date"), but using a function with every row seems like an unnecessary performance hit. Also, it seems counter-intuitive that the driver doesn't recognize the correct field type. The problem looks suspiciously like the mySQL bug rep...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...