Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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, -- John |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
Hi John,
All you need to do after choosing the last value is to press the tab key. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Training Spec." wrote in 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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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 "macropod" wrote: Hi John, All you need to do after choosing the last value is to press the tab key. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Training Spec." wrote in 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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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] ------------------------- "Training Spec." wrote in message ... 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 "macropod" wrote: Hi John, All you need to do after choosing the last value is to press the tab key. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Training Spec." wrote in 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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
Word 2007 Table with Calculation & Dropdown Fields
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 "Training Spec." wrote in message ... 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 "macropod" wrote: Hi John, All you need to do after choosing the last value is to press the tab key. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Training Spec." wrote in 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 "macropod" wrote: 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},{Dro pdown4}) \# 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] ------------------------- "Training Spec." wrote in 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, -- John |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form fields, calculation, etc. | Microsoft Word Help | |||
Word Table Calculation VBA | Tables | |||
WORD: Dropdown auto link and input into fields | Microsoft Word Help | |||
How do I set up calculation fields in Word with negative numbers? | Microsoft Word Help | |||
adding fields "calculation" | Mailmerge |