Performing Calculations in Tables

Word tables are useful for organizing text into rows and columns and for providing an attractive layout option for lists and other data. But tables get especially powerful and dynamic when you apply formulas to the numeric data contained within a table's rows or columns. For example, if you have a table of sales for various departments, you could display the total sales in a cell at the bottom of the table. Similarly, if your table lists the gross margins from all company divisions, you could display the average gross margin in a cell.

Referencing Table Cells

The trick to using formulas within tables is to reference the table cells correctly. The easiest way to do this is to use the relative referencing that's built into Word tables:

Relative Reference	Refers To
ABOVE	All the cells above the formula cell in the same column.
BELOW	All the cells below the formula cell in the same column.
LEFT	All the cells to the left of the formula cell in the same row.
RIGHT	All the cells to the right of the formula cell in the same row.

For example, the following formula field sums all the numeric values in the cells above the formula cell in the same column:

 { =SUM(ABOVE) } 

Figure 1.27 shows an example of this formula field in action in the subtotal cell (D12, showing the result $90.17).

Figure 1.27. The subtotal in this invoice document is calculated using a field with the formula =SUM(ABOVE).

If you need to refer to specific cells in your formula, you should use absolute referencing, which is very similar to the cell referencing used by Excel. That is, the table columns are assigned the letters A (for the first column), B (second column), and so on; the table rows are assigned the numbers 1 (for the first row), 2 (second row), and so on. Following are some examples:

Absolute Reference	Refers To
A1	The cell in the first row and first column.
D5	The cell in the fifth row and fourth column.
A1,D5	The cells A1 and D5.
A1:D5	The rectangular range of cells created by A1 in the top-left corner and D5 in the bottom-right corner.
B:B	All the cells in the second column.
3:3	All the cells in the third row.

In the invoice shown in Figure 1.27, for example, the total for the Non-Reflective Mirror item is derived using the following formula field:

 { =A2 * C2 \# $0.00 } 

An Entire Row/Column Gotcha

If you want to use an absolute reference for an entire column (such as A:A) or an entire row (such as 1:1), make sure you place your formula in a different row or column. For example, consider the following formula field:

 { =SUM(A:A) } 

This sums all the cell values in the first column. However, if you insert the field into a cell in column A, the field result will be included in the sum the next time you update the field. In Excel, this would be flagged as a circular reference. In Word, you just get the wrong answer.

Finally, you can also use bookmarks to create formulas that have "named" operands. For example, if you select a cell and insert a bookmark named GrossMargin, you can refer to that cell using the bookmark name, as in this example:

 { =B3 * GrossMargin } 

In the invoice document shown in Figure 1.27, I assigned a bookmark named Subtotal to the subtotal cell. To calculate the sales tax value, I used the following formula field:

 { = Subtotal * .05 } 

For Sophisticated Formulas, Use Excel

Given the row-and-column format of a table and the use of Excel-like absolute referencing, Word's tables bear some resemblance to an Excel worksheet. However, that resemblance is vague, at best. Given the relatively limited set of functions available with fields, and without any proper spreadsheet tools at your disposal, you should use Word tables for only the simplest of formula tasks. If you need something more powerful, you can always embed an Excel worksheet right into a Word document. See the section in Chapter 6 titled "Inserting an Object from Another Application," to learn how this is done.

This also works for entire tables, although in an annoyingly quirky way. Select your table (via Table, Select, Table) and then insert a bookmark to name it. You can then reference a cell in that table as follows:

 SUM(TableName Cell) 

Here, TableName is the bookmark name for the table, and Cell is an absolute cell reference. For example, the following code refers to cell A1 in a table named Table1:

 SUM(Table1 A1) 

You might think I used the preceding SUM function as an example, but that's not the case. To reference a cell in another table in Word, you must include the table name and cell reference inside a function! In other words, the following apparently sensible code in fact produces a syntax error in Word:

 Table1 A1 

The SUM function is most often used because the sum of a single cell is the value of the cell itself. You can also use AVERAGE, COUNT, MAX, MIN, or PRODUCT, if you want to be different.

For example, to refer to the Total Due cell in the table in Figure 1.27, I first selected the table and assigned a bookmark named InvoiceDetail. With that done, the following formula field references the Total Due cell, as shown in Figure 1.28:

 { =SUM(InvoiceDetail C15) } 

Figure 1.28. Assign a bookmark to a table to reference cells in that table from other parts of the document.

Another Reference Gotcha

Why does the formula reference C15 instead of D15? Because the bottom four rows of the table have only three columns. Therefore, the rightmost column is column C for those rows.

Solving a Relative Reference Problem

There's an important limitation to the relative references I mentioned earlier. When it comes upon a relative reference, Word gathers the cells to use in the formula by moving along the column (for ABOVE and BELOW) or the row (for LEFT and RIGHT). Crucially, it stops moving along the row or column when it comes to a cell that's either empty or that contains text. Even if you have more numbers further along the row or column, Word doesn't see them.

To show you how this works, consider the version of the invoice table shown in Figure 1.29.

Figure 1.29. When you use a relative reference, Word stops when it hits a cell that is blank or that contains text.

Notice how the Total cell for the Teflon Bath Mat item (D4) is blank. If you examine the Subtotal, the result is clearly incorrect. That's because the ABOVE relative reference applies only to cells D5 through D11. Because cell D4 is empty, the sum doesn't include the numeric values in cells D2 and D3.

When the Problem Isn't a Problem

This relative reference behavior may seem brain dead, but at times it comes in handy. For example, the formula that calculates the Total Due value in our invoice table is =SUM(ABOVE). This sums the Shipping & Handling, Sales Tax, and Subtotal cells, but then stops because the cell above Subtotal is blank. This is what we want, so in this case, the relative reference "problem" isn't really a problem at all.

One way to work around this problem is to replace the relative reference with an absolute reference:

 { =SUM(D2:D11) } 

That works, as you can see in Figure 1.30.

Figure 1.30. One way to work around Word's relative reference problem is to use absolute referencing.

The problem with this workaround is that it's not particularly flexible. That is, if you add or delete rows in the table, you must edit the formula by hand.

A better solution is to create two tables by splitting the original table just above the SUM formula. (The easiest way to do this is to place the cursor in the row that will form the top of the second table, and then select the Table, Split Table command.) Now use a bookmark to name the table containing the values you want to sum, and then reference this table in the formula.

Figure 1.31 shows the split invoice table. The top table I've named OrderDetail, and I've changed the Subtotal field to the following:

 { =SUM(OrderDetail D:D) } 

Figure 1.31. To always get an accurate sum, place the SUM formula in a separate table and then reference the table containing the values.

Now, no matter how many rows you add or delete in the OrderDetail table, the formula will always return the correct subtotal.

"Hiding" the Table Split

To make this workaround effective, you should hide the paragraph between the two tables. Place the cursor in the paragraph and select Format, Font. Reduce the font size to 1 point, and activate the Hidden effect.