Results 1 to 10 of 10

20020830, 01:18 #1
 Join Date
 Dec 2000
 Location
 Los Angeles Area, California, USA
 Posts
 7,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
IF field Formula (Word 2000/SR1)
Hi All:
I'm having a <img src=/S/meltdown.gif border=0 alt=meltdown width=15 height=15> . In a table, the formula:
{ =IF (B2=0, 0, b2b1) } works whether B2 is blank, 0, or a number BUT, I can't understand why
{ IF b2 = 0 "0" "{ =(b2b1) }" } will not work for b2=0. The latter formula continues to give me the result of B2B1.
Thanks,
P.S. No rush as I will be gone this weekend. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20020830, 03:50 #2
 Join Date
 Dec 2000
 Location
 Quakertown, PA, Pennsylvania, USA
 Posts
 517
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF field Formula (Word 2000/SR1)
I am only getting in this post because I am sick of unanswered mail merge conditional fields. I swear to GAWD there will be a definitive guide somewhere if I have to do it myself! Here's hoping someone gives us some fantastic link we haven't seen before. [img]/forums/images/smilies/smile.gif[/img]
Ping!

20020830, 04:10 #3
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: IF field Formula (Word 2000/SR1)
Phil, cast your mind back... back... to If statements in field codes. That thread resulted in a test for a blank cell, which used the COUNT function. Incorporating that insight, here's a new field for you to try:
{ if { =COUNT(b2) } = 0 "" "{ if { =b2 } = 0 "0" "{ =b2b1 }" }" }
Or, in human language:
<UL><LI>If cell b2 is not a valid number, the result of this field is blank
*
<LI>If cell b2 is NOT blank, then:
<UL><LI>If cell b2 = 0 then this field = 0<LI>If cell b2 is not zero, then this field = b2b1[/list][/list]Right?

20020903, 08:09 #4
 Join Date
 Dec 2000
 Location
 Los Angeles Area, California, USA
 Posts
 7,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF field Formula (Word 2000/SR1)
Hi Jefferson:
Yes, your field also works. I do recall our prior thread (after a nudge) <img src=/S/smile.gif border=0 alt=smile width=15 height=15> & I remember I was going to look into the fields more (but I didn't). <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I keep thinking that my field should work. I can get my other field to work & your field to work; I'm having trouble understanding why the
{ IF b2 = 0 "0" "{ =(b2b1) }" } will not work for b2=0 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. Is it a bug, or is my logic just off?
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20020904, 04:30 #5
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: IF field Formula (Word 2000/SR1)
Phil, can you get ANY value to work? I can't seem to get b2= n to work properly for any value of n. (I tested using "TRUE" and "FALSE" as the true and false results, for simplicity.)
Well, this is speculation, but I think Word is reading b2 to mean "the string consisting of b2" rather than a table cell reference. Try these, for example:
<UL>{ if b3 = b3 "TRUE" "FALSE" } True, regardless of cell contents
{ if b3 = "b3" "TRUE" "FALSE" } True, regardless of cell contents
{ if b3 = b4 "TRUE" "FALSE" } False, regardless of cell contents
{ if b3 = "b4" "TRUE" "FALSE" } False, regardless of cell contents[/list]Why a plain old b2 works in some contexts but doesn't seem to work in others, I have no idea.

20020906, 06:21 #6
 Join Date
 Dec 2000
 Location
 Los Angeles Area, California, USA
 Posts
 7,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF field Formula (Word 2000/SR1)
<img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Jefferson,
That's it!!! I don't know why I couldn't see it. Unless B2 is in a formula field, Word doesn't recognize it as a table cell. I just tried the IF field:
{ IF { = B2 } = 0 "0" { = (b2b1) } }
which works every time. I feel much better. I'll NEVER forget this again!! <!t>[If I say it loud enough, I'll be too embarassed to forget.]<!/t> Of course, if (when) I do, I'll blame it on Alzheimer's. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

20020906, 10:08 #7
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: IF field Formula (Word 2000/SR1)
That is a lot more intuitive than Word's IF field syntax. Where are you getting the list of valid expressions to use inside the {=} field?

20020906, 11:29 #8
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,054
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: IF field Formula (Word 2000/SR1)
<P ID="edit" class=small>(Edited by macropod on 06Sep02 21:29. Correction to second example & expansion on last)</P>Hi Phil,
In most cases you don't really need all those braces. For a field that only evaluates b2b1 if b2 is empty or has any numeric value other than 0,
{=if(b2=0,0,b2b1)} or {=if(b2,b2b1,0)}
works just as well.
For a field that evaluates b2b1 if b2 has any numeric value including 0, you could use:
{=if(COUNT(b2)=0,0,b2b1)} *** edited to correct cell references
For an Excel user like me, these formulae are easier to build and their syntax is much easier to follow.
In all of the above, if you want to hide '0' results, you can put a numeric switch like # #;#; before the final brace  the final ';' tells Word not to display 0 results.
Where things seem to come unstuck using an Excel syntax in Word is if you want to have a nul result if b2 is empty or has any numeric value other than 0. In Excel you'd use:
=if(b2=0,"",b2b1)
but, in Word:
{=if(b2=0,"",b2b1)}
produces a syntax error. You then need to resort to constructs like:
{if{=COUNT(b2)} <> 0 "{=(b2b1)}" ""} or {if{=COUNT(b2)} = 0 "" "{=(b2b1)}"} *** extra example added
Hope this is something useful to add to your repetoire.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20020906, 11:47 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,054
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: IF field Formula (Word 2000/SR1)
Hi Jefferson,
The Word 2K Help file lists these:
Function Returns
ABS(x) The positive value of a number or formula, regardless of its actual positive or negative value.
AND( x,y) The value 1 if the logical expressions x and y are both true, or the value 0 (zero) if either expression is false.
AVERAGE( ) The average of a list of values.
COUNT( ) The number of items in a list.
DEFINED(x) The value 1 (true) if the expression x is valid, or the value 0 (false) if the expression cannot be computed.
FALSE 0 (zero).
INT(x) The numbers to the left of the decimal place in the value or formula x.
MIN( ) The smallest value in a list.
MAX( ) The largest value in a list.
MOD(x,y) The remainder that results from dividing the value x by the value y a whole number of times.
NOT(x) The value 0 (zero) (false) if the logical expression x is true, or the value 1 (true) if the expression is false.
OR(x,y) The value 1 (true) if either or both logical expressions x and y are true, or the value 0 (zero) (false) if both expressions are false.
PRODUCT( ) The result of multiplying a list of values. For example, the function { = PRODUCT (1,3,7,9) } returns the value 189.
ROUND(x,y) The value of x rounded to the specified number of decimal places y; x can be either a number or the result of a formula.
SIGN(x) The value 1 if x is a positive value, or the valueCheers,
Paul Edstein
[MS MVP  Word]

20020906, 18:08 #10
 Join Date
 Dec 2000
 Location
 Los Angeles Area, California, USA
 Posts
 7,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF field Formula (Word 2000/SR1)
Hi macropod:
Thanks for the response. And I agree, there is often a lack of consistency among Office applications.
Cheers,