Average command issue in Word

Question

In Word 2010, how can i average fields leaving blank fields omitted in the calculation like Excel does?  Word counts all fields whether populated or not and returns an incorrect value as the result.  For example, if i have only two values populated, (5,3) in the table and two other fields not populated or blank, i get an average value of 2 instead of 4.  Excel handles this well, but I'm not able to get the same results in Word.  Audience will only be using Word to complete the form.  Thanks for the help!

Answer 1

Thanks macropod!  That worked great for me!  I was using the ABOVE instead of A1:A5.  When i changed my formula to the range, it now works the way you said it would!  Thanks for your timely input and for your expertise!!!  it is  greatly appreciated!!!  Problem solved!!!!  :)

Answer 2

With a formula field coded as:

{=AVERAGE(A1:A5)}

your result should be 4 - that's what I get. If you're using a formula field coded as:

{AVERAGE(ABOVE)

you're liable to get spurious results when cells above are empty. Your should also avoid using formfields for the calculations. See the discussion in my Microsoft Word Field Maths Tutorial, at:

http://windowssecrets.com/forums/showthread.php/154369-Microsoft-Word-Field-Maths-Tutorial

or:

http://www.gmayor.com/downloads.htm#Third_party

Answer 3

Thanks macropod.  I have created a table within Word with 5 fields for a test. 

5

3

blank, no 0

blank, no 0

then Average formula.

My result is 2, not 4, because Word DOES count the blank field within its calculation.  Within Excel, it is correct with 4 as the result.  When I embed an Excel worksheet within Word for my users to use. Word does not carry over the formula, and will not update when entries are made to it without manual intervention.

If you what i am doing incorrectly, please advise.  Thanks,

Answer 4

Word's AVERAGE field function ignores blank cells (but not cells containing 0) in a table, regardless of whether those cells get their input via fields.

Answer 5

One solution is to embed an Excel sheet in the Word document. The only other solution is to write a fairly complex macro in the Word document, which would probably trigger security warnings when the document is opened on other computers.