MS word conditional formula

[Kevin]

I am trying to write a formula that will:
a) add two fields (E2 and G2) together and then either:
b) display "" (empty..nothing) if the total value is 0 (zero).
or
c) display the total value if the value is not equal to 0

So I've tried this:

=IF{SUM(E2,G2)=0} "" "=SUM(E2,G2)"

but I get a syntax error.

I'm thinking that something like this should be very simple, but I'm having
a hard time with it.

Any ideas??

 

[Jezebel]

Word has nothing like the SUM() capabilities of Excel. Nor are cell
references so simple.

1. Use bookmarks to name your two cells (eg "cell_1", and "cell_2")

2. Use { IF { = cell_1 + cell_2 } = 0 "" { = cell_1 + cell_2 } }

 

[Peter Jamieson]

Try

{ IF { =SUM(E2,G2) } = 0 "" "{ =SUM(E2,G2) }" }

or if you know the output formats you want, you might be able to use a
format switch, e.g.
{ =SUM(E2,G2) \#"#.00;-#00;" }

(the formats in this case are for positive;negative;zero results).

Peter Jamieson

 

[Kevin]

Thanks for the response! Your solutions did not work, but they also didn't
give me a syntax error like I had been getting from previous efforts. The
formula doesn't display the result, no matter what is entered.

Can you think of any reason why?

 

[Kevin]

Thanks for the response! Your solution did not work, but it also didn't give
me a syntax error like I had been getting from previous efforts. The formula
doesn't display the result, no matter what is entered.

Can you think of any reason why?

 

[Jezebel]

Works for me. How are you creating the nested fields?

 

[Kevin]

I just have an empty cell (cell I2) and from the top menu in word, i select

table > formula...

then i pasted the formula that you so graciously supplied into the Formula
box.

the cells are actually bookmarked as the exact name of the cell. so the
bookmark for E2 is E2 and the bookmark for G2 is G2.

is any of that rationale or process incorrect?

thanks,

kevin

 

[Peter Jamieson]

Works OK here as long as the cell containing the formula is in the same
table as the cells containing the values. If the formula is outside the
table, you need to put a bookmark in the table, or bookmark the table, (e.g.
as mytable1) and use

{ IF { =SUM(mytable1 E2,mytable1 G2) } = 0 "" "{ =SUM(mytable1 E2,mytable1
G2) }" }

Things to look at:
a. are you using the special field code braces that you can enter using F9
for every pair of {} ? You can't just use the characters from the keyboard,
so you can't just copy/paste nested fields from messages either.
b. when you use the above syntax, Word is working from table cell
references, not bookmark names. If you create bookmark names such as E2 and
F2 you will only confuse things.
c. what is the result of { =SUM(E2,G2) } ?
d. Is there anything except numeric information in E2 or G2?

Peter Jamieson

 

[Jezebel]

Pasting the formula won't work. The curly brackets that surround fields have
to be inserted using Insert > Field or CTRL-F9. They are not the same as the
curly brackets on the keyboard (which is what you've ended up with by
copying and pasting).

So the formula I suggested is actually three fields - an outer one, and two
inner ones. Try creating the inner ones first, so you understand what's
going on:

{ = E2 + G2 }

Press F9 to update the field and make sure that the displayed result is in
fact the sum of the two cells. (Use Alt-F9 to switch between displaying
field codes and field results.

 

[Kevin]

ok....E2 + G2 now works! and i can see that i am not allowed to enter the
curly brackets...they must be added by word itself.

so that takes care of the first inner...

now how about the next inner? when i go back to insert > field in the same
cell, it doesn't want to allow me to insert another field into the same cell.
it keeps going to the field that i have already created.

alt-F9 is a pretty cool trick also....i wish that i could simply copy and
paste the bold curly brackets...it seems like that would be easier...i can't
seem to understand how to add more than one field into a single cell.

let me know what i should do next.

 

[Jezebel]

Position the cursor where you want the field (within the other field) and
press Ctrl-F9.

You can copy and paste fields, just not keyboard curly brackets.

 

[Kevin]

i'd like to send you a sample word doc of what i am doing so that you can
look at it. is that possible?

the formula is in place, but the 0 is still showing if nothing is entered
into the two fields i need for nothing to show.....a zero (0) is too much.
the field needs to be blank.

 

[Kevin]

OK...i now have the following:

{ IF { field_B1+field_D1 } = 0 "" {=field_B1+field_D1}}

where each of the curly brackets is the sort that word inserts rather than
the variety that can be typed on the keyboard.

the addition of the two fields works just fine (they are number values
within the same table) but i want the 0 (zero) to go away so that the result
field (where the formula is) is empty.

does the 0 go away for you?

 

[Peter Jamieson]

the addition of the two fields works just fine (they are number values

within the same table) but i want the 0 (zero) to go away so that the
result
field (where the formula is) is empty.

At the moment I'm not sure I follow, but am assuming that you mean that the
result of the {=} field is always displayed, even when it is 0.

But is the result actually 0, or is it something like 0.0? i.e., what is the
result of {=field_B1+field_D1 ) (or whatever you actually have), on its own?

Also, now you've played around with fields a bit more, does my other
suggestion along the lines of

{ =SUM(E2,G2) \#"#.00;-#00;" }

do the trick?

NB, it's sometimes helpful if you can show the field codes /exactly// as
they are in your document.

Peter Jamieson

 

[Kevin]

YES!!! it works! that is so cool. thank you so much!!