Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
I've got 10 Text Form Fields that are all named. I want to average the
numbers that are entered into those fields. The only problem is that not all fields are used every time. I've tried this two ways and neither seems to work. The first is "=average(field1, field2, etc.)". This only works if all fields are used. The second way is to sum the results of the fields, then divide that by a count of the fields in use. To get the count of the fields in use I used a separate field for each that calculates "=(if, field11,1,0)", then I sum all those up to get the number. I can get the sum and the count to work, but when I divide the sum by the count it doesn't work. Any ideas? |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
Hi John,
Since you're dealing with formfields, I take it that you mean the unused ones are simply left with the default 5 spaces. I'm assuming also that a '0' value might actually be a valid number for averaging. In that case, you'll have to code a formula field something with along the lines of: {=({IF{Field1}= " " 0 {Field1}}+{IF{Field2}= " " 0 {Field2}} +{IF{Field3}= " " 0 {Field3}}+{IF{Field4}= " " 0 {Field4}} +{IF{Field5}= " " 0 {Field5}}+{IF{Field6}= " " 0 {Field6}} +{IF{Field7}= " " 0 {Field7}}+{IF{Field8}= " " 0 {Field8}} +{IF{Field9}= " " 0 {Field9}}+{IF{Field10}= " " 0 {Field10}}) /({IF{Field1}= " " 0 1}+{IF{Field2}= " " 0 1}+ {IF{Field3}= " " 0 1}+{IF{Field4}= " " 0 1}+ {IF{Field5}= " " 0 1}+{IF{Field6}= " " 0 1}+ {IF{Field7}= " " 0 1}+{IF{Field8}= " " 0 1}+ {IF{Field9}= " " 0 1}+{IF{Field10}= " " 0 1})} Cheers -- macropod [MVP - Microsoft Word] "JohnS" wrote in message ... | I've got 10 Text Form Fields that are all named. I want to average the | numbers that are entered into those fields. The only problem is that not all | fields are used every time. I've tried this two ways and neither seems to | work. | | The first is "=average(field1, field2, etc.)". This only works if all | fields are used. | | The second way is to sum the results of the fields, then divide that by a | count of the fields in use. To get the count of the fields in use I used a | separate field for each that calculates "=(if, field11,1,0)", then I sum all | those up to get the number. I can get the sum and the count to work, but | when I divide the sum by the count it doesn't work. | | Any ideas? |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
Hi John,
Here's a simpler field construct to work with: {=SUM({=Field1},{=Field2},{=Field3},{=Field4},{=Fi eld5},{=Field6},{=Field7},{=Field8},{=Field9},{=Fi eld10})/ SUM({=Field10},{=Field20},{=Field30},{=Field40 },{=Field50},{=Field60},{=Field70},{=Field80}, {=Field90},{=Field100})} Cheers -- macropod [MVP - Microsoft Word] "JohnS" wrote in message ... | I've got 10 Text Form Fields that are all named. I want to average the | numbers that are entered into those fields. The only problem is that not all | fields are used every time. I've tried this two ways and neither seems to | work. | | The first is "=average(field1, field2, etc.)". This only works if all | fields are used. | | The second way is to sum the results of the fields, then divide that by a | count of the fields in use. To get the count of the fields in use I used a | separate field for each that calculates "=(if, field11,1,0)", then I sum all | those up to get the number. I can get the sum and the count to work, but | when I divide the sum by the count it doesn't work. | | Any ideas? |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
Hi John,
My last post only works if 0s aren't valid values. If a '0' value is a valid value, you can use a composite of the previous two posts, coded along the lines of: {=SUM({=Field1},{=Field2},{=Field3},{=Field4},{=Fi eld5},{=Field6},{=Field7},{=Field8},{=Field9},{=Fi eld10})/ ({IF{Field1}= " " 0 1}+{IF{Field2}= " " 0 1}+ {IF{Field3}= " " 0 1}+{IF{Field4}= " " 0 1}+ {IF{Field5}= " " 0 1}+{IF{Field6}= " " 0 1}+ {IF{Field7}= " " 0 1}+{IF{Field8}= " " 0 1}+ {IF{Field9}= " " 0 1}+{IF{Field10}= " " 0 1})} Cheers -- macropod [MVP - Microsoft Word] "JohnS" wrote in message ... | I've got 10 Text Form Fields that are all named. I want to average the | numbers that are entered into those fields. The only problem is that not all | fields are used every time. I've tried this two ways and neither seems to | work. | | The first is "=average(field1, field2, etc.)". This only works if all | fields are used. | | The second way is to sum the results of the fields, then divide that by a | count of the fields in use. To get the count of the fields in use I used a | separate field for each that calculates "=(if, field11,1,0)", then I sum all | those up to get the number. I can get the sum and the count to work, but | when I divide the sum by the count it doesn't work. | | Any ideas? |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
I tried all three variants. One issue I run into is that the number of
characters allowed in the calculation field is too few for these formulas. However, even when I break them up into different text form fields, they still don't count the number of fields that are greater than 0. It just gives me a blank field. Any other ideas? This would be so easy to do in Excel... "macropod" wrote: Hi John, My last post only works if 0s aren't valid values. If a '0' value is a valid value, you can use a composite of the previous two posts, coded along the lines of: {=SUM({=Field1},{=Field2},{=Field3},{=Field4},{=Fi eld5},{=Field6},{=Field7},{=Field8},{=Field9},{=Fi eld10})/ ({IF{Field1}= " " 0 1}+{IF{Field2}= " " 0 1}+ {IF{Field3}= " " 0 1}+{IF{Field4}= " " 0 1}+ {IF{Field5}= " " 0 1}+{IF{Field6}= " " 0 1}+ {IF{Field7}= " " 0 1}+{IF{Field8}= " " 0 1}+ {IF{Field9}= " " 0 1}+{IF{Field10}= " " 0 1})} Cheers -- macropod [MVP - Microsoft Word] "JohnS" wrote in message ... | I've got 10 Text Form Fields that are all named. I want to average the | numbers that are entered into those fields. The only problem is that not all | fields are used every time. I've tried this two ways and neither seems to | work. | | The first is "=average(field1, field2, etc.)". This only works if all | fields are used. | | The second way is to sum the results of the fields, then divide that by a | count of the fields in use. To get the count of the fields in use I used a | separate field for each that calculates "=(if, field11,1,0)", then I sum all | those up to get the number. I can get the sum and the count to work, but | when I divide the sum by the count it doesn't work. | | Any ideas? |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Text Form Field Calculation
Hi John,
| I tried all three variants. One issue I run into is that the number of | characters allowed in the calculation field is too few for these formulas. How so? Word formula fields can handle numbers with up to 15 digits and up to 20 nesting levels (I only had two levels) and a document can have up to 32,000 fields. | However, even when I break them up into different text form fields, they | still don't count the number of fields that are greater than 0. It just | gives me a blank field. To get a count of the number of fields *greater than 0*, where the field names are 'Field1' - 'Field10', all you need is: {=SUM({=Field10},{=Field20},{=Field30},{=Field4 0},{=Field50},{=Field60},{=Field70},{=Field80 },{=Field90},{=Field100})} Your reference to 'different text form fields' suggests you might be trying to do this using a calculation formfield. DON'T. Instead, use an ordinary formula field. For example, to create the above: .. press Alt-F9 to toggle field code display 'on'. .. select the location where you want the field to go .. press Ctrl-F9 twice. You see a nested pair of field braces (ie '{ { } }') .. fill nested pair of field braces in so that you get '{=SUM({=Field10},)}' .. copy the '{=Field10},' portion of the field and paste it in after the ',' nine times, then delete the final ','. You should now have a field looking like: '{=SUM({=Field10},{=Field10},{=Field10},{=Field 10},{=Field10},{=Field10},{=Field10},{=Field1 0},{=Field10},{=Field10},)}' .. edit the 'Field1' strings so that you get a 'Field1' - 'Field10' sequence, looking like: {=SUM({=Field10},{=Field20},{=Field30},{=Field4 0},{=Field50},{=Field60},{=Field70},{=Field80 },{=Field90},{=Field100})} .. press Alt-F9 to toggle field code display 'off'. .. protect your form Now fill in the form fields. As you do, you'll see the field you've just created update to give a count of the number of fields greater than 0. To get the averages, you'll need to unprotect the document and edit the formula field, by inserting: 'SUM({=Field1},{=Field2},{=Field3},{=Field4},{=Fie ld5},{=Field6},{=Field7},{=Field8},{=Field9},{=Fie ld10})/' just after the '=' sign. Cheers -- macropod [MVP - Microsoft Word] |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating dynamic cross reference links in a Word document | Microsoft Word Help | |||
Setting Date property in a form field | Microsoft Word Help | |||
Forms and continuous section breaks (dropdown & fill-ins) | Page Layout | |||
Autofill of Text Form Field based on another Text Form Field | Microsoft Word Help | |||
Text form field inside a WORD table cell, odd behavior when select | Tables |