Erroneous Table Math

Written by Allen Wyatt (last updated July 8, 2020)
This tip applies to Word 97, 2000, 2002, and 2003


1

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Unicode Characters

Unicode is a character-encoding scheme that works with a huge variety of characters. This tip explains what Unicode is ...

Discover More

Automatically Applying Custom Styles to Footnotes

Word is great in that it allows you to create styles that define how you want your text to appear. If you spend a great ...

Discover More

Grouping Tiles on the Start Screen

The Start screen can serve as your launching pad for whatever programs you desire. You can move tiles around on the Start ...

Discover More

The First and Last Word on Word! Bestselling For Dummies author Dan Gookin puts his usual fun and friendly candor back to work to show you how to navigate Word 2013. Spend more time working and less time trying to figure it all out! Check out Word 2013 For Dummies today!

More WordTips (menu)

Picking a Contiguous Range of Table Cells

Creating a table in Word is a relatively simple task. When you want to format or edit information in the table, often the ...

Discover More

Resizing Table Columns with the Mouse

Once a table is inserted in your document, you can use the mouse to adjust the width of columns. The effect the mouse ...

Discover More

Footnotes for Tables

Word includes a powerful feature that allows you to add footnotes and endnotes to your document. What if you want them at ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six minus 4?

2018-12-14 09:47:02

Andrew McCollum

Hey, I could use some help. I am using a spreadsheet that has amounts, descriptions, and reference codes. It is all fake data so I can create some fake bank statements for a class I am teaching. I am using mail merge to insert the amounts, descriptions, and reference codes into the table in a Word document. I then am using the Formula in Word tables to "try" to calculate the total of the amount. It doesn't seem to recognize or know what to do with negative numbers coming over from the mail merge. My math is completely wrong.

Do you know of any way to make Word play nice when summing negative numbers?


This Site

Got a version of Word that uses the menu interface (Word 97, Word 2000, Word 2002, or Word 2003)? This site is for you! If you use a later version of Word, visit our WordTips site focusing on the ribbon interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.