IF field with a formula

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

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" wrote in message
...
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

On Aug 21, 1:00*am, "Doug Robbins - Word MVP"
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" wrote in ...



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

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 wrote:
On Aug 21, 1:00 am, "Doug Robbins - Word MVP"
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" wrote in
...



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

On Aug 21, 8:48*am, "Graham Mayor" 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"
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" wrote in
...

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.

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




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 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.

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 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 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.

On Aug 22, 2:56*am, "Graham Mayor" 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

On Aug 25, 5:34*am, "Kima & Greg Schwend" wrote:
On Aug 22, 2:56*am, "Graham Mayor" 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- 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

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 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