WD: Table Formula SUM(Above) Adds Number from Heading Row

ID: Q188407

The information in this article applies to:

Microsoft Word for Windows, versions 6.0, 6.0a, 6.0c
Microsoft Word for Windows 95, versions 7.0, 7.0a
Microsoft Word for the Macintosh, versions 6.0, 6.0.1, 6.0.1a

SYMPTOMS

In the versions of Word listed at the beginning of this article, if a table row is formatted as a heading row (select the row and click Headings on the Table menu), the results of =SUM(Above) and =AVERAGE(Above) formulas may not be what you expect.

CAUSE

Word includes values from the heading row in the calculation.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

When Word searches for values to include in the formula results, it starts at the formula cell and continues up until it finds the first cell containing a number. It then calculates up until it encounters a blank cell or a cell containing both a number and text above the last cell that contains a number only.

The following sample tables show formula results that are incorrect (they include numbers from the heading row) and others in which the results are correct.

Sample Table with Formulas


   ---------------------------------------------------------
   |      1      |     Two     |    No. 3    |      4      |
   ---------------------------------------------------------

   |      2      |      2      |      2      |             |
   ---------------------------------------------------------
   |      3      |      3      |   row 3     |      3      |
   ---------------------------------------------------------
   |{=SUM(Above)}|{=SUM(Above)}|{=SUM(Above)}|{=SUM(Above)}|
   ---------------------------------------------------------

Sample Table with Results



   ---------------------------------------------------------
   |      1      |     Two     |    No. 3    |      4      |
   ---------------------------------------------------------
   |      2      |      2      |      2      |             |
   ---------------------------------------------------------
   |      3      |      3      |  row  3     |      3      |
   ---------------------------------------------------------
   |      6      |      5      |      5      |      3      |
   ---------------------------------------------------------

Row 1 of the sample table is a heading row (to create a heading row, select the row and click Headings on the Table menu).

Result of row 4, column 1:

The result of the formula includes the numeric value of the heading for that column.

Result of row 4, column 2:

The result of the formula includes the set of contiguous cells that contain numeric values.

Result of row 4, column 3:

The result of the formula does not include the heading for that column because that cell also contains text characters. Note, however, that the value of row 3, column 3, is included in the result because the cell following it contains only a number. A numeric value that occurs along with text is included with the formula results if Word encounters it before the first cell containing only numbers (Word calculates from the formula upwards).

Result of row 4, column 4:

The result of the formula does not include the heading for that column because there is a blank cell above the last cell containing only numbers.

Additional query words: sum total calculate

Keywords : kbdta kbtable
Version : MACINTOSH:6.0,6.0.1,6.0.1a; WINDOWS:6.0,6.0a,6.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug