Word 2007 Table with Calculation & Dropdown Fields

[Training Spec.]

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[macropod]

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

Cheers

 

[Training Spec.]

Thanks, I'll try your first solution. In addition to numbers (1 - 5), I also
have an N/A in my dropdowns. If I understand you correctly, you can't have
letters in the dropdown; so, I'll delete them.

I'll let you know if this works for me.

Thanks again, this has been driving me crazy for two days, now!
--
John

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

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

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[Training Spec.]

I used the Average function and it works. But, it didn't automatically
update, so I unlocked the cell (which has the formula) so it would update
(CTRL+SHIFT+F11). It gives me an average of cells 1, 2 and 3 but not 4 (the
last one in the series)!

I have the 'Protect' cells on so a user can only make entries in the fields
(nothing else). So there's no way to remove the focus from the last entry
(which would allow the update).

Any ideas?
--
John

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

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

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[macropod]

Hi John,

All you need to do after choosing the last value is to press the tab key.

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

I used the Average function and it works. But, it didn't automatically
update, so I unlocked the cell (which has the formula) so it would update
(CTRL+SHIFT+F11). It gives me an average of cells 1, 2 and 3 but not 4 (the
last one in the series)!

I have the 'Protect' cells on so a user can only make entries in the fields
(nothing else). So there's no way to remove the focus from the last entry
(which would allow the update).

Any ideas?
--
John

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will
update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

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

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[Training Spec.]

Thanks, That did it! Next time you talk to Microsoft, please tell them they
need to work on this issue. There must be an easier way. Hopefully, we'll see
it in MS Word 2009.

Thanks again,
--
John

Hi John,

All you need to do after choosing the last value is to press the tab key.

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

I used the Average function and it works. But, it didn't automatically
update, so I unlocked the cell (which has the formula) so it would update
(CTRL+SHIFT+F11). It gives me an average of cells 1, 2 and 3 but not 4 (the
last one in the series)!

I have the 'Protect' cells on so a user can only make entries in the fields
(nothing else). So there's no way to remove the focus from the last entry
(which would allow the update).

Any ideas?
--
John

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will
update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

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

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[macropod]

Hi John,

All formfields work this way. Having some that do and some that don't wouldn't necessarily make things any easier for users.

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

Thanks, That did it! Next time you talk to Microsoft, please tell them they
need to work on this issue. There must be an easier way. Hopefully, we'll see
it in MS Word 2009.

Thanks again,
--
John

Hi John,

All you need to do after choosing the last value is to press the tab key.

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

I used the Average function and it works. But, it didn't automatically
update, so I unlocked the cell (which has the formula) so it would update
(CTRL+SHIFT+F11). It gives me an average of cells 1, 2 and 3 but not 4 (the
last one in the series)!

I have the 'Protect' cells on so a user can only make entries in the fields
(nothing else). So there's no way to remove the focus from the last entry
(which would allow the update).

Any ideas?
--
John


:

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and 'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric results in the dropdowns (ie you can't use spaces to
indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to treat dropdowns with non-numeric results as 0s. A quite
complex
construction could even be used to exclude dropdowns with 0s or non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on exit' set is up to you. If each has it, the formula will
update
as you tab away from each dropdown; otherwise it will only update when you tab away from the one so configured.

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

In the 4th column of my Table, I have 4 dropdown fields (each has scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for Excel,
Outlook, PowerPoint and Word. Then I entered the formula =AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That produced
an error. I tried "Calculate on Exit" with the four dropdown and with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,

 

[Doug Robbins - Word MVP]

FormFields are a legacy in Word 2007 and I would expect that they will
probably not be available in some future version.

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

Thanks, That did it! Next time you talk to Microsoft, please tell them
they
need to work on this issue. There must be an easier way. Hopefully, we'll
see
it in MS Word 2009.

Thanks again,
--
John

Hi John,

All you need to do after choosing the last value is to press the tab key.

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

message

I used the Average function and it works. But, it didn't automatically
update, so I unlocked the cell (which has the formula) so it would
update
(CTRL+SHIFT+F11). It gives me an average of cells 1, 2 and 3 but not 4
(the
last one in the series)!

I have the 'Protect' cells on so a user can only make entries in the
fields
(nothing else). So there's no way to remove the focus from the last
entry
(which would allow the update).

Any ideas?
--
John


:

Hi John,

You should be able to use a formula field coded as:
{=({Dropdown1}+{Dropdown2}+{Dropdown3}+{Dropdown4})/4 \# 0.00 }
or
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}) \# 0.00}
where the field braces (ie '{ }') are all created via Ctrl-F9 and
'Dropdown#' is the bookmark name assigned to each of your
dropdowns.

Note that either formula will fail while there are any non-numeric
results in the dropdowns (ie you can't use spaces to indicate
that the assessment is outstanding - you'd need to use 0 instead.

A more complicated construction using an IF test could be used to
treat dropdowns with non-numeric results as 0s. A quite complex
construction could even be used to exclude dropdowns with 0s or
non-numeric results from the averages.

Whether each dropdown field, or only one of them needs 'calculate on
exit' set is up to you. If each has it, the formula will
update
as you tab away from each dropdown; otherwise it will only update when
you tab away from the one so configured.

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

message
In the 4th column of my Table, I have 4 dropdown fields (each has
scores of 1
to 5). At the bottom of the 4th column, I have a Calculated field. I
have
tried
everyhting to get an Average of the 4 entries (dropdowns) in the
cell. I
bookmarked the cells as "e,o,p and w" to represent the scores for
Excel,
Outlook, PowerPoint and Word. Then I entered the formula
=AVERAGE(e,o,p,w).
That did not work. Next, I tried this formula =AVERAGE(D14). That
produced
an error. I tried "Calculate on Exit" with the four dropdown and
with/without
"Calculate on Exit" in the formula cell. Nothing works!

If I type in some numbers in the cells, the formula works fine.

Bottom Line: What is the formula for averaging four dropdown fields?

Here's a visual of the 3rd and 4th columns of my Table:
Excel (dropdown)
Outlook (dropdown)
PowerPoint (dropdown)
Word (dropdown)
Average (calculated field)

Thanks,