Results 1 to 10 of 10
  1. #1
    Uranium Lounger
    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/SR-1)

    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, b2-b1) } works whether B2 is blank, 0, or a number BUT, I can't understand why

    { IF b2 = 0 "0" "{ =(b2-b1) }" } will not work for b2=0. The latter formula continues to give me the result of B2-B1.
    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>

  2. #2
    4 Star Lounger
    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/SR-1)

    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!

  3. #3
    Super Moderator jscher2000's Avatar
    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/SR-1)

    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" "{ =b2-b1 }" }" }

    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 = b2-b1[/list][/list]Right?

  4. #4
    Uranium Lounger
    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/SR-1)

    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" "{ =(b2-b1) }" } 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>

  5. #5
    Super Moderator jscher2000's Avatar
    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/SR-1)

    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.

  6. #6
    Uranium Lounger
    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/SR-1)

    <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" { = (b2-b1) } }

    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>

  7. #7
    Super Moderator jscher2000's Avatar
    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/SR-1)

    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?

  8. #8
    Super Moderator
    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/SR-1)

    <P ID="edit" class=small>(Edited by macropod on 06-Sep-02 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 b2-b1 if b2 is empty or has any numeric value other than 0,
    {=if(b2=0,0,b2-b1)} or {=if(b2,b2-b1,0)}
    works just as well.

    For a field that evaluates b2-b1 if b2 has any numeric value including 0, you could use:
    {=if(COUNT(b2)=0,0,b2-b1)} *** 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,"",b2-b1)
    but, in Word:
    {=if(b2=0,"",b2-b1)}
    produces a syntax error. You then need to resort to constructs like:
    {if{=COUNT(b2)} <> 0 "{=(b2-b1)}" ""} or {if{=COUNT(b2)} = 0 "" "{=(b2-b1)}"} *** extra example added

    Hope this is something useful to add to your repetoire.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    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/SR-1)

    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 value
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Uranium Lounger
    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/SR-1)

    Hi macropod:
    Thanks for the response. And I agree, there is often a lack of consistency among Office applications.
    Cheers,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •