Using IF logical formula to determine formatting

Topic

New Reply

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.

Reply | Quote

Replies

I believe you have the = in the wrong place.

Try {if {= a1 } >1 {=d2/c2 # “0.000”} {=d2/c2 # “0.00”} }

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Too complicated! Try:
{=d2/c2 # “0.00{=A1>1 # “‘0’;;”}”}

The master speaks! Awesome!

Reply | Quote

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

Reply | Quote

:clapping: Wow! That is a very slick example of field code logic Macropod; definitely one for my ‘useful tricks’ file…

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.

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the explanation.

Reply | Quote