Erroneous Table Math

You may have noticed that if you do table math in a Word table, some of your answers may not be exactly what you would expect. For instance, if you are using the =SUM(ABOVE) function in a table cell to add together the values in a table, the function will add any numeric value in any cell. Thus, if your headings have text in them, such as Quarter 1, Quarter 2, and so on, the function will pull the values 1 and 2 out of the cells and add them into your total.

There are several ways around this problem. The first is to use a specific range in the SUM function instead of the ABOVE attribute. The only drawback is if you edit the table, it could throw your SUM calculations off. Thus, if you add or delete rows from your table on a regular basis, you would need to check the SUM range over and over again to make sure it is always working on the proper range of cells.

A related approach is to make your summation field just a bit more complex—just subtract the first row from the sum. For instance, you could use the following field in the cell where you want the total to appear:

{ =SUM(ABOVE) – A1 }

This formulaic approach is a simple way to make sure that you compensate for the known "glitch" in how Word applies the ABOVE attribute.

Another option is to spell out the numbers in the cells you don't want added in. This would require using headings such as Quarter One and Quarter Two, rather than Quarter 1 and Quarter 2. You could also use Roman numerals, such as Quarter I and Quarter II. For many purposes, the approaches might not be acceptable.

Still another workaround is to insert a blank row between your headings and the body of your table. The =SUM(ABOVE) function stops adding when it reaches a blank row. If you don't want the appearance of a blank row, then you can always format the row height to be a very small value, such as a single point. (One point is the smallest value that you can use for a row height.) If the table borders are off, a row this small will be all but invisible.

A variation on this approach is to split your table into two: the first table would be just your headings, while the body would be in the next table. Then, either format the intervening paragraph so it is very small (again, 1 point is nice) or simply format the paragraph as hidden text and make sure hidden text doesn't print on your printout. The result is that the two tables print so close to each other that they look like one. The drawback is that if you need to subsequently change table column width, you must do it for each of the tables independently.

Perhaps one of the most interesting workarounds is to replace the space just before the number in the heading with a different symbol that looks like a space, but which causes Word to ignore the numbers following it. Try the following:

1. Select the space in the heading, just before the quarter number.
2. Choose Symbol from the Insert menu. Word displays the Symbol dialog box.
3. Make sure the Symbol font is selected.
4. Double-click on the very first character in the font table. It looks like a blank. Word replaces the space you selected in step 1 with the character.
5. Click on Close.
6. Recalculate the totals in the column.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (1398) applies to Microsoft Word 97, 2000, 2002, and 2003.