Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using IF logical formula to determine formatting

    In a word formula, I want to be able to format the answer to 2 decimal places if a precondition is 1, and to 3 decimal places if the precondition is greater than one.

    Here is what I tried-

    {=if( a1 >1,d2/c2 \# "0.000",d2/c2 \# "0.00") }

    I get the message-

    Error! Too many picture switches defined.

    So the equation is the same for both answers, I just want to define the number of decimals shown.

    I'm guessing it must be something simple, but just can't figure out how to do it.

    Any help is greatly appreciated.

  2. #2
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    I believe you have the = in the wrong place.

    Try {if {= a1 } >1 {=d2/c2 \# "0.000"} {=d2/c2 \# "0.00"} }
    Charles Kyle Kenyon
    Madison, Wisconsin

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Charles Kenyon View Post
    Try {if {= a1 } >1 {=d2/c2 \# "0.000"} {=d2/c2 \# "0.00"} }
    Too complicated! Try:
    {=d2/c2 \# "0.00{=A1>1 \# "'0';;"}"}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. The Following User Says Thank You to macropod For This Useful Post:

    madkiwi (2014-09-25)

  5. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Quote Originally Posted by macropod View Post
    Too complicated! Try:
    {=d2/c2 \# "0.00{=A1>1 \# "'0';;"}"}
    The master speaks! Awesome!
    Charles Kyle Kenyon
    Madison, Wisconsin

  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It might be too complicated, but I can follow what it does. I tried yours and it works, but I still don't understand how it does.

    But thank you, work it does!

    Mark

  7. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madkiwi View Post
    I tried yours and it works, but I still don't understand how it does.
    Basically, it conditionally adds a 0 to the formatting switch. I expect you'll recognize:
    {=d2/c2 \# "0.00"}
    and:
    {=A1>1 \# "'0';;"}
    on their own. The second one is just a TRUE/FALSE test using a formula (A>1) instead of an IF test. That formula would ordinarily output TRUE or FALSE, but the numeric picture switch causes it to output 1 for TRUE and 0 for FALSE. In this case, however, the numeric picture switch outputs a literal 0 (notice the '0') for the TRUE result instead of 1 and supresses the logical 0 of the FALSE result (via the ;; ). So, when used in {=d2/c2 \# "0.00{=A1>1 \# "'0';;"}"}, an extra 0 gets added to the formatting switch when A>1.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. The Following User Says Thank You to macropod For This Useful Post:

    EricFletcher (2014-09-25)

  9. #7
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Thanks for the explanation.
    Charles Kyle Kenyon
    Madison, Wisconsin

  10. #8
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I do see how you did it now, but I could not have come up with that on my own. I never would have been able to wrap my mind around that.

    I'm so glad I chose accounting instead of programming...

    Mark

  11. #9
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Canada
    Posts
    122
    Thanks
    3
    Thanked 20 Times in 18 Posts
    Wow! That is a very slick example of field code logic Macropod; definitely one for my 'useful tricks' file...

    Quote Originally Posted by madkiwi View Post
    I'm so glad I chose accounting instead of programming...
    Despite lots of supposedly clear examples, I've never been able to wrap my head around double entry accounting! This thread is a great example of why forums like this are so useful.

Posting Permissions

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