If calculation field has 0 result, blank

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cksiz - 22 Oct 2007 22:21 GMT

I have several rows in a table, with the last column being a calculated form
field of A1*B1.
The table has 10 rows, but the user might only fill in 1 or 2 of the rows.
For the rows that have no data (essentially, blank Col1 and Col2), the total
column always displays a 0 value.
How can I make this field display BLANK (or null if you will) if Col1 and
Col2 are blank?
I know a formula field will do this, but I need it to be a form field,
because I use these field further down in more calculations. (essentially
need the bookmark name, and a formula field in word does not have a name, so
I must use a form field)

I have tried formatting the field, as suggested in this webpage
http://www.gmayor.com/formatting_word_fields.htm

And putting the switch as part of the formatting, however, to no avail.
\# "$0;($0);"

Reply to this Message

Graham Mayor - 23 Oct 2007 07:45 GMT

Wrap a QUOTE field around your calculated form field and add the switch to
the Quote field instead eg

{ QUOTE{ FORMTEXT { =Text1 * Text2 }} \# ",$0.00;;"}

or if the result could be negative

{ QUOTE{ FORMTEXT { =Text1 * Text2 }} \# ",$0.00;(,$0.00);"}

Signature

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

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

> I have several rows in a table, with the last column being a
> calculated form field of A1*B1.
[quoted text clipped - 13 lines]
> And putting the switch as part of the formatting, however, to no
> avail. \# "$0;($0);"

Reply to this Message

macropod - 23 Oct 2007 09:04 GMT

Hi cksiz,

Save yourself some grief, use a formula field. To see why, check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442
or
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the item titled 'Calculations In Word Forms'. If you need to use the results in later calculations, you can
simply bookmark the formula field and cross-reference that.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

>I have several rows in a table, with the last column being a calculated form
> field of A1*B1.
[quoted text clipped - 13 lines]
> And putting the switch as part of the formatting, however, to no avail.
> \# "$0;($0);"

Reply to this Message

cksiz - 23 Oct 2007 22:22 GMT

Thank you. That document helped me in many other areas as well.

However, using a formula field for division of cells in the same table,
displays "!Zero Divide" until I have completed filling in the fields that are
used in the divide result.

For the fields that I am filling in, I have tried using the "Calculate on
exit" being both ON and OFF, but still get the zero divide message in my
formula field.

Is there a way to tell the formula field not to display anything until it
actually has valid numbers to perform the formula on?

> Hi cksiz,
>
[quoted text clipped - 23 lines]
> > And putting the switch as part of the formatting, however, to no avail.
> > \# "$0;($0);"

Reply to this Message

macropod - 23 Oct 2007 23:43 GMT

Hi cksiz,

You can suppress the zero divide message via an IF test. For example:
{IF{=Divisor}= 0 "" {=Dividend/Divisor}}
where 'Dividend' & 'Divisor are the bookmark names of the relevant formfields. You may also want to add an appropriate numeric
picture switch to format the 'correct' division result.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> Thank you. That document helped me in many other areas as well.
>
[quoted text clipped - 37 lines]
>> > And putting the switch as part of the formatting, however, to no avail.
>> > \# "$0;($0);"

Reply to this Message

cksiz - 24 Oct 2007 00:11 GMT

Thats just bootiful.
Thank you very much.

> Hi cksiz,
>
[quoted text clipped - 45 lines]
> >> > And putting the switch as part of the formatting, however, to no avail.
> >> > \# "$0;($0);"

Reply to this Message

cksiz - 24 Oct 2007 16:46 GMT

But is a "blank" form field, actually 0?
I tried the IF logic, however now the results do not display anything.
Its almost like if the form field is "blank", there is SOMETHING in there,
but what?
With the IF now, even if I put values in my dividend and divisor, it will
not display the results.

Sorry, this must seem very basic to you, but I guess I'm just not
understanding how word handles null form fields.
My formula is below. all values are within the same table, so I am using
cell references (just to note, using the bookmark name for the form fields
produces the same results)

{IF{=B4} = 0 "" {=B4/B6} }

also tried
{IF{=Bkmrk1} = 0 "" {=Bkmrk1/Bkmrk3} }
{IF{=Bkmrk1} = "" "" {=Bkmrk1/Bkmrk3} }
also tried unchecking "calculate on exit" on all fields, except the last one
(essentially, dont calculate anything until the last field has been entered)
But as soon as you hit protect, the formula field does its evaluation, or
leaves the value from the last time i tried.

> Hi cksiz,
>
[quoted text clipped - 45 lines]
> >> > And putting the switch as part of the formatting, however, to no avail.
> >> > \# "$0;($0);"

Reply to this Message

macropod - 24 Oct 2007 21:40 GMT

Hi cksiz,

You need to ensure all those braces (ie '{ }') are created via Ctrl-F9 - you con't simply type them in. You also need to make sure
the formfields in B4 & B6 both have their properties set to 'calculate on exit'.

Your {IF{=B4} = 0 "" {=B4/B6} } construction should then work fine.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> But is a "blank" form field, actually 0?
> I tried the IF logic, however now the results do not display anything.
[quoted text clipped - 68 lines]
>> >> > And putting the switch as part of the formatting, however, to no avail.
>> >> > \# "$0;($0);"

Reply to this Message