blank when zero
teachelr
calculated field for quantity * price. I have 15 lines for items on the PO,
but if I have a calculated field for quantity * price and I have nothing on
the line it still has a 0. I don't want 14 zeros when I only have 1 item on
my PO. any suggestions.
thanks is advance.
Greg Maxey
One way is to nest your formula in an IF field. If the formula result
(nothing times nothing, or nothing times something, or something times
nothing) returns a syntax error then display nothing, else display the
formulat result:
{IF {Your formula}="!Syntax Error, *"""{Your formula}}
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support
Suzanne S. Barnhill
specifically for the "positive, negative, zero" format. An example of a
switch that would suppress zero here is:
#,##0.00;(#,##0.00);''
The last part is two apostrophes rather than a quotation mark (and the whole
picture should be enclosed in double quotes). I believe it also works if you
put nothing or a space after the last semicolon. I'm basing this on Google
and vague memories; perhaps someone more knowledgeable will post the right
answer!
There are a lot of variations on this answer, but here's the one I would
consider authoritative:
http://www.google.com/groups?selm=uh9TatVRBHA.1656%40tkmsftngp03&output=gplain
--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
"teachelr" <teac...@discussions.microsoft.com> wrote in message
news:5EB3B6D8-6FCC-4DCD...@microsoft.com...
Graham Mayor
on my web site. The required syntax would be
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Suzanne S. Barnhill wrote:
> Look in Word's Help for the "numeric picture switch" topic and look
> specifically for the "positive, negative, zero" format. An example of
> a switch that would suppress zero here is:
>
> #,##0.00;(#,##0.00);''
>
> The last part is two apostrophes rather than a quotation mark (and
> the whole picture should be enclosed in double quotes). I believe it
> also works if you put nothing or a space after the last semicolon.
> I'm basing this on Google and vague memories; perhaps someone more
> knowledgeable will post the right answer!
>
> There are a lot of variations on this answer, but here's the one I
> would consider authoritative:
> http://www.google.com/groups?selm=uh9TatVRBHA.1656%40tkmsftngp03&output=gplain
>
>
>
Graham Mayor
The correct syntax would be
\# "$,0.00;(,$0.00);"
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Suzanne S. Barnhill wrote:
> Look in Word's Help for the "numeric picture switch" topic and look
> specifically for the "positive, negative, zero" format. An example of
> a switch that would suppress zero here is:
>
> #,##0.00;(#,##0.00);''
>
> The last part is two apostrophes rather than a quotation mark (and
> the whole picture should be enclosed in double quotes). I believe it
> also works if you put nothing or a space after the last semicolon.
> I'm basing this on Google and vague memories; perhaps someone more
> knowledgeable will post the right answer!
>
> There are a lot of variations on this answer, but here's the one I
> would consider authoritative:
> http://www.google.com/groups?selm=uh9TatVRBHA.1656%40tkmsftngp03&output=gplain
>
>
>
Greg
I don't see how your suggestions solves the OPs question.
The OP is using a calculation field in a form. I don't
see any combination of switches that will eliminate the
Zero when one or both of the variables is blank.
>.
>
Suzanne S. Barnhill
in forms. His calculated result is a zero and he doesn't want to display a
zero; this numeric picture will suppress the zero.
--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
"Greg" <anon...@discussions.microsoft.com> wrote in message
news:165601c4a6e9$44fa9480$a401...@phx.gbl...
Greg
Have you tested this? Unless you have talked to him and
have inside information, his price and quantity fields
are "blank" (his words where "nothing on the line") and
the actual calculation is a syntax error. The from
calculation field masks this with a 0.00 display using the
switch you provided. Even if I enter 0 in either of the
two variable fields or both I am still getting a display
0.00 in the calculation field using your switch.
While your switch will mask the 0 result if both variables
are 0 in a regular formula field {=({Text1}*{Text2}) \#
#,##0.00;(#,##0.00);''}, if either is blank a syntax error
is returned.
If you can't confirm this then something screwy is going
on in both my 2000 and 2003 versions.
>> >news:5EB3B6D8-6FCC-4DCD-9892-
2AA4F1...@microsoft.com...
>> >> I have a Purchase order template that I am working on
>> and I want to
>> >include a
>> >> calculated field for quantity * price. I have 15
>> lines for items on the
>> >PO,
>> >> but if I have a calculated field for quantity * price
>> and I have nothing
>> >on
>> >> the line it still has a 0. I don't want 14 zeros
when
>> I only have 1 item
>> >on
>> >> my PO. any suggestions.
>> >>
>> >> thanks is advance.
>> >
>> >.
>> >
>
>.
>
Jean-Guy Marcil
Dans son message, < Greg > écrivait :
In this message, < Greg > wrote:
|| Suzanne,
||
|| Have you tested this? Unless you have talked to him and
|| have inside information, his price and quantity fields
|| are "blank" (his words where "nothing on the line") and
|| the actual calculation is a syntax error. The from
|| calculation field masks this with a 0.00 display using the
|| switch you provided. Even if I enter 0 in either of the
|| two variable fields or both I am still getting a display
|| 0.00 in the calculation field using your switch.
||
|| While your switch will mask the 0 result if both variables
|| are 0 in a regular formula field {=({Text1}*{Text2}) \#
|| #,##0.00;(#,##0.00);''}, if either is blank a syntax error
|| is returned.
||
(BTW, you are missing a double quote in your formula... Between \# and the
first #. A typo?)
I have just tested it on Word 2002.
I used a 3col x 5row table.
I put the following field in the third cell of the first row (C1):
{=({=A1}*{=B1}) \# "# ##0,00;(# ##0,00);''}
C1 is always empty if A1 or B1 are either empty or contain a zero. If both
A1 and B1 contain non-zero numbers, then the result is properly displayed.
The switch works as expected on my machine.
OTOH, I think that if you had
{=({=A1}/{=B1}) \# "# ##0,00;(# ##0,00);''}
then something like what you posted would be necessary to handle the Divide
by zero error message.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarci...@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org
Greg Maxey
You are right of course wrt the typo.
My original post was dealing with formfield bookmark names vice table cell
references .
{IF {Your formula}="!Syntax Error, *"""{Your formula}} meaning
{IF {=({Text1}*{Text2})}="!Syntax Error, *"""{=({Text1}*{Text2})}}
As the the user posted in the Table group he can obviosly use table cell
references and your formula is correct.
Still, I am unable to make Suzanne's switch or any other switch return a
"blank" in a formfield calculation type field if either of the variables or
if both varable is blank.
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support
Jean-Guy Marcil
Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:
|| JGM,
||
|| You are right of course wrt the typo.
||
|| My original post was dealing with formfield bookmark names vice table
cell
|| references .
||
|| {IF {Your formula}="!Syntax Error, *"""{Your formula}} meaning
|| {IF {=({Text1}*{Text2})}="!Syntax Error, *"""{=({Text1}*{Text2})}}
||
|| As the the user posted in the Table group he can obviosly use table cell
|| references and your formula is correct.
||
|| Still, I am unable to make Suzanne's switch or any other switch return a
|| "blank" in a formfield calculation type field if either of the variables
or
|| if both varable is blank.
Have you tried:
{=({=Text1}*{=Text2}) \# "# ##0,00;(# ##0,00);''}
??
The switch works really well over here...
Greg Maxey
You are right again for application in a standard formula field. The point
that I am trying to express and fairly miserably is ... I can't make the
numeric picture switch that Suzanne or you are suggesing work to mask a 0 in
formfield "calculation" type field. I don't think it will work, but as I
have been wrong before I can certainly be wrong again.
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support
Jean-Guy Marcil
Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:
|| JGM,
||
|| You are right again for application in a standard formula field. The
point
|| that I am trying to express and fairly miserably is ... I can't make the
|| numeric picture switch that Suzanne or you are suggesing work to mask a 0
in
|| formfield "calculation" type field. I don't think it will work, but as I
|| have been wrong before I can certainly be wrong again.
||
Ah!
Yes, it is possible, but you have to cheat a bit.
Insert your formfield and double click on it to access its properties.
Set it to Calculation, insert the formula you want [e.g. =(A1*B1)] and leave
the number format list blank. Hit OK
Now, do ALT-F9 to display the field codes. You should have something like:
{ FORMTEXT { =(A1*B1) }}
Now, add the switch to obtain:
{ FORMTEXT { =(A1*B1) }\# "#,##0.00;(#,##0.00);" }
Do ALT-F9 again.
Protect the form, now it should work.
Greg Maxey
Once again a session with you is better than school of the boat :-).
Thanks.
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support
Jean-Guy Marcil
Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:
|| JGM,
||
|| Once again a session with you is better than school of the boat :-).
|| Thanks.
||
LOL
What a memory you have!
macropod
Why 'cheat' when you can use:
{={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}
This has the distinct advantage of not being held hostage to a table
structure.
Cheers
"Jean-Guy Marcil" <no-...@leaveme.alone> wrote in message
news:esNNtU0p...@TK2MSFTNGP12.phx.gbl...
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 9/08/2004
Jean-Guy Marcil
Dans son message, < macropod > écrivait :
In this message, < macropod > wrote:
|| Hi Guys,
||
|| Why 'cheat' when you can use:
|| {={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}
||
|| This has the distinct advantage of not being held hostage to a table
|| structure.
||
Greg was inquiring about numerical switches in Calculated Formfields. The
problem was not in the calculation, but in displaying the result.
Are you saying there is a way to make such a field hide results that are
equal to 0 through the field property dialog?
I could not get the last " ; " (the one used for formatting 0 results) to
stick through the dialog. That is why I mentioned "Cheating" as it was the
only way I found to do it easily and quickly.
macropod
I don't believe there is any way to hide the empty Form field itself. If you
leave it empty you get the default with 5 spaces. You could reduce that to
one space by simply hitting the space bar, but that would pointless unless
you're trying to affect the spacing of characters around the Form field,
since it doesn't print anyway.
The equation field that I posted:
{={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}
affects only the display of the calculated result, and avoids the 'Syntax
error' message that you get with:
{={Text1}*{Text2}}
if either Form field is left empty.
Cheers
"Jean-Guy Marcil" <no-...@leaveme.alone> wrote in message
news:ONfOX3Dq...@tk2msftngp13.phx.gbl...
Jean-Guy Marcil
Dans son message, < macropod > écrivait :
In this message, < macropod > wrote:
|| Hi Jean-Guy Marcil,
||
|| I don't believe there is any way to hide the empty Form field itself. If
you
|| leave it empty you get the default with 5 spaces. You could reduce that
to
|| one space by simply hitting the space bar, but that would pointless
unless
|| you're trying to affect the spacing of characters around the Form field,
|| since it doesn't print anyway.
True, but this is not what we are talking about!
A calculated formfield cannot be modified directly by the user. It shows a
result based on calculations.
If the said result = 0, it will display 0 (And I believe it will also
display 0 if the result of the calculation is actually "empty/nil", as it is
the case when you have a calculated formfield that calculate the product of
two cells, but the cells are actually empty, which is not the same as
containing a zero). With regular equation fields, you can add the list
separator at the end of the numeric format switch. If the list separator is
followed by nothing at all, this means that Word will display nothing if the
result of the equation is 0.
Now, in a calculated formfield, you have a dropdown list of numeric format
to choose from in the property dialog box. You can also create your own by
just typing it in the dropdown field. This is where I could not get the list
separator added at the end of the format switch to stick. Word would keep
the list separator as part of the format switch but totally ignore it if a
calculated result was = 0. Hence my suggestion to Greg to cheat a bit by
creating the calculated formfield, and then modifying the field code instead
of trying to achieve the goal of hiding 0 result through the formfield
property dialog. This actually work (Well it worked on Word 2002 on my
machine!). The formfield will be hidden if the result is 0 or "nil"
Does that make any sense now?
macropod
While you may not be able to hide a calculated Form Field, why would you use
one when a simple formula field can return the desired result with a lot
less fiddling? What I showed is how to use a standard formula field to take
the output from two Form Fields and give a 'correct' result when either or
both Form Fields is empty.
Cheers
"Jean-Guy Marcil" <no-...@leaveme.alone> wrote in message
news:Oc2c2hVq...@TK2MSFTNGP15.phx.gbl...
Jean-Guy Marcil
Dans son message, < macropod > écrivait :
In this message, < macropod > wrote:
> Hi Jean-Guy Marcil,
>
> While you may not be able to hide a calculated Form Field, why would you
use
I was able to with a lot of ease, once you know how!
Just because! :-) Greg was curious as to how it could be done, and he
thought it could not be done. Se we just looked for a way to do it.
> one when a simple formula field can return the desired result with a lot
> less fiddling? What I showed is how to use a standard formula field to
take
> the output from two Form Fields and give a 'correct' result when either or
> both Form Fields is empty.
The format/forms of the numbers used to perform the calculation was
irrelevant to the discussion. We were just concerned by the result itself.:
How to hide a calculated formfield if its calculated result is "0"
(Regardless of how that result was achieved)?
Of course you are right, "normal" formula field are much easier and I
usually use those as well.
It was just a discussion for the sake of it. I.e "I think it cannot be
done." "Yes, it can, here's how"
;-)
macropod
A bit like climbing a mountain then: Because it's there!
Salut
"Jean-Guy Marcil" <no-...@leaveme.alone> wrote in message
news:eS%23W5rhq...@TK2MSFTNGP15.phx.gbl...