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]

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











- 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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Kima & Greg Schwend]

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:










- 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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Kima & Greg Schwend]

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 valueso

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









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

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

 

[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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Kima & Greg Schwend wrote:
- Show quoted text -

 

[Kima & Greg Schwend]

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 -







- 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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>