 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / February 2006MS word conditional formula
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?? 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 } } >I am trying to write a formula that will: > a) add two fields (E2 and G2) together and then either: [quoted text clipped - 13 lines] > > Any ideas?? 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? > Word has nothing like the SUM() capabilities of Excel. Nor are cell > references so simple. [quoted text clipped - 20 lines] > > > > Any ideas?? Works for me. How are you creating the nested fields? > Thanks for the response! Your solution did not work, but it also didn't > give [quoted text clipped - 28 lines] >> > >> > Any ideas?? 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 > Works for me. How are you creating the nested fields? > [quoted text clipped - 30 lines] > >> > > >> > Any ideas?? 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. >I just have an empty cell (cell I2) and from the top menu in word, i select > [quoted text clipped - 47 lines] >> >> > >> >> > Any ideas?? 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. > 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 [quoted text clipped - 62 lines] > >> >> > > >> >> > Any ideas?? 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. > 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. [quoted text clipped - 89 lines] >> >> >> > >> >> >> > Any ideas?? 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. > Position the cursor where you want the field (within the other field) and > press Ctrl-F9. [quoted text clipped - 94 lines] > >> >> >> > > >> >> >> > Any ideas?? 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 >I am trying to write a formula that will: > a) add two fields (E2 and G2) together and then either: [quoted text clipped - 13 lines] > > Any ideas?? 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? > Try > [quoted text clipped - 24 lines] > > > > Any ideas?? 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 > Thanks for the response! Your solutions did not work, but they also > didn't [quoted text clipped - 31 lines] >> > >> > Any ideas?? 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? > 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 [quoted text clipped - 51 lines] > >> > > >> > Any ideas?? > 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 > OK...i now have the following: > [quoted text clipped - 71 lines] >> >> > >> >> > Any ideas?? YES!!! it works! that is so cool. thank you so much!! > > 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 [quoted text clipped - 94 lines] > >> >> > > >> >> > Any ideas?? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.