IF field with a formula

[Kima & Greg Schwend]

I have a table that contains form fields that are bookmarked. I want
to use an IF field to prevent a divide by zero error and to display
the result of an equation.

The fields are defined as number fields and bookmarked Text66, Text67,
and Text68.

The formula is =Text66/Text67*Text68

The formula works if I don't use the IF statement. But I want to
display nothing if Text67 is blank or 0.

{ IF Text67 > 0 =Text66/Text67*Text68 \# "0.00" ""}

This just displays =Text66/Text67*Text68 if Text67 is greater than 0.
I've tried to use an extra set of brackets to contain the true result
(by pressing ctrl+F9) but that doesn't work, nor does containing the
true result with quotes.

How can I display my true result as a number and not text?

Thanks

[Doug Robbins - Word MVP]

Use

{ IF { Text67 } <> 0 { = { Text66 } / { Text67 } * { Text68 } \#
.00;-0.00; } "" }

It does not have to be in a formfield, but for Text66, Text67 and Text68 you
will need to check the Calculate on Exit box.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Kima & Greg Schwend" <kima...@gmail.com> wrote in message
news:9bf95d30-7911-4a37...@b15g2000yqd.googlegroups.com...

[Kima & Greg Schwend]

On Aug 21, 1:00 am, "Doug Robbins - Word MVP"

<d...@REMOVECAPSmvps.org> wrote:
> Use
>
> { IF { Text67 } <> 0 { = { Text66 } / { Text67 } * { Text68 } \#
> .00;-0.00;  } "" }
>
> It does not have to be in a formfield, but for Text66, Text67 and Text68 you
> will need to check the Calculate on Exit box.
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

> "Kima & Greg Schwend" <kimag...@gmail.com> wrote in messagenews:9bf95d30-7911-4a37...@b15g2000yqd.googlegroups.com...

>
>
>
> >I have a table that contains form fields that are bookmarked. I want
> > to use an IF field to prevent a divide by zero error and to display
> > the result of an equation.
>
> > The fields are defined as number fields and bookmarked Text66, Text67,
> > and Text68.
>
> > The formula is =Text66/Text67*Text68
>
> > The formula works if I don't use the IF statement. But I want to
> > display nothing if Text67 is blank or 0.
>
> > { IF Text67 > 0 =Text66/Text67*Text68 \# "0.00" ""}
>
> > This just displays =Text66/Text67*Text68 if Text67 is greater than 0.
> > I've tried to use an extra set of brackets to contain the true result
> > (by pressing ctrl+F9) but that doesn't work, nor does containing the
> > true result with quotes.
>
> > How can I display my true result as a number and not text?
>

> > Thanks- Hide quoted text -
>
> - Show quoted text -

Thank you Doug, but it doesn't seem to work.

I typed in your suggestion, using ctrl+F9 to open each bracket. Then I
right clicked and selected 'toggle the field code' to hide the IF
field. I protected my form and filled in the three fields related to
this formula (all have calculate on exit selected). The IF field
shows !Syntax Error, * after filling in the first field; !Unexpected
End of Formula after filling in the second field; a partial result
after filling in the third field. That partial result was the division
part of the formula without the multiplication (Text66/Text67). But
after clearing the three fields and then re-entering them the result
displayed is an incorrect .00

Now I filled in 10, 5, 6 for the text66 thru 68 fields then If I
toggle the field code to view what I typed in then this is displayed:
{ IF 5 <> 0 { = 10 / 5 * 6 \#.00:-0.00; } "" }

I can't seem to view everything I typed in for the IF field. I can use
'Toggle field code' twice to display some of the nested brackets. But
I can't use the 'Edit field' selection from the right click menu.

I know this is simple in Excel, but I have some complicated formatting
in my Word 2003 document and I want to avoid inserting from Excel.

Thanks

[Graham Mayor]

For the calculation to work as Doug intended, without the syntax errors
caused by calculation empty fields, it would be necessary to set the 3 Text
field types to 'Number' and put in a default value of 0.

{ IF{ Text67 } <> 0 { = { Text66 } / { Text67 } * { Text68 } \#

",0.00;-,0.00;" } "" }

The above should then work to two decimal places. However note that the
commas and semi-colons used in the formatting switch -
",0.00;-,0.00;"
are language specific - here English - and the switch will produce some odd
results if you use the wrong thousands and list separators for your local
language.

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

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

[Kima & Greg Schwend]

On Aug 21, 8:48 am, "Graham Mayor" <gma...@REMOVETHISmvps.org> wrote:
> For the calculation to work as Doug intended, without the syntax errors

> caused by calculation empty fields, it would be necessary to set the 3 Textfieldtypes to 'Number' and put in a default value of 0.

>
> {IF{ Text67 } <> 0  { = { Text66 } / { Text67 } * { Text68 } \#
> ",0.00;-,0.00;" } "" }
>
> The above should then work to two decimal places. However note that the
> commas and semi-colons used in the formatting switch -
> ",0.00;-,0.00;"
> are language specific - here English - and the switch will produce some odd

> resultsifyou use the wrong thousands and list separators for your local

> language.
>
> --
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Graham Mayor -  Word MVP
>
> My web sitewww.gmayor.com

> Word MVP web sitehttp://word.mvps.org

> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>
> Kima & Greg Schwend wrote:
>
>
>
> > On Aug 21, 1:00 am, "Doug Robbins - Word MVP"
> > <d...@REMOVECAPSmvps.org> wrote:
> >> Use
>
> >> {IF{ Text67 } <> 0 { = { Text66 } / { Text67 } * { Text68 } \#
> >> .00;-0.00; } "" }
>
> >> It does not have to be in a formfield, but for Text66, Text67 and
> >> Text68 you
> >> will need to check the Calculate on Exit box.
>
> >> --
> >> Hope this helps.
>
> >> Please reply to the newsgroup unless you wish to avail yourself of my
> >> services on a paid consulting basis.
>
> >> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
> >> "Kima & Greg Schwend" <kimag...@gmail.com> wrote in
> >> messagenews:9bf95d30-7911-4a37...@b15g2000yqd.googlegroups.com...
>
> >>> I have a table that contains form fields that are bookmarked. I want

> >>> to use anIFfieldto prevent a divide by zero error and to display

> >>> the result of an equation.
>
> >>> The fields are defined as number fields and bookmarked Text66,
> >>> Text67, and Text68.
>
> >>> Theformulais =Text66/Text67*Text68
>

> >>> TheformulaworksifI don't use theIFstatement. But I want to

> >>> display nothingifText67 is blank or 0.
>

> >>> {IFText67 > 0 =Text66/Text67*Text68 \# "0.00" ""}
>
> >>> This just displays =Text66/Text67*Text68ifText67 is greater than

> >>> 0. I've tried to use an extra set of brackets to contain the true
> >>> result (by pressing ctrl+F9) but that doesn't work, nor does
> >>> containing the true result with quotes.
>
> >>> How can I display my true result as a number and not text?
>
> >>> Thanks- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thank you Doug, but it doesn't seem to work.
>
> > I typed in your suggestion, using ctrl+F9 to open each bracket. Then I
> > right clicked and selected 'toggle thefieldcode' to hide theIF
> >field. I protected my form and filled in the three fields related to

> > thisformula(all have calculate on exit selected). TheIFfield

> > shows !Syntax Error, * after filling in the firstfield; !Unexpected

> > End ofFormula after filling in the secondfield; a partial result
> > after filling in the thirdfield. That partial result was the division
> > part of theformulawithout the multiplication (Text66/Text67). But

> > after clearing the three fields and then re-entering them the result
> > displayed is an incorrect .00
>
> > Now I filled in 10, 5, 6 for the text66 thru 68 fields thenIfI

> > toggle thefieldcode to view what I typed in then this is displayed:
> > {IF5 <> 0 { = 10 / 5 * 6 \#.00:-0.00; } "" }

>
> > I can't seem to view everything I typed in for theIFfield. I can use

> > 'Togglefieldcode' twice to display some of the nested brackets. But
> > I can't use the 'Editfield' selection from the right click menu.

>
> > I know this is simple in Excel, but I have some complicated formatting
> > in my Word 2003 document and I want to avoid inserting from Excel.
>

> > Thanks- Hide quoted text -
>
> - Show quoted text -

Thank you for the help,

Adding the default value of zero to each of the three number fields
has successfully produced the result of a blank field or a number. I
think part of the solution was for me to skip toggling the field code
to hide the formula before protecting the form. I skipped that and
went straight to protecting the form which hid the code for me.

But there are a couple problems. If a form user overtypes the default
0 in one of the three number fields then deletes any typing in that
field (blank field) then I get the !Syntax Error.

If a user types in text into a number field then I get an !Undefined
Bookmark error.

How can I trap these errors? I can't use macros because of the user
base. And I don't want to import from Excel.

Thank you.

[Graham Mayor]

If you want to error check without macros then I suspect the best you can do
is to set the field result to 0 when the field is either empty or contains
text. Something along the lines of

{ SET A { IF { ={ Text66 } * 1 } = "!*" "0" "{ Text66 }" } }{ SET B { IF
{ ={ Text67 } * 1 } = "!*" "0" "{ Text67 }" } }{ SET C { IF { ={ Text68 }
* 1 } = "!*" "0" "{ Text68 }" } }{ IF { B } <> 0 "{ ={ A }/{ B } * { C }
\# ",0.00;-,0.00;" }" ""}

should do that.

Essentially it tests each field for numeric content by multiplying the field
result by a number (here 1) which will produce an error message that begins
with an exclamation mark. If the result is that exclamation mark it sets a
new bookmark with the value 0 or if not it sets the bookmark with the value
of the field. This is repeated for each field then the calculation is based
on the new bookmark names and not the field names. The only problem is there
is no feedback to the user if he makes the type of error you have raised.

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

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

[Graham Mayor]

On further reflection and assuming that your numbers are less than a
million, you could trap a bit more thoroughly and give the user some
feedback, but the limit is the possibility that Text67 may have a 0 value so

{ SET A { IF { ={ Text66 } * 1 } = "!*" "9999999" "{ Text66 }" } }{ SET B

{ IF { ={ Text67 } * 1 } = "!*" "0" "{ Text67 }" } }{ SET C { IF { ={

Text68 } * 1 } = "!*" "9999999" "{ Text68 }" } }{ IF { B } <> 0 "{ IF{
={ A }/{ B } * { C }} < 1000000 "{ ={ A }/{ B } * { C } \#
",0.00;-,0.00;" }" "Incorrect field data entry!" }" ""}

You will still get no feedback if an incorrect entry is made in Text67 - and
I cannot immediately see a way around that.

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

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

Graham Mayor wrote:
> If you want to error check without macros then I suspect the best you
> can do is to set the field result to 0 when the field is either empty
> or contains text. Something along the lines of
>
> { SET A { IF { ={ Text66 } * 1 } = "!*" "0" "{ Text66 }" } }{ SET B
> { IF { ={ Text67 } * 1 } = "!*" "0" "{ Text67 }" } }{ SET C { IF {
> ={ Text68 } * 1 } = "!*" "0" "{ Text68 }" } }{ IF { B } <> 0 "{ ={
> A }/{ B } * { C } \# ",0.00;-,0.00;" }" ""}
>
> should do that.
>
> Essentially it tests each field for numeric content by multiplying
> the field result by a number (here 1) which will produce an error
> message that begins with an exclamation mark. If the result is that
> exclamation mark it sets a new bookmark with the value 0 or if not it
> sets the bookmark with the value of the field. This is repeated for
> each field then the calculation is based on the new bookmark names
> and not the field names. The only problem is there is no feedback to
> the user if he makes the type of error you have raised.
>
>

[Kima & Greg Schwend]

On Aug 22, 2:56 am, "Graham Mayor" <gma...@REMOVETHISmvps.org> wrote:
> On further reflection and assuming that your numbers are less than a
> million, you could trap a bit more thoroughly and give the user some
> feedback, but the limit is the possibility that Text67 may have a 0 value so
>
> { SET A {IF{ ={ Text66 } * 1 } =  "!*" "9999999" "{ Text66 }" } }{ SET B
> {IF{ ={ Text67 } * 1  } =  "!*" "0" "{ Text67 }" } }{ SET C  {IF{ ={
> Text68 } * 1  } =  "!*" "9999999" "{ Text68 }" } }{IF{ B } <> 0  "{IF{
> ={ A }/{ B } * { C }} < 1000000  "{ ={ A }/{ B } * { C } \#

> ",0.00;-,0.00;" }" "Incorrectfielddata entry!" }" ""}
>
> You will still get no feedbackifan incorrect entry is made in Text67 - and

> I cannot immediately see a way around that.
>
> --
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Graham Mayor -  Word MVP
>

> My web sitewww.gmayor.com
> Word MVP web sitehttp://word.mvps.org
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>
>
>

> Graham Mayor wrote:
> >Ifyou want to error check without macros then I suspect the best you
> > can do is to set thefieldresult to 0 when thefieldis either empty

> > or contains text. Something along the lines of
>
> > { SET A {IF{ ={ Text66 } * 1 } =  "!*" "0" "{ Text66 }" } }{ SET B
> > {IF{ ={ Text67 } * 1  } =  "!*" "0" "{ Text67 }" } }{ SET C  {IF{
> > ={ Text68 } * 1  } =  "!*" "0" "{ Text68 }" } }{IF{ B } <> 0  "{ ={
> > A }/{ B } * { C } \# ",0.00;-,0.00;" }" ""}
>
> > should do that.
>
> > Essentially it tests eachfieldfor numeric content by multiplying

> > thefieldresult by a number (here 1) which will produce an error
> > message that begins with an exclamation mark.Ifthe result is that
> > exclamation mark it sets a new bookmark with the value 0 orifnot it

> > sets the bookmark with the value of thefield. This is repeated for

> > eachfieldthen the calculation is based on the new bookmark names
> > and not thefieldnames. The only problem is there is no feedback to
> > the userifhe makes the type of error you have raised.

>
> > Kima & Greg Schwend wrote:
> >> Thank you for the help,
>
> >> Adding the default value of zero to each of the three number fields

> >> has successfully produced the result of a blankfieldor a number. I

> >> think part of the solution was for me to skip toggling thefieldcode

> >> to hide theformulabefore protecting the form. I skipped that and

> >> went straight to protecting the form which hid the code for me.
>

> >> But there are a couple problems.Ifa form user overtypes the default

> >> 0 in one of the three number fields then deletes any typing in that

> >>field(blankfield) then I get the !Syntax Error.
>
> >>Ifa user types in text into a numberfieldthen I get an !Undefined

> >> Bookmark error.
>
> >> How can I trap these errors? I can't use macros because of the user
> >> base. And I don't want to import from Excel.
>

> >> Thank you.- Hide quoted text -

>
> - Show quoted text -

Thank you,

That suggestion for error trapping does everything I need for this
form. I really appreciate your help.

Greg

[Kima & Greg Schwend]

> Greg- Hide quoted text -

>
> - Show quoted text -

I find that I have one more issue with my form. Even though I protect
the document my text fields allow the user to press the Enter key.
This inserts one or more paragraph marks and it increases the row
height with each press of the Enter key. I specified row height
exactly, and tried selecting 'fit text' in the cell options. But with
a simple press of the Enter key the user can completely distort my
form appearance. I have about 75 text fields. Is there a way that Word
2003 can control row height in my protected document?

Thanks

[Graham Mayor]

You can fix the row height and column width of table cells to an exact size,
which will stop the table from growing when enter is being pressed.
If you set the row height to an exact figure (not 'at least') then provided
you have set an exact row height that will only accommodate the text you
want to allow, pressing enter will not cause the table row height to change.

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

My web site www.gmayor.com

Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

[Kima & Greg Schwend]

On Aug 26, 5:50 am, "Graham Mayor" <gma...@REMOVETHISmvps.org> wrote:
> You can fix the row height and column width of table cells to an exact size,
> which will stop the table from growing when enter is being pressed.
> If you set the row height to an exact figure (not 'at least') then provided
> you have set an exact row height that will only accommodate the text you
> want to allow, pressing enter will not cause the table row height to change.
>
> --
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Graham Mayor -  Word MVP
>

> My web sitewww.gmayor.com
> Word MVP web sitehttp://word.mvps.org

> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>
> Kima & Greg Schwend wrote:
>  - Show quoted text -
>
>
>
>
>
> > I find that I have one more issue with my form. Even though I protect
> > the document my text fields allow the user to press the Enter key.
> > This inserts one or more paragraph marks and it increases the row
> > height with each press of the Enter key. I specified row height
> > exactly, and tried selecting 'fit text' in the cell options. But with
> > a simple press of the Enter key the user can completely distort my
> > form appearance. I have about 75 text fields. Is there a way that Word
> > 2003 can control row height in my protected document?
>

> > Thanks- Hide quoted text -
>

> - Show quoted text -

Thank you,

I've tried that but there was a complication...
Some of the rows have cells that are split in two forming two half-
height cells, one above the other. I've found that I have to select
'Exact' row height on any row near the row containing form fields, not
just the row that contains the fields. Now it works correctly.

Thanks

[Graham Mayor]

Split cells can really screw up your day ;)

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

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