Using "IF/Then" in tables.

[frank]

How would I write the following spreadsheet function so
that it would work in a MS Word table:
=IF(A1<>"",A1*B1,"")
It seems that Word tables have different syntax
Thanks
Frank

 

[Charles Kenyon]

I think this will work but haven't tested it.

{ IF { REF "A1" } <> "" "{ = { REF "A1" } * { REF "B1" } }" "" }

Insert the field braces using Ctrl-F9. Don't type them using the braces on
the keyboard; they don't work. You can do this by typing the text for the
fields and then select each field (working from the inside to the outside)
and press Ctrl-F9. Or, you can press Ctrl-F9 as you get to the place where
you want each field and use your arrow keys or mouse to leave the field and
continue typing. When you have finished the entire field, move the insertion
point (cursor) into the field and press the F9 key.

If that doesn't work, try it without the quotation marks around the cell
references. The REF fields may not be required but I have found I seem to
get better results using them rather than simply using bookmark names.
--

Charles Kenyon

Word New User FAQ & Web Directory:
<URL: http://addbalance.com/word/index.htm>

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

See also the MVP FAQ: <URL: http://www.mvps.org/word/> 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.

How would I write the following spreadsheet function so

--

Charles Kenyon

Word New User FAQ & Web Directory:
<URL: http://addbalance.com/word/index.htm>

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

See also the MVP FAQ: <URL: http://www.mvps.org/word/> 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.

 

[Suzanne S. Barnhill]

If the cells are in the same table where the IF field appears, I think this
will work:

{ IF A1 = "" "" { = A1*B1 } }

The only problem is that for some reason it displays 0 instead of blank when
A1 is blank. I get this even if I use

{ IF A1 = "" { = A1 } { = A1*B1 } }

I haven't found any combination that doesn't result in 0 when A1 is blank.

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

 

[Frank]

Thank you for your response
However Charles sugestion produces a syntax error with or
without quotations. And Susan's sugestion produces a zero
which is what I wanted to avoid.

Any suggestions?
What I sould like to do is write up a table where if
cell A1 is blank cell C1 will also be blank. If there
there is a number in cell A1 it will multiply with the
number is cell B1 and the total is in C1.
Can anyone help?
Thanks
Frank

 

[Greg Maxey]

Frank,

This one is tricky but the following will work placed in c1:

{ IF { =IF(a1=0,1,2)} = "1" """{=(a1*b1)}}

 

[Frank]

Greg,
Thanks so much!
It work great!!!
Yea

-----Original Message-----
Frank,

This one is tricky but the following will work placed in c1:

{ IF { =IF(a1=0,1,2)} = "1" """{=(a1*b1)}}

--
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
To e-mail, edit out the "w...spam" in (e-mail address removed)



.

 

[Greg Maxey]

Frank,

There is one glitch that I noticed after posting. I am hoping that it will
not have an adverse effect on the application that you intend for your
formula. If the value in a1 is actually "0" then the result in in c1 will
not be the expected by 0*2 or 0 but blank instead. I could find not any way
to avoid this.

 

[Cindy M -WordMVP-]

Hi Greg,

This one is tricky but the following will work placed in c1:

{ IF { =IF(a1=0,1,2)} = "1" """{=(a1*b1)}}

More correct would be to use a number formatting switch, more
like this:

{ IF A1 = "" { = A1 \# "0.00,-0.00,''" } { = A1*B1 \#
"0.00,-0.00,''" } }

As in Excel, the first part is for positive numbers, the
second for negative numbers and the third the display for zero
values. In this case, the latter is two apostrophes ( ' ) to
signify a zero-length string.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Greg Maxey]

Cindy,

Your use of commas in place of semicolons in the number
switch formats had me climbing the walls

Your method produces the same result as mine (except yours
has a number format). The original poster wants c1 to be
blank if a1 is blank otherwise c1 should return the
product a1*b1. My method and yours fails to do this if a
zero "0" is typed in a1. The result in c1 should be 0*b1
or 0 and with your switches 0.00.

I can't find a way to make c1 return 0 if a1 holds 0 but
c1 be blank if a1 is blank

 

[Cindy M -WordMVP-]

Hi Greg,

Your use of commas in place of semicolons in the number
switch formats had me climbing the walls

Well, it all depends on what settings you have in Windows
Word takes its list separator from Windows, and all the
examples in Help use commas. Since you're US-based, I'm
surprised you're using semi-colons.

Your method produces the same result as mine (except yours
has a number format). The original poster wants c1 to be
blank if a1 is blank otherwise c1 should return the
product a1*b1. My method and yours fails to do this if a
zero "0" is typed in a1. The result in c1 should be 0*b1
or 0 and with your switches 0.00.

That shouldn't be happening if the numeric switch is
configured correctly. When I set up a test, I see "nothing"
instead of zero when a1 contains nothing (or 0):

{ = a1 * b1 \# "0.00;-0.00;''" }

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Frank]

For what I am doing, namely submitting invoices to
customers for services performed,a 0 is not necessary.
I tweaked Greg's syntax a little to add the dollar sign
in the the last column:

{IF{=IF(C7=0,1,2)}="1" """{=(C7*D7)\# "$#,##0.00;
($#,##0.00)"}}
I think this is right. Any way it works for me.
Since I have used Word Perfect since dos and know nothing
anout word or visual basic, Im just happy it works for me!
Thanks. For this had been a source of great frustration.
I have one last question, I will post new.
Thanks again Frank

 

[Greg Maxey]

Cindy,

As Frank has already indicated a blank in cell c1 is acceptable when the
enter in a1 is 0. I was trying to produce something like this (make not
come out aligned)"

a1 b1 c1
2 5 10
5
0 5 0

or c1 would always return the product a1*b1 if a value (even zero) were
assigned to a1.

 

[Cindy M -WordMVP-]

Hi Greg,

I think you could only do something like this by assigning
bookmarks to each cell's contents. As soon as you use the =
sign Word will do its best to force everything to a number.
And a zero-length string is always converted to 0.
Bookmarked content can be forced to literal text
interpretation by placing it in "quotes".

I was trying to produce something like this (make not
come out aligned)"

a1 b1 c1
2 5 10
5
0 5 0

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Greg Maxey]

Cindy,

Thanks for confirming my observations. It is good to know
that I wasn't just missing something simple.