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 Formula using IF



 
 
Thread Tools Display Modes
  #1  
Old October 31st, 2007, 06:50 PM posted to microsoft.public.word.tables
Thomas
external usenet poster
 
Posts: 211
Default Word Formula using IF

I have a small table with 4 or 5 fields that calculate based on the first
fields value. However, I need one field to calculate based on an IF
function.

I know how to write it in excel: =IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a formula
that can calculate 15% of the value in cell D1. If it is greater that 1,500,
place the calculated value in this field, if it is not greater than 1,500,
place 1,500 in this field.

Please Help

  #2  
Old October 31st, 2007, 08:45 PM posted to microsoft.public.word.tables
Jay Freedman
external usenet poster
 
Posts: 9,488
Default Word Formula using IF

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based on
an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help


Using Ctrl+F9 to insert each matched pair of field braces, use this syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.


  #3  
Old October 31st, 2007, 09:10 PM posted to microsoft.public.word.tables
Thomas
external usenet poster
 
Posts: 211
Default Word Formula using IF

Jay:

Thank you so much, this is exactly what I was looking for. It works great!
I was on your site today looking for this, but was not able to figure it out.
Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based on
an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help


Using Ctrl+F9 to insert each matched pair of field braces, use this syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.



  #4  
Old October 31st, 2007, 09:52 PM posted to microsoft.public.word.tables
Jay Freedman
external usenet poster
 
Posts: 9,488
Default Word Formula using IF

You're welcome.

By the way, it occurred to me as I reread my reply that there's a simpler
formula you could use:

{=MAX( {=H9*15%}, 1500)}

This one only calculates the 15% once, and then uses the larger of that
number or 1500.

Thomas wrote:
Jay:

Thank you so much, this is exactly what I was looking for. It works
great! I was on your site today looking for this, but was not able to
figure it out. Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based on
an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help


Using Ctrl+F9 to insert each matched pair of field braces, use this
syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.



  #5  
Old October 31st, 2007, 10:29 PM posted to microsoft.public.word.tables
macropod
external usenet poster
 
Posts: 1,231
Default Word Formula using IF

Hi Thomas,

A simpler way to code the field is:
{=IF(H9*0.151500,H9*0.15,1500)}

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Thomas" wrote in message ...
I have a small table with 4 or 5 fields that calculate based on the first
fields value. However, I need one field to calculate based on an IF
function.

I know how to write it in excel: =IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a formula
that can calculate 15% of the value in cell D1. If it is greater that 1,500,
place the calculated value in this field, if it is not greater than 1,500,
place 1,500 in this field.

Please Help

  #6  
Old March 25th, 2008, 06:25 PM posted to microsoft.public.word.tables
Dots
external usenet poster
 
Posts: 7
Default Word Formula using IF

I have a similar question. I have a calculated field that requires the
calculation from previous dropdown boxes. My formula, based on this previous
thread would look like this:
{IF {Dropdown6} = "Y"
{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5)+100)}

{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5))} 0}}

When I re-lock my form, I do not get any calculation in my form field. What
am I doing wrong???

Thanks.

"Thomas" wrote:

Jay:

Thank you so much, this is exactly what I was looking for. It works great!
I was on your site today looking for this, but was not able to figure it out.
Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based on
an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help


Using Ctrl+F9 to insert each matched pair of field braces, use this syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.



  #7  
Old March 25th, 2008, 07:46 PM posted to microsoft.public.word.tables
Jay Freedman
external usenet poster
 
Posts: 9,488
Default Word Formula using IF

First, each occurrence of the name of any of the dropdowns must be
surrounded by field markers. This is easy to fix: Double-click an occurrence
to select it, and press Ctrl+F9 to put the markers around it. Repeat for
each of the others. (If they don't have field markers, they're just
valueless text as far as the calculation is concerned.)

Second, the 0 and extra closing marker at the end of your formula don't
belong there. The general syntax of an IF field is

IF (condition) (value if true) (value if false)

In your formula, the condition is {Dropdown6} = "Y" and the two "equals"
fields are the true and false values, respectively; there's no place in the
syntax for that trailing 0.

Third, make sure that the "Calculate on exit" option is checked in the
Properties dialog of at least Dropdown6, and preferably for all six
dropdowns. If only Dropdown6 has that, then the formula will be recalculated
only when you exit that form field. If all of them have it, the formula will
be recalculated each time you leave any of the fields.

A final note: it's a really good idea to change the names of the form fields
(in the Bookmark box of the Properties dialog) to descriptive terms. Then
change the names in the calculated field to the same set of descriptive
terms. It will make the calculations much easier to understand, especially
if you have to edit them sometime later when you've forgotten how you put
them together.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

Dots wrote:
I have a similar question. I have a calculated field that requires
the calculation from previous dropdown boxes. My formula, based on
this previous thread would look like this:
{IF {Dropdown6} = "Y"
{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5)+100)}

{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5))}
0}}

When I re-lock my form, I do not get any calculation in my form
field. What
am I doing wrong???

Thanks.

"Thomas" wrote:

Jay:

Thank you so much, this is exactly what I was looking for. It works
great! I was on your site today looking for this, but was not able
to figure it out. Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based
on an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help

Using Ctrl+F9 to insert each matched pair of field braces, use this
syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.



  #8  
Old March 25th, 2008, 08:10 PM posted to microsoft.public.word.tables
Dots
external usenet poster
 
Posts: 7
Default Word Formula using IF

Jay,
Here is the exact syntax I am using. Only Dropdown6 is set to recalc on
exit. The Calculated field still does not show the result of the
calculation. Can you check my syntax to see if I am missing any special
characters? In my review it appears to be set correctly? I'd appreciate a
3rd eye.

{IF {Dropdown6} = Y {=
(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*15 )+({Dropdown4}*20)+({Dropdown5}*5)+100)}
{
=(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*1 5)+({Dropdown4}*20)+({Dropdown5}*5))}}

"Jay Freedman" wrote:

First, each occurrence of the name of any of the dropdowns must be
surrounded by field markers. This is easy to fix: Double-click an occurrence
to select it, and press Ctrl+F9 to put the markers around it. Repeat for
each of the others. (If they don't have field markers, they're just
valueless text as far as the calculation is concerned.)

Second, the 0 and extra closing marker at the end of your formula don't
belong there. The general syntax of an IF field is

IF (condition) (value if true) (value if false)

In your formula, the condition is {Dropdown6} = "Y" and the two "equals"
fields are the true and false values, respectively; there's no place in the
syntax for that trailing 0.

Third, make sure that the "Calculate on exit" option is checked in the
Properties dialog of at least Dropdown6, and preferably for all six
dropdowns. If only Dropdown6 has that, then the formula will be recalculated
only when you exit that form field. If all of them have it, the formula will
be recalculated each time you leave any of the fields.

A final note: it's a really good idea to change the names of the form fields
(in the Bookmark box of the Properties dialog) to descriptive terms. Then
change the names in the calculated field to the same set of descriptive
terms. It will make the calculations much easier to understand, especially
if you have to edit them sometime later when you've forgotten how you put
them together.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

Dots wrote:
I have a similar question. I have a calculated field that requires
the calculation from previous dropdown boxes. My formula, based on
this previous thread would look like this:
{IF {Dropdown6} = "Y"
{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5)+100)}

{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5))}
0}}

When I re-lock my form, I do not get any calculation in my form
field. What
am I doing wrong???

Thanks.

"Thomas" wrote:

Jay:

Thank you so much, this is exactly what I was looking for. It works
great! I was on your site today looking for this, but was not able
to figure it out. Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on the
first fields value. However, I need one field to calculate based
on an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write a
formula that can calculate 15% of the value in cell D1. If it is
greater that 1,500, place the calculated value in this field, if it
is not greater than 1,500, place 1,500 in this field.

Please Help

Using Ctrl+F9 to insert each matched pair of field braces, use this
syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.




  #9  
Old March 25th, 2008, 09:36 PM posted to microsoft.public.word.tables
Jay Freedman
external usenet poster
 
Posts: 9,488
Default Word Formula using IF

Sorry, it works for me, after converting the braces to field markers. Also
(as expected) it works the same in Word 2003 and 2007, and I have no reason
to expect different behavior in older versions.

What are the contents of the entries in Dropdown6? Are they literally N and
Y? And are the entries in the first 5 dropdowns all numbers?

And, in the same sense as the help desk question "Is it plugged in?", have
you checked to be sure forms protection is turned on?

Dots wrote:
Jay,
Here is the exact syntax I am using. Only Dropdown6 is set to recalc
on exit. The Calculated field still does not show the result of the
calculation. Can you check my syntax to see if I am missing any
special characters? In my review it appears to be set correctly?
I'd appreciate a 3rd eye.

{IF {Dropdown6} = Y {=
(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*15 )+({Dropdown4}*20)+({Dropdown5}*5)+100)}
{
=(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*1 5)+({Dropdown4}*20)+({Dropdown5}*5))}}

"Jay Freedman" wrote:

First, each occurrence of the name of any of the dropdowns must be
surrounded by field markers. This is easy to fix: Double-click an
occurrence to select it, and press Ctrl+F9 to put the markers around
it. Repeat for each of the others. (If they don't have field
markers, they're just valueless text as far as the calculation is
concerned.)

Second, the 0 and extra closing marker at the end of your formula
don't belong there. The general syntax of an IF field is

IF (condition) (value if true) (value if false)

In your formula, the condition is {Dropdown6} = "Y" and the two
"equals" fields are the true and false values, respectively; there's
no place in the syntax for that trailing 0.

Third, make sure that the "Calculate on exit" option is checked in
the Properties dialog of at least Dropdown6, and preferably for all
six dropdowns. If only Dropdown6 has that, then the formula will be
recalculated only when you exit that form field. If all of them have
it, the formula will be recalculated each time you leave any of the
fields.

A final note: it's a really good idea to change the names of the
form fields (in the Bookmark box of the Properties dialog) to
descriptive terms. Then change the names in the calculated field to
the same set of descriptive terms. It will make the calculations
much easier to understand, especially if you have to edit them
sometime later when you've forgotten how you put them together.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Dots wrote:
I have a similar question. I have a calculated field that requires
the calculation from previous dropdown boxes. My formula, based on
this previous thread would look like this:
{IF {Dropdown6} = "Y"
{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5)+100)}

{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5))}
0}}

When I re-lock my form, I do not get any calculation in my form
field. What
am I doing wrong???

Thanks.

"Thomas" wrote:

Jay:

Thank you so much, this is exactly what I was looking for. It
works great! I was on your site today looking for this, but was
not able to figure it out. Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on
the first fields value. However, I need one field to calculate
based on an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write
a formula that can calculate 15% of the value in cell D1. If it
is greater that 1,500, place the calculated value in this field,
if it is not greater than 1,500, place 1,500 in this field.

Please Help

Using Ctrl+F9 to insert each matched pair of field braces, use
this syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.



  #10  
Old March 25th, 2008, 09:47 PM posted to microsoft.public.word.tables
Dots
external usenet poster
 
Posts: 7
Default Word Formula using IF

Hi Jay,
I am using 2002. Funny thing is when I do the Ctrl+F9 to get the field
markers, nothing happens.... I'll keep trying. Thanks for your help!

"Jay Freedman" wrote:

Sorry, it works for me, after converting the braces to field markers. Also
(as expected) it works the same in Word 2003 and 2007, and I have no reason
to expect different behavior in older versions.

What are the contents of the entries in Dropdown6? Are they literally N and
Y? And are the entries in the first 5 dropdowns all numbers?

And, in the same sense as the help desk question "Is it plugged in?", have
you checked to be sure forms protection is turned on?

Dots wrote:
Jay,
Here is the exact syntax I am using. Only Dropdown6 is set to recalc
on exit. The Calculated field still does not show the result of the
calculation. Can you check my syntax to see if I am missing any
special characters? In my review it appears to be set correctly?
I'd appreciate a 3rd eye.

{IF {Dropdown6} = Y {=
(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*15 )+({Dropdown4}*20)+({Dropdown5}*5)+100)}
{
=(({Dropdown1}*25)+({Dropdown2}*20)+({Dropdown3}*1 5)+({Dropdown4}*20)+({Dropdown5}*5))}}

"Jay Freedman" wrote:

First, each occurrence of the name of any of the dropdowns must be
surrounded by field markers. This is easy to fix: Double-click an
occurrence to select it, and press Ctrl+F9 to put the markers around
it. Repeat for each of the others. (If they don't have field
markers, they're just valueless text as far as the calculation is
concerned.)

Second, the 0 and extra closing marker at the end of your formula
don't belong there. The general syntax of an IF field is

IF (condition) (value if true) (value if false)

In your formula, the condition is {Dropdown6} = "Y" and the two
"equals" fields are the true and false values, respectively; there's
no place in the syntax for that trailing 0.

Third, make sure that the "Calculate on exit" option is checked in
the Properties dialog of at least Dropdown6, and preferably for all
six dropdowns. If only Dropdown6 has that, then the formula will be
recalculated only when you exit that form field. If all of them have
it, the formula will be recalculated each time you leave any of the
fields.

A final note: it's a really good idea to change the names of the
form fields (in the Bookmark box of the Properties dialog) to
descriptive terms. Then change the names in the calculated field to
the same set of descriptive terms. It will make the calculations
much easier to understand, especially if you have to edit them
sometime later when you've forgotten how you put them together.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Dots wrote:
I have a similar question. I have a calculated field that requires
the calculation from previous dropdown boxes. My formula, based on
this previous thread would look like this:
{IF {Dropdown6} = "Y"
{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5)+100)}

{=((Dropdown1*25)+(Dropdown2*20)+(Dropdown3*15)+(D ropdown4*20)+(Dropdown5*5))}
0}}

When I re-lock my form, I do not get any calculation in my form
field. What
am I doing wrong???

Thanks.

"Thomas" wrote:

Jay:

Thank you so much, this is exactly what I was looking for. It
works great! I was on your site today looking for this, but was
not able to figure it out. Thank you very much.

"Jay Freedman" wrote:

Thomas wrote:
I have a small table with 4 or 5 fields that calculate based on
the first fields value. However, I need one field to calculate
based on an IF function.

I know how to write it in excel:
=IF(SUM(H9*15%)1500,SUM(H9*15%),1500)

but how do I write something like this in Word? I need to write
a formula that can calculate 15% of the value in cell D1. If it
is greater that 1,500, place the calculated value in this field,
if it is not greater than 1,500, place 1,500 in this field.

Please Help

Using Ctrl+F9 to insert each matched pair of field braces, use
this syntax:

{IF {=(H9*15%)} 1500 {=(H9*15%)} 1500}

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.




 




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 04:36 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.