If functions in table cells

[Kay]

I have read some of the post on calcualations in word tables and have dome
many over the years, but not as complicated as I have seen in the download
files. I have a word table that recieves its data as a bookmark from a form
in another section. If the bookmark returns 1 - Unsatisfactory, I want the
next column in the corresponding row to show 1 so that I can then total that
column and divide by the number of entries. I assumed...{if(B2="1 -
Unsatisfactory",1,"")}. I always get a syntax error. Inf act, I need to
nest five ifs. if(B2="1 - Unsatisfactory",1,if(B2="2 - Nees
Improvement",2,if(b2=3 - Meets Expcetations",3," ")}. etc...

Any help will be appreciated.

 

[Doug Robbins - Word MVP]

If you select the text in B2 and assign a Bookmark B2 to ti, then you can
use:

{ IF { REF B2 } = "1 - Unsatisfactory" 1 }

--
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

 

[macropod]

Hi Kay,

This can be ridiculously easy - if you get rid of that hyphen (perhaps replace it with a colon). Then all you need is {=B1}

Cheers

 

[Kay]

Doug,

The content of the cell I need to reference in the formula is a reference
from a bookmark. That would be like a double reference, unless you mean to
reference the cell and not the contents. If so, can I nest five times and
what would the syntax look like. Do you have to provide all three arguments
for the "if"?

Thanks for your help!

 

[Kay]

Are you suggesting that the "-" keeps the function from working. I tried
replacing it, but remember, I still need to nest five ifs..and maybe it is
just my syntax {if(B2= "1. Unsatisfactory" ,1,if(b2= "2. Needs
Improvement",2,"")

Thanks for your help!

Hi Kay,

This can be ridiculously easy - if you get rid of that hyphen (perhaps replace it with a colon). Then all you need is {=B1}

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

I have read some of the post on calcualations in word tables and have dome
many over the years, but not as complicated as I have seen in the download
files. I have a word table that recieves its data as a bookmark from a form
in another section. If the bookmark returns 1 - Unsatisfactory, I want the
next column in the corresponding row to show 1 so that I can then total that
column and divide by the number of entries. I assumed...{if(B2="1 -
Unsatisfactory",1,"")}. I always get a syntax error. Inf act, I need to
nest five ifs. if(B2="1 - Unsatisfactory",1,if(B2="2 - Nees
Improvement",2,if(b2=3 - Meets Expcetations",3," ")}. etc...

Any help will be appreciated.

 

[macropod]

Hi Kay,

Simply removing the hyphen will allow the formula field to extract just the number, which is what I understand you're trying to use
the IF test for.

What happens when a formula field encounters text in a cell is that it ignores the text and tries to perform a calculation using any
numbers and mathematical operators it finds there. In this case, the hyphen is acting like a minus sign, but there's only one number
on the cell, so the formula field thinks you've got an invalid formula and returns a 0. If you had a second number in the cell, and
kept the hyphen, the formula field would subtract the second number from the first. If you've got two numbers an no mathematical
operator (ie + - / *) Word adds them together. Try pointing a formula field to a cell containing a date in dd/mm/yy format and you
get the day divided by the month divided by the year. Bizarre!

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

Are you suggesting that the "-" keeps the function from working. I tried
replacing it, but remember, I still need to nest five ifs..and maybe it is
just my syntax {if(B2= "1. Unsatisfactory" ,1,if(b2= "2. Needs
Improvement",2,"")

Thanks for your help!

Hi Kay,

This can be ridiculously easy - if you get rid of that hyphen (perhaps replace it with a colon). Then all you need is {=B1}

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

I have read some of the post on calcualations in word tables and have dome
many over the years, but not as complicated as I have seen in the download
files. I have a word table that recieves its data as a bookmark from a form
in another section. If the bookmark returns 1 - Unsatisfactory, I want the
next column in the corresponding row to show 1 so that I can then total that
column and divide by the number of entries. I assumed...{if(B2="1 -
Unsatisfactory",1,"")}. I always get a syntax error. Inf act, I need to
nest five ifs. if(B2="1 - Unsatisfactory",1,if(B2="2 - Nees
Improvement",2,if(b2=3 - Meets Expcetations",3," ")}. etc...

Any help will be appreciated.

 

[Kay]

You are so clever....thanks so much. It works perfectly!!!!!!

Hi Kay,

Simply removing the hyphen will allow the formula field to extract just the number, which is what I understand you're trying to use
the IF test for.

What happens when a formula field encounters text in a cell is that it ignores the text and tries to perform a calculation using any
numbers and mathematical operators it finds there. In this case, the hyphen is acting like a minus sign, but there's only one number
on the cell, so the formula field thinks you've got an invalid formula and returns a 0. If you had a second number in the cell, and
kept the hyphen, the formula field would subtract the second number from the first. If you've got two numbers an no mathematical
operator (ie + - / *) Word adds them together. Try pointing a formula field to a cell containing a date in dd/mm/yy format and you
get the day divided by the month divided by the year. Bizarre!

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

Are you suggesting that the "-" keeps the function from working. I tried
replacing it, but remember, I still need to nest five ifs..and maybe it is
just my syntax {if(B2= "1. Unsatisfactory" ,1,if(b2= "2. Needs
Improvement",2,"")

Thanks for your help!

Hi Kay,

This can be ridiculously easy - if you get rid of that hyphen (perhaps replace it with a colon). Then all you need is {=B1}

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

I have read some of the post on calcualations in word tables and have dome
many over the years, but not as complicated as I have seen in the download
files. I have a word table that recieves its data as a bookmark from a form
in another section. If the bookmark returns 1 - Unsatisfactory, I want the
next column in the corresponding row to show 1 so that I can then total that
column and divide by the number of entries. I assumed...{if(B2="1 -
Unsatisfactory",1,"")}. I always get a syntax error. Inf act, I need to
nest five ifs. if(B2="1 - Unsatisfactory",1,if(B2="2 - Nees
Improvement",2,if(b2=3 - Meets Expcetations",3," ")}. etc...

Any help will be appreciated.