Written by Allen Wyatt (last updated July 8, 2020)
This tip applies to Word 97, 2000, 2002, and 2003
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:
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.
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!
Creating a table in Word is a relatively simple task. When you want to format or edit information in the table, often the ...
Discover MoreOnce a table is inserted in your document, you can use the mouse to adjust the width of columns. The effect the mouse ...
Discover MoreWord includes a powerful feature that allows you to add footnotes and endnotes to your document. What if you want them at ...
Discover MoreFREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
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?
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.
Visit the WordTips channel on YouTube
FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments