Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Greetings,
I have a table in Word 2003 and would like to do an “IF” statement on a Text Form Field, type - Calculation. The problem is: If CellA multiplied by CellB is $150 or less, the answer is $150; otherwise the answer is the result of CellA multiplied by CellB. The expression would be this in Excel 2003: =IF(SUM(D19*D21) 150,150,SUM(D19*D21)) The expression would be this in Word 2003: *stumped* I do not have the luxury of keeping Excel 2003 in the picture for this document. I have done Google searches for “IF” statements, formulas, calculations, etc. And all “IF” calculations for Word 2003 lead me to: http://groups.google.com/group/micro...a67d4b94ada58f AND http://www.wopr.com/cgi-bin/w3t/show...?Number=365442 Anyone looking for a great tutorial should download MVP Macropods. This person has amazing talent, simply an incredible tutorial . The thoroughness of the project is remarkable. Even though I found great resources and fantastic advice, I still come up short on a valid Form Field Expression for this problem. I have an example Word.doc if that would help, I do not mind emailing it if that would be more helpful. Any help solving this dilemma is greatly appreciated. Stacy |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Hi Stacy ,
In Excel, you could do also use: =MAX(D19*D21,150) In Word, you could use a formula field (rather than a calculation formfield) coded as: {=MAX({=D19*D21},150)} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in message ... Greetings, I have a table in Word 2003 and would like to do an “IF” statement on a Text Form Field, type - Calculation. The problem is: If CellA multiplied by CellB is $150 or less, the answer is $150; otherwise the answer is the result of CellA multiplied by CellB. The expression would be this in Excel 2003: =IF(SUM(D19*D21) 150,150,SUM(D19*D21)) The expression would be this in Word 2003: *stumped* I do not have the luxury of keeping Excel 2003 in the picture for this document. I have done Google searches for “IF” statements, formulas, calculations, etc. And all “IF” calculations for Word 2003 lead me to: http://groups.google.com/group/micro...a67d4b94ada58f AND http://www.wopr.com/cgi-bin/w3t/show...?Number=365442 Anyone looking for a great tutorial should download MVP Macropods. This person has amazing talent, simply an incredible tutorial . The thoroughness of the project is remarkable. Even though I found great resources and fantastic advice, I still come up short on a valid Form Field Expression for this problem. I have an example Word.doc if that would help, I do not mind emailing it if that would be more helpful. Any help solving this dilemma is greatly appreciated. Stacy |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Thank You, macropod!
And especially for the 'note' part. I have done the following: Opened Document Unprotected Document Clicked Table, Insert Formula In Formula Field typed "=MAX({=D19*D21},150)" (without the quotation marks) In the Format Field, selected "$#,##0.00;($#,##0.00)" (without the quotation marks) Clicked Ok Received: "!Syntax Error, {" (without the quotation marks) I gave elementary steps I know, please know I am not questioning your intelligence but more so questioning my own with this process. Would you mind correcting me on where I went wrong? I greatly appreciate your help, macropod. Stacy On Aug 25, 7:47*pm, "macropod" wrote: Hi Stacy , In Excel, you could do also use: =MAX(D19*D21,150) In Word, you could use a formula field (rather than a calculation formfield) coded as: {=MAX({=D19*D21},150)} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in ... Greetings, I have a table in Word 2003 and would like to do an “IF” statement on a Text Form Field, type - Calculation. The problem is: *If CellA multiplied by CellB is $150 or less, the answer is $150; otherwise the answer is the result of CellA multiplied by CellB. The expression would be this in Excel 2003: *=IF(SUM(D19*D21) 150,150,SUM(D19*D21)) The expression would be this in Word 2003: **stumped* I do not have the luxury of keeping Excel 2003 in the picture for this document. I have done Google searches for “IF” statements, formulas, calculations, etc. *And all “IF” calculations for Word 2003 lead me to: http://groups.google.com/group/micro...bles/browse_th... AND http://www.wopr.com/cgi-bin/w3t/show...?Number=365442 Anyone looking for a great tutorial should download MVP Macropods. This person has amazing talent, simply an incredible tutorial . *The thoroughness of the project is remarkable. Even though I found great resources and fantastic advice, I still come up short on a valid Form Field Expression for this problem. I have an example Word.doc if that would help, I do not mind emailing it if that would be more helpful. *Any help solving this dilemma is greatly appreciated. Stacy |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
I should have specified that I did try to do this as well:
Go to the cell within the table. CTRL-F9=max(CTRL F9=D19*D21 ,150) And that did not work either. I am sorry for the double post, I should have stated that as well. |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Hi Stacy,
Where you want the calculation to go, press Ctrl-F9 twice, so that you get a nested pair of field braces, thus '{ { } }'. Then fillin/around them so that you end up with '{=MAX({=D19*D21},150)}' -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in message ... I should have specified that I did try to do this as well: Go to the cell within the table. CTRL-F9=max(CTRL F9=D19*D21 ,150) And that did not work either. I am sorry for the double post, I should have stated that as well. |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Thank you for sticking with me, macropod.
I did as you suggested and wrote (CTRL-F9 Twice for { } ): {=MAX ({=D19*D21},150)} I received this: !Syntax Error, $ When I right clicked for field code, this appears: {=MAX ($374,696,150)} When I ALT-F9 I see the code that I typed: {=MAX({=D19*D21},150)} I double checked the table cell references, those are correct. Your thoughts? |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Try
{=MAX({=D19*D21 \# 0 },150)} In tests here, that got rid of the $ before the numbers being compared and overcame the !Syntax Error, $ -- Hope 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, originally posted via msnews.microsoft.com "Stacy Birk" wrote in message ... Thank you for sticking with me, macropod. I did as you suggested and wrote (CTRL-F9 Twice for { } ): {=MAX ({=D19*D21},150)} I received this: !Syntax Error, $ When I right clicked for field code, this appears: {=MAX ($374,696,150)} When I ALT-F9 I see the code that I typed: {=MAX({=D19*D21},150)} I double checked the table cell references, those are correct. Your thoughts? |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Thank You, Doug! Both you and macropod are amazing!
From what I can tell the ' \ # 0' is a formatting switch of some sort? Also, side question - on various forums around here I notice the formula written sometimes has spaces, sometimes does not - do spaces matter when writing a formula such as you all did above? Just curious on that. Thank you again you two - I am extremely grateful to the assistance. Stacy |
#9
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Thank you both so very much for your help and time. You both are
fantastic. Doug, this works. The ' \# 0 ' is for formatting right? I am trying to understand the logic so I know for next time. And having said that the field would not have a dollar sign and should. Am I S.O.L.? or do I have options for including a dollar sign? Stacy |
#10
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Hi Stacy,
Yes, the '\# 0' is a "numeric picture switch" (in Word parlance). There's more on field switches in Word's help file. To see how manage field formatting and perform a wide range of calculations in Word, check out my Word Field Maths Tutorial, at: http://www.wopr.com/index.php?showtopic=365442 or http://www.gmayor.com/downloads.htm#Third_party -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in message ... Thank You, Doug! Both you and macropod are amazing! From what I can tell the ' \ # 0' is a formatting switch of some sort? Also, side question - on various forums around here I notice the formula written sometimes has spaces, sometimes does not - do spaces matter when writing a formula such as you all did above? Just curious on that. Thank you again you two - I am extremely grateful to the assistance. Stacy |
#11
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
You can have a dollar sign \# "$,0" or for cents too \# "$,0.00"
All this and more is covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Stacy Birk wrote: Thank you both so very much for your help and time. You both are fantastic. Doug, this works. The ' \# 0 ' is for formatting right? I am trying to understand the logic so I know for next time. And having said that the field would not have a dollar sign and should. Am I S.O.L.? or do I have options for including a dollar sign? Stacy |
#12
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Thank you both for your reply.
macropod - I have your 'math zip' file on my system, I have referred to it before. Thank you for providing that information. Graham Mayor - I have added your links for referrals in the future. Forgive my ignorance here but in the links nor in the math.zip did I find a point of reference when using the formula with an integer at the end, such as the '150' that is in the above formula. The following formulas I have tried and both returned the !Syntax Error, $: {=MAX({=D19*D21\# "$,0"}, 150)} {=MAX({=D19*D21\#$#,##0}, 150)} Possibly I am overlooking something here but I am not for certain what. I appreciate the help. |
#13
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Hi Stacy,
using the '\# 0' within the embedded field simply tells Word to strip off the currency formatting for the purposes of the MAX test. Since it seems you'll want the displayed result to have the currency formatting, code the field this way: {=MAX({=D19*D21 \# 0 },150) \# $,0} or {=MAX({=D19*D21 \# 0.00 },150) \# $,0.00} depending on whether any decimals might be involved. The comma after the $ sign inserts the thousands separator - delete it if you don't want one. -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in message ... Thank you both for your reply. macropod - I have your 'math zip' file on my system, I have referred to it before. Thank you for providing that information. Graham Mayor - I have added your links for referrals in the future. Forgive my ignorance here but in the links nor in the math.zip did I find a point of reference when using the formula with an integer at the end, such as the '150' that is in the above formula. The following formulas I have tried and both returned the !Syntax Error, $: {=MAX({=D19*D21\# "$,0"}, 150)} {=MAX({=D19*D21\#$#,##0}, 150)} Possibly I am overlooking something here but I am not for certain what. I appreciate the help. |
#14
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
macropod!
Thank you so very much for your help. You do not know how much it is appreciated. The final result that you offered works like a charm. Side question: When working with field formulas such as we did within this post, if spaces are not part of the end results, do spaces matter when trying to get to a result? For example: This is the formula - {=MAX({=D19*D21 \# 0 },150) \# $,0} (notice space after D21, after # and after 0) as opposed to using: {=MAX({=D19*D21\#0},150)\# $,0} . You are wonderful. Thank you again. Stacy |
#15
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Hi Stacy,
The correct use of spaces between the expressions in a formula field can be critical. For the space between an expression and a picture switch, though, I don't believe it is. Thus: {=MAX({=D19*D21 \# 0 },150) \# $,0} {=MAX({=D19*D21 \#0 },150) \#$,0} {=MAX({=D19*D21\# 0 },150)\#$,0} all give the same result, but {=MAX({=D19*D21\# 0 },150)\#$ ,0} will produce a syntax error. If you need spaces etc withing the formatted output, you need to enclose the numeric formatting within double quotes. For example: {=MAX({=D19*D21\# 0 },150) \# "$ ,0"} -- Cheers macropod [Microsoft MVP - Word] "Stacy Birk" wrote in message ... macropod! Thank you so very much for your help. You do not know how much it is appreciated. The final result that you offered works like a charm. Side question: When working with field formulas such as we did within this post, if spaces are not part of the end results, do spaces matter when trying to get to a result? For example: This is the formula - {=MAX({=D19*D21 \# 0 },150) \# $,0} (notice space after D21, after # and after 0) as opposed to using: {=MAX({=D19*D21\#0},150)\# $,0} . You are wonderful. Thank you again. Stacy |
#16
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2003 - IF Statement, Form Field Calculate
Afternoon macropod!
Your explanation is most helpful. And is greatly appreciated. Have a fantastic day, Stacy |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a User Form using an If statement field code | Mailmerge | |||
How do you add an IF statement to a user form field? | Microsoft Word Help | |||
Form field in conditional statement | Microsoft Word Help | |||
Calculate date with form field | Microsoft Word Help | |||
form field auto calculate | Microsoft Word Help |