Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.docmanagement
JohnS JohnS is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.word.docmanagement
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default 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   Report Post  
Posted to microsoft.public.word.docmanagement
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default 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   Report Post  
Posted to microsoft.public.word.docmanagement
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default 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   Report Post  
Posted to microsoft.public.word.docmanagement
JohnS JohnS is offline
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.word.docmanagement
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default 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

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating dynamic cross reference links in a Word document torajudo Microsoft Word Help 5 April 27th 23 08:57 PM
Setting Date property in a form field mdavison Microsoft Word Help 13 October 6th 06 02:43 PM
Forms and continuous section breaks (dropdown & fill-ins) MJones Page Layout 9 August 26th 06 07:13 AM
Autofill of Text Form Field based on another Text Form Field Brett Kinross Microsoft Word Help 3 November 24th 05 04:49 AM
Text form field inside a WORD table cell, odd behavior when select pwrichcreek Tables 9 October 20th 05 12:34 AM


All times are GMT +1. The time now is 10:51 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"