Word 2007 Table with Calculation & Dropdown Fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Training Spec. - 17 Apr 2008 13:12 GMT

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(D1:D4). 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,

Signature

John

Reply to this Message

macropod - 17 Apr 2008 15:54 GMT

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

Signature

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
[quoted text clipped - 18 lines]
>
> Thanks,

Reply to this Message

Training Spec. - 17 Apr 2008 16:12 GMT

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!

Signature

John

> Hi John,
>
[quoted text clipped - 37 lines]
> >
> > Thanks,

Reply to this Message

Training Spec. - 17 Apr 2008 18:44 GMT

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?

Signature

John

> Hi John,
>
[quoted text clipped - 37 lines]
> >
> > Thanks,

Reply to this Message

macropod - 17 Apr 2008 20:20 GMT

Hi John,

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

Cheers

Signature

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
[quoted text clipped - 49 lines]
>> >
>> > Thanks,

Reply to this Message

Training Spec. - 18 Apr 2008 13:12 GMT

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,

Signature

John

> Hi John,
>
[quoted text clipped - 54 lines]
> >> >
> >> > Thanks,

Reply to this Message

macropod - 18 Apr 2008 14:12 GMT

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

Signature

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
[quoted text clipped - 62 lines]
>> >> >
>> >> > Thanks,

Reply to this Message

Doug Robbins - Word MVP - 19 Apr 2008 23:31 GMT

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

Signature

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
[quoted text clipped - 79 lines]
>> >> >
>> >> > Thanks,

Reply to this Message