IF field with a formula
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.
|