A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Word 2003 - IF Statement, Form Field Calculate



 
 
Thread Tools Display Modes
  #1  
Old August 25th, 2009, 10:19 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 26th, 2009, 01:47 AM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default 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  
Old August 26th, 2009, 05:22 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 26th, 2009, 05:31 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 26th, 2009, 09:20 PM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default 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  
Old August 26th, 2009, 10:01 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 26th, 2009, 10:33 PM posted to microsoft.public.word.tables
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default 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  
Old August 27th, 2009, 07:54 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 27th, 2009, 08:03 PM posted to microsoft.public.word.tables
Stacy Birk
external usenet poster
 
Posts: 9
Default 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  
Old August 27th, 2009, 11:28 PM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.