A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

MS Word table - problem with conditional formula



 
 
Thread Tools Display Modes
  #1  
Old April 4th, 2006, 04:50 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

I have set up in MS Word a main document and source document, which performs
a mail merge to produce a Form Letter. The mail merge works OK and the
appropriate Merge Fields are correctly inserted into the Form Letter. The
main document is an advice to individuals and team leaders re competitions
they wish to enter and includes a table, the first three columns of which are
– Col A – Competition Name, Col B – Accepted and Col C Entry Fee. Row 1
contains the column headings and in Rows 2 to 12 the names of various
competitions. In Row 13 cell C13 has the formula: -

{=Sum(C2:C12)\#”£#,##0.00;(£#,##0.00)”} and correctly shows the total of any
amounts (entered manually) in Col C.

I am having problems in getting a conditional formula to work. In Col B an X
will signify acceptance of an entry (in the competition for that row) and in
Col C the formula is required to calculate the entry fee, which will vary
according to the number of people in the team for that competition. I
envisage something like: –

{=IF B2=X Product (2.90,2)}. With an appropriate number format (#,##0.00)
inserted in the formula dialog box this should produce 5.80 in cell C2
provided B2 has X, (or blank if not X), and likewise in other cells in Col C
with a similar formula.

I know the ‘curly brackets’ have to be inserted by using Ctrl + F9 and not
typed in from the keyboard. However all my attempts seem to produce a Syntax
Error in cell C2. On one occasion I did manage to update C2 by using F9 but
when I removed the X from B2 and did another update the 5.90 remained in C2
(instead of a blank because there was no acceptance for that competition).

The following formula: - {IF {B2X,””,{=PRODUCT(2.9,2)\#”#,##0.00”}}} was
accepted in C2 without any error message, but when the cell was selected and
updated by F9 no result was displayed whether or not B2 had an X. I then
tried inserting an = sign before the IF and was rewarded with: - !Unexpected
end of formula.

All suggestions for resolving this problem gratefully received.
  #2  
Old April 6th, 2006, 01:07 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Please can someone tell me what is wrong with the syntax in the following
formula: -

{=IF(B3="X",PRODUCT(2.9,2),"")}

The ’curly brackets’ (braces) were inserted into the cell using Ctrl + F9. I
constructed the formula in the cell originally from the keyboard and got a !
Syntax Error, “ message. I then deleted the formula and constructed the
formula in an Excel worksheet and it worked. I then copied the formula to
the clipboard, went back to the (Word 2000) document table, inserted the
braces into the cell and then pasted the formula between the braces and again
got the same error message as before. Does anyone know the significance of
the double quotes at the end of the message?

From experimenting it seems the problem is to do with the IF condition,
because a straightforward function - PRODUCT(2.9,2) - with appropriate number
formatting gives the correct result i.e. 5.80

To get the job finished I have had to manually update my Form Letter but
would like to get this problem sorted so that I can automate the job next
time I use it.

Peter

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1
  #3  
Old April 6th, 2006, 04:24 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

I think you're trying to use Excel syntax in Word. Word's syntax would be:

{ IF B3 = "X" { = PRODUCT (2.9,2) } "" }

or just

{ IF B3 = "X" { = 2.9 * 2 } "" }

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Peterkins via OfficeKB.com" u20492@uwe wrote in message
news:5e5ece24139be@uwe...
Please can someone tell me what is wrong with the syntax in the following
formula: -

{=IF(B3="X",PRODUCT(2.9,2),"")}

The ’curly brackets’ (braces) were inserted into the cell using Ctrl + F9.

I
constructed the formula in the cell originally from the keyboard and got a

!
Syntax Error, “ message. I then deleted the formula and constructed the
formula in an Excel worksheet and it worked. I then copied the formula to
the clipboard, went back to the (Word 2000) document table, inserted the
braces into the cell and then pasted the formula between the braces and

again
got the same error message as before. Does anyone know the significance

of
the double quotes at the end of the message?

From experimenting it seems the problem is to do with the IF condition,
because a straightforward function - PRODUCT(2.9,2) - with appropriate

number
formatting gives the correct result i.e. 5.80

To get the job finished I have had to manually update my Form Letter but
would like to get this problem sorted so that I can automate the job next
time I use it.

Peter

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1


  #4  
Old April 7th, 2006, 12:05 AM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Hi Suzanne

Thanks for your response. If one tries to construct the formula from the
Word menu bar - Table Formula, the Formula dialogue appears and any formula
constructed appears in the cell with a pair of braces around it. However if
one pastes the IF function into the formula line it then appears as "=IF( )"
(without the quotes of course). I have been unable to find any way to insert
a pair of braces in the formula line. So I have constructed the formula
directly in the table cell using Ctrl + F9 to insert the braces then keying
in your suggested syntax. The formula was accepted into the cell without any
error message, but when I selected the cell and updated it with F9 the cell
remained blank even though there was an X in B3. Your alternative also
performed similarly.

It seems the inclusion of spaces does make a difference. In the following I
have put asterisks where spaces occur: -

{*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the
cell is blank when updated.

{*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of the
first "=" causes a X to appear in the cell when it is updated.

Unfortunately I am no further forward in finding what is wrong with the
syntax of my version. Please may I ask you if you have actually tried to get
this working on your system; I would be very interested to know.



Suzanne S. Barnhill wrote:
I think you're trying to use Excel syntax in Word. Word's syntax would be:

{ IF B3 = "X" { = PRODUCT (2.9,2) } "" }

or just

{ IF B3 = "X" { = 2.9 * 2 } "" }


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1
  #5  
Old April 7th, 2006, 03:52 AM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Well, this was pretty bizarre, but here's the answer:

{ IF B2=X { = PRODUCT (2.9,2} "" }

That is, remove the quotes around "X." The spaces around the first = are
crucial, but none of the other spaces matter, and it works with or without
quotes around the IF field.

In most case spaces make no difference; Word inserts and in some cases
insists on spaces between the field delimiters and the contents; spaces
around operators can be more touchy.

Also, I realize this is probably just a sample/example/test, but if your
actual field were going to be the product of 2.9 and 2, it would make a lot
more sense to just put "5.8" instead of { = PRODUCT (2.9,2) }.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Peterkins via OfficeKB.com" u20492@uwe wrote in message
news:5e648d56f3b52@uwe...
Hi Suzanne

Thanks for your response. If one tries to construct the formula from the
Word menu bar - Table Formula, the Formula dialogue appears and any

formula
constructed appears in the cell with a pair of braces around it. However

if
one pastes the IF function into the formula line it then appears as

"=IF( )"
(without the quotes of course). I have been unable to find any way to

insert
a pair of braces in the formula line. So I have constructed the formula
directly in the table cell using Ctrl + F9 to insert the braces then

keying
in your suggested syntax. The formula was accepted into the cell without

any
error message, but when I selected the cell and updated it with F9 the

cell
remained blank even though there was an X in B3. Your alternative also
performed similarly.

It seems the inclusion of spaces does make a difference. In the following

I
have put asterisks where spaces occur: -

{*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the
cell is blank when updated.

{*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of

the
first "=" causes a X to appear in the cell when it is updated.

Unfortunately I am no further forward in finding what is wrong with the
syntax of my version. Please may I ask you if you have actually tried to

get
this working on your system; I would be very interested to know.



Suzanne S. Barnhill wrote:
I think you're trying to use Excel syntax in Word. Word's syntax would

be:

{ IF B3 = "X" { = PRODUCT (2.9,2) } "" }

or just

{ IF B3 = "X" { = 2.9 * 2 } "" }


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1


  #6  
Old April 7th, 2006, 01:27 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Hi Peterkins,

A formula in Column C like:
{IF B2=X {=2*2.9} \# £,0.00}
should give the result you're after. However, this requires hard-coding both
the entry fee and the number of entrants. If you set your table up so that
Column C has the number of entries, and Column D has the fee per entry, you
could use:
{IF B2=X {=C2*D2} \# £,0.00}
in Column E.

For more on Word field maths, check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/show...?Number=365442

Cheers


"Peterkins via OfficeKB.com" u20492@uwe wrote in message
news:5e648d56f3b52@uwe...
Hi Suzanne

Thanks for your response. If one tries to construct the formula from the
Word menu bar - Table Formula, the Formula dialogue appears and any

formula
constructed appears in the cell with a pair of braces around it. However

if
one pastes the IF function into the formula line it then appears as

"=IF( )"
(without the quotes of course). I have been unable to find any way to

insert
a pair of braces in the formula line. So I have constructed the formula
directly in the table cell using Ctrl + F9 to insert the braces then

keying
in your suggested syntax. The formula was accepted into the cell without

any
error message, but when I selected the cell and updated it with F9 the

cell
remained blank even though there was an X in B3. Your alternative also
performed similarly.

It seems the inclusion of spaces does make a difference. In the following

I
have put asterisks where spaces occur: -

{*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the
cell is blank when updated.

{*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of

the
first "=" causes a X to appear in the cell when it is updated.

Unfortunately I am no further forward in finding what is wrong with the
syntax of my version. Please may I ask you if you have actually tried to

get
this working on your system; I would be very interested to know.



Suzanne S. Barnhill wrote:
I think you're trying to use Excel syntax in Word. Word's syntax would

be:

{ IF B3 = "X" { = PRODUCT (2.9,2) } "" }

or just

{ IF B3 = "X" { = 2.9 * 2 } "" }


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1



  #7  
Old April 7th, 2006, 02:19 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Hi Suzanne

This problem seems to be getting even more bizarre. First if I may explain I
used the Product function because the Entry Fee can vary from time to time.
Because I was trying to get this thing working by taking one step at a time,
I used a fixed fee in the formula, but intended to substitute the result of
an ASK field into the formula once the formula is made to work properly. My
main document has two ASK fields one for a date and the other for the Entry
Fee. As you doubtless know, if the whole document is selected (Ctrl + A) and
F9 to update, Word displays the prompts for the ASK fields which are then
updated along with the results from the formulae in each of the rows in
Column C.

I used your revised syntax first with the fixed fee and then substituting the
ASK field { EntryFee } in the formula and got the correct results with both
versions and using various amounts for fees. However once an update had
produced a value in C, removing the X from Col B and again updating, the
previously calculated value remained in C. I made some further experiments
and then found that the calculation is performed whether or not there is an X
in Col B!!

This is how my formula now looks (* = space): - {*IF*B2=X*{*PRODUCT(
{*EntryFee*},2)\#*#0.00*}""*}

What further thoughts do you have please?

With many thanks for your time and patience.

Peter

Suzanne S. Barnhill wrote:
Well, this was pretty bizarre, but here's the answer:

{ IF B2=X { = PRODUCT (2.9,2} "" }

That is, remove the quotes around "X." The spaces around the first = are
crucial, but none of the other spaces matter, and it works with or without
quotes around the IF field.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1
  #8  
Old April 7th, 2006, 02:44 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Hi Peterkins,

Table cell referencing doesn't work with text strings. To refer to a
character string in a cell, the character string (not the cell itself) must
be individually bookmarked and the bookmark referred to in the reference. So
my previous post on this was wrong (Should have read my own tutorial)!

So, if you bookmark the text range in cell B2 with something like 'Cell2B'
you could use a field coded like:
{IF{Cell2B}= X {=EntryFee*2} \# £,0.00}
Since maintaining the bookmark in such a field can be a PITA, you might find
it easiest to add a FILLIN field to cell B2 and bookmark that. Code the
FILLIN field something like:
{FILLIN "Enter 'X' if accepted"}

Cheers


"Peterkins via OfficeKB.com" u20492@uwe wrote in message
news:5e6c0184d18e9@uwe...
Hi Suzanne

This problem seems to be getting even more bizarre. First if I may

explain I
used the Product function because the Entry Fee can vary from time to

time.
Because I was trying to get this thing working by taking one step at a

time,
I used a fixed fee in the formula, but intended to substitute the result

of
an ASK field into the formula once the formula is made to work properly.

My
main document has two ASK fields one for a date and the other for the

Entry
Fee. As you doubtless know, if the whole document is selected (Ctrl + A)

and
F9 to update, Word displays the prompts for the ASK fields which are then
updated along with the results from the formulae in each of the rows in
Column C.

I used your revised syntax first with the fixed fee and then substituting

the
ASK field { EntryFee } in the formula and got the correct results with

both
versions and using various amounts for fees. However once an update had
produced a value in C, removing the X from Col B and again updating, the
previously calculated value remained in C. I made some further

experiments
and then found that the calculation is performed whether or not there is

an X
in Col B!!

This is how my formula now looks (* = space): - {*IF*B2=X*{*PRODUCT(
{*EntryFee*},2)\#*#0.00*}""*}

What further thoughts do you have please?

With many thanks for your time and patience.

Peter

Suzanne S. Barnhill wrote:
Well, this was pretty bizarre, but here's the answer:

{ IF B2=X { = PRODUCT (2.9,2} "" }

That is, remove the quotes around "X." The spaces around the first = are
crucial, but none of the other spaces matter, and it works with or

without
quotes around the IF field.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1



  #9  
Old April 7th, 2006, 05:24 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

Hi macropod

Thanks for your help, but I am still a bit stuck. I have not had experience
with bookmarking before but have tried to follow your instructions. Although
I don't quite understand how/why maintaining a book mark can be a pain, I
opted for your suggestion to create a FILLIN field and this is what I have
entered (again * = space): -

{*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*}

(I don't understand the Mergeformat bit but presume it has been put there by
Word because this is a main document for a mail merge.) I selected all of
the Fillin and using Insert Bookmark named it CellB2.

The formula in C2 is now: - {*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0.
00*}""*}

I ran a mail merge and after answering the ASK prompts I had an error message
"A field calculation error occurred in record " for each record in the
source document, and all the sections of the resulting Form Letter had "=X"
(without quotes) in C2. I didn't get any prompt to "Enter X if entering
Men's Singles" even thought the FILLIN switch was set to \d.

Any further ideas please?

Regards

Peter



macropod wrote:
Hi Peterkins,

Table cell referencing doesn't work with text strings. To refer to a
character string in a cell, the character string (not the cell itself) must
be individually bookmarked and the bookmark referred to in the reference. So
my previous post on this was wrong (Should have read my own tutorial)!

So, if you bookmark the text range in cell B2 with something like 'Cell2B'
you could use a field coded like:
{IF{Cell2B}= X {=EntryFee*2} \# £,0.00}
Since maintaining the bookmark in such a field can be a PITA, you might find
it easiest to add a FILLIN field to cell B2 and bookmark that. Code the
FILLIN field something like:
{FILLIN "Enter 'X' if accepted"}


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1
  #10  
Old April 7th, 2006, 06:03 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default MS Word table - problem with conditional formula

You might try an ASK field rather than a fill-in. That will let you set the
bookmark directly. This makes up for the bookmark's fragility. You can find
an ASK and FILL-IN field tutorial at
http://addbalance.com/word/download.htm.

I would suggest adding spaces around the equals sign and quotation marks
around the comparison items and around your True result as well. I also
added a REF field.
{*IF*"{*REF*CellB2*}"*=*"X"*"{*PRODUCT({*EntryFee* },2)\#*#0.00*}"*""*}

--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.


"Peterkins via OfficeKB.com" u20492@uwe wrote in message
news:5e6d9e84e6a5d@uwe...
Hi macropod

Thanks for your help, but I am still a bit stuck. I have not had
experience
with bookmarking before but have tried to follow your instructions.
Although
I don't quite understand how/why maintaining a book mark can be a pain, I
opted for your suggestion to create a FILLIN field and this is what I have
entered (again * = space): -

{*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*}

(I don't understand the Mergeformat bit but presume it has been put there
by
Word because this is a main document for a mail merge.) I selected all of
the Fillin and using Insert Bookmark named it CellB2.

The formula in C2 is now: -
{*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0.
00*}""*}

I ran a mail merge and after answering the ASK prompts I had an error
message
"A field calculation error occurred in record " for each record in the
source document, and all the sections of the resulting Form Letter had
"=X"
(without quotes) in C2. I didn't get any prompt to "Enter X if entering
Men's Singles" even thought the FILLIN switch was set to \d.

Any further ideas please?

Regards

Peter



macropod wrote:
Hi Peterkins,

Table cell referencing doesn't work with text strings. To refer to a
character string in a cell, the character string (not the cell itself)
must
be individually bookmarked and the bookmark referred to in the reference.
So
my previous post on this was wrong (Should have read my own tutorial)!

So, if you bookmark the text range in cell B2 with something like 'Cell2B'
you could use a field coded like:
{IF{Cell2B}= X {=EntryFee*2} \# £,0.00}
Since maintaining the bookmark in such a field can be a PITA, you might
find
it easiest to add a FILLIN field to cell B2 and bookmark that. Code the
FILLIN field something like:
{FILLIN "Enter 'X' if accepted"}


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...ables/200604/1



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to put graphics on envelopes? Steve Koenig General Discussion 20 September 14th, 2007 04:40 PM
Converting WordPerfect 12 files to Word 2003 Curious New Users 3 March 29th, 2006 01:05 AM
WP merge file to Word sstires Tables 4 February 14th, 2006 07:26 PM
How to change merge forms from Word Perfect to Microsoft Word Charles Kenyon General Discussion 1 December 30th, 2004 04:35 PM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM


All times are GMT +1. The time now is 09:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.