 |
 | 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 / Mailmerge and Fax / March 2006Calculations in a mergefield.
I have a mergefield called AnnualPremium. How can i calculate the MonthlyPremium from this. I've tried { =AnnualPremium/12 } but that doesn't work. I haven't found any examples of how to do this on the web but i'm sure there's an easy solution for how to do it. Thanks in advance, Daryl You are right. That won't work :) You need to combine your mergefield with a formula field thus {=({Mergefield AnnualPremium} / 12) \# "$,0.00"} Insert the {} bracket pairs with CTRL+F9 and type the rest. Then F9 and ALT+F9 to update and if necessary toggle the display. There are some examples at http://www.gmayor.com/formatting_word_fields.htm  Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > I have a mergefield called AnnualPremium. How can i calculate the > MonthlyPremium from this. I've tried { =AnnualPremium/12 } but that [quoted text clipped - 3 lines] > > Daryl Excellent thanks. I didn't have the mergefield embedded in another F9. Actually, now that i've tried it, it's not working. Here's the text below. I'm trying to divide a field by 5000 { = ({MERGEFIELD CoverageLevel \# "#" \* MERGEFORMAT } \ 5000 } and it says Syntax error. I've tried it formatting as dollars, and without formatting, but it's either coming through as Syntax Error or as 5000. Any thoughts? Thanks in advance. Daryl sorry was missing a comma in the above merge field. What i'm seeing after some tinkering is this. I'll enter a formula, it will evaluate correctly once, and thereafter when i try and view it it will show up as Syntax Error!<<. When i try and update, it says it cannot update one of the fields on the screen, but i can't for the life of me see why it won't update it. Here's another look at what i've tried: The following statement below works. But if i try and embed that in another CTRL-F9, it says it cannot update it: { IF { {MERGEFIELD CoverageLevel} <> "" "{MERGEFIELD CI_AR_CoverageLevel}" "0" } This is the statement with the added formula condition. Word says it cannot update the field. { = { IF { {MERGEFIELD CoverageLevel} <> "" "{MERGEFIELD CI_AR_CoverageLevel}" "0" } * 2 } { = { IF { {MERGEFIELD CoverageLevel} <> "" "{MERGEFIELD CI_AR_CoverageLevel}" "0" } * 2 \# "##" } I've tried it with and without calculations, i'm stumped. Thanks in advance for any suggestions. What is it about adding the Formula part that it fails to update? Use { IF { {MERGEFIELD CoverageLevel} <> "" { = ({MERGEFIELD CI_AR_CoverageLevel}* 2) \# "##" } 0 } SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP > sorry was missing a comma in the above merge field. What i'm seeing > after some tinkering is this. I'll enter a formula, it will evaluate [quoted text clipped - 19 lines] > advance for any suggestions. What is it about adding the Formula part > that it fails to update? What you posted { = ({MERGEFIELD CoverageLevel \# "#" \* MERGEFORMAT } \ 5000 } is nothing like the example that Graham provided, which was {=({Mergefield AnnualPremium} / 12) \# "$,0.00"} What you need is: { = ({MERGEFIELD CoverageLevel } / 5000) \# $,0.00"} SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP > Actually, now that i've tried it, it's not working. Here's the text > below. I'm trying to divide a field by 5000 [quoted text clipped - 6 lines] > > Daryl Sorry for the confusion. I was posting about this mergefield i was currently trying to get to work. The annual premium field i was able to get the input source to give me both Monthly and Annually premiums so that was how i was able to avoid that one. However, i did the following to insert the above formula and i still get a Syntax error when i CTRL-A and hit F9 to update. Here is the verbatim text from the mergefield, with field codes on. I have no idea why this is giving me such trouble. I use nested IFs, compares, etc. with no issue, but cannot get even the simplest Calculation to work. hit CTRL F9 {} insert a mergefield {{MERGEFIELD CoverageLevel}} add the = and Parenthesis { = ({MERGEFIELD CoverageLevel} ) } add the calculation { = ({MERGEFIELD CoverageLevel} / 5000) } add the formatting to the end ( i use this formatting all over, so it can't be this) { = ({MERGEFIELD CoverageLevel} / 5000) \# "$,0.00" } I believe that's the same as above, but it still gives me errors. There has to be something in the order that i'm inserting the field/calculation/braces that word doesn't like. I tried Graham's statement as well, but i get a syntax error with that one too. Thanks again, Daryl One additional note, i was able to get the formula from Graham's reply: { IF { MERGEFIELD CoverageLevel } = "" "" { = { ( {MERGEFIELD CoverageLevel} / 5000) \# "$,0.00" } 0} to update without getting a Syntax Error, but it Always evaluates to 0 no matter what the CoverageLevel is ( i have that displayed in another field so i know it's nonzero). I imagine it's because of the 0 appended to the end, but i don't know what that 0 is supposed to signify. Thanks in advance, Daryl Once again, what you have posted is nothing like what Graham had suggested. You posted: { IF { MERGEFIELD CoverageLevel } = "" "" { = { ( {MERGEFIELD CoverageLevel} / 5000) \# "$,0.00" } 0} It should be: { IF { MERGEFIELD CoverageLevel } = "" "" { = ( {MERGEFIELD CoverageLevel} / 5000) \# "$,0.00" } But, what does { MERGEFIELD CoverageLevel } if it is not empty. SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP > One additional note, i was able to get the formula from Graham's reply: > [quoted text clipped - 10 lines] > > Daryl Let's take a step back and start this again You want to divide the number produced by the coveragelevel field by 5000 if the coverage level field contains a number, or if it is empty enter a 0? Then what you need is: { IF{ MERGEFIELD CoverageLevel } = "" "0" "{ =({ MERGEFIELD CoverageLevel } / 5000) \# "$,0.00"}" } Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > One additional note, i was able to get the formula from Graham's > reply: [quoted text clipped - 11 lines] > > Daryl What is returned by {MERGEFIELD CoverageLevel}? Maybe it is not numeric and hence cannot have an arithmetic operation performed on it. SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP > Sorry for the confusion. I was posting about this mergefield i was > currently trying to get to work. The annual premium field i was able [quoted text clipped - 32 lines] > > Daryl Ok, it sort of works. In the .dot template, when i try and CTRL-A and hit F9, it says 'Syntax Error <<', but when i load that document with an actual merge source, the correct value is in there, or it's blank if there's CoverageLevel is "". I don't understand it, but it's better than it was. Am i just misunderstanding how merge documents work, and that all formulas with merge fields in them will evaluate to a Syntax Error when you're designing the form and try to update, but they will work correctly with an input merge source? I've been focusing on inserting the fields above and trying to update them immediately (without a merge source). For grins i decided to just run the merge, and lo and behold the formula worked. Thanks a lot for the help! hopefully you can give me some insight as to why a formula that has a Syntax error can work correctly :) Maybe it will educate others than just me too heh. Daryl Well, that was it. Now all my formulas are working great once there's a merge source behind them. All those times i tried the formulas above work fine once i do the actual merge. Sorry to take up so much of your time with this guys, i really do appreciate it. Daryl Use { = { MERGEFIELD AnnualPremium } \ 12 } Each pair of field delimiters must be inserted by using Ctrl+F9. To make sure you get the { MERGEFIELD AnnualPremium } correct, you can insert the field from the Insert Merge Fields pull down which will give you <<AnnualPremium>> Then when you press Alt+F9 to toggle the display of field codes, you will see { MERGEFIELD AnnualPremium } Select that and then press Ctrl+F9 and you will then get { { MERGEFIELD AnnualPremium } } which you can then edit to give the construction first shown above. You may also need to add a formatting switch. { = { MERGEFIELD AnnualPremium } \ 12 \# "$#,###.00" } For more on formatting fields, See "Formatting Word fields with switches" on fellow MVP Graham Mayor's website at http://www.gmayor.com/formatting_word_fields.htm SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP >I have a mergefield called AnnualPremium. How can i calculate the > MonthlyPremium from this. I've tried { =AnnualPremium/12 } but that [quoted text clipped - 3 lines] > > Daryl |
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.