 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Tables / October 2004blank when zero
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. teachelr, 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}}  SignatureGreg Maxey/Word MVP A Peer in Peer to Peer Support > 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 [quoted text clipped - 3 lines] > > thanks is advance. 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 SignatureSuzanne 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. > 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, [quoted text clipped - 3 lines] > > thanks is advance. This is covered in the http://www.gmayor.com/formatting_word_fields.htm page on my web site. The required syntax would be Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Look in Word's Help for the "numeric picture switch" topic and look > specifically for the "positive, negative, zero" format. An example of [quoted text clipped - 26 lines] >> >> thanks is advance. Oops - pressed the wrong key - my post should have finished The correct syntax would be \# "$,0.00;(,$0.00);" Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Look in Word's Help for the "numeric picture switch" topic and look > specifically for the "positive, negative, zero" format. An example of [quoted text clipped - 26 lines] >> >> thanks is advance. Suzanne/Graham, 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. >-----Original Message----- >Look in Word's Help for the "numeric picture switch" topic and look [quoted text clipped - 26 lines] > >. These same numeric pictures are available for Number and Calculation fields in forms. His calculated result is a zero and he doesn't want to display a zero; this numeric picture will suppress the zero. SignatureSuzanne 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. > Suzanne/Graham, > [quoted text clipped - 51 lines] > > > >. 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. If you can't confirm this then something screwy is going on in both my 2000 and 2003 versions. >-----Original Message----- >These same numeric pictures are available for Number and Calculation fields [quoted text clipped - 58 lines] > >. Bonjour, Dans son message, < Greg > ?crivait : In this message, < Greg > wrote: || Suzanne, || [quoted text clipped - 11 lines] || #,##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. SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org 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. SignatureGreg Maxey/Word MVP A Peer in Peer to Peer Support > Bonjour, > [quoted text clipped - 35 lines] > then something like what you posted would be necessary to handle the > Divide by zero error message. Bonjour, 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 . || [quoted text clipped - 6 lines] || 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... SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org 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. SignatureGreg Maxey/Word MVP A Peer in Peer to Peer Support > Bonjour, > [quoted text clipped - 23 lines] > > The switch works really well over here... Bonjour, 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. SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org JGM, Once again a session with you is better than school of the boat :-). Thanks. SignatureGreg Maxey/Word MVP A Peer in Peer to Peer Support > Bonjour, > [quoted text clipped - 25 lines] > > Protect the form, now it should work. Bonjour, 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! SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org 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. Cheers > Bonjour, > [quoted text clipped - 26 lines] > > Protect the form, now it should work. Bonjour, Dans son message, < macropod > ?crivait : In this message, < macropod > wrote: || Hi Guys, || [quoted text clipped - 3 lines] || 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. SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org 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. 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 > Bonjour, > [quoted text clipped - 17 lines] > stick through the dialog. That is why I mentioned "Cheating" as it was the > only way I found to do it easily and quickly. Bonjour, 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? SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org Hi Jean-Guy Marcil, 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 > Bonjour, > [quoted text clipped - 36 lines] > > Does that make any sense now? Bonjour, 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" ;-) SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org Hi, A bit like climbing a mountain then: Because it's there! Salut > Bonjour, > [quoted text clipped - 28 lines] > > ;-) |
Reply to this Message |
 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.