Calculations and Functions in Word Tables

What is the one application you can be sure to find on just about every single personal computer, whether hooked up to the Internet or not? Word processing, right? And yet, the early success of the PC was not due to the popularity of word processing. After all, in the early 1980s there were electronic typewriters and word processors much cheaper than the \$2,000 to \$3,000 IBM PCs and clones. And with predominantly black and green monitors with no hard drives, only rudimentary games could be played.

What drove businesses to install personal computers in offices and made home users feel compelled to “keep up” was the introduction of spreadsheet software. Daniel Bricklin invented VisiCalc in 1979, giving the computer world a program that could organize and calculate information in the traditional, accounting worksheet format (columns and rows). Unlike paper-and-pencil worksheets, VisiCalc and its better known successors, such as Lotus 1-2-3 and Microsoft Excel, can be modified, copied, and deleted just like word processing documents.

Including Mathematical Tables in Word Documents
To risk stating the obvious, word processors such as Microsoft Word are best at handling text, and spreadsheet programs such as Microsoft Excel are best at handling numbers. There are many occasions, however, when a table of values needs to be included in a document containing text. Microsoft Word has several methods for incorporating such tables in documents. In this article, you will see how to do arithmetic within Word Tables. In a later article, you will see how to embed and/or link Excel tables into a Word document.

When your arithmetic needs are basic, create a Word table and include the formulas and functions using Word table syntax. For example, Figure 1 shows a Word table that checks monthly expenses. The first column (column A) lists each budget item; the second column (column B), the amount budgeted for that item; and, the third column (column C), the amount actually spent.

Figure 1

Rather than using a calculator to determine the total amount budgeted and the total actual amount spent, the AutoSum feature completes the math in one click. AutoSum adds the values in the cells above or to the left of the cell in which it is entered. The Sum icon (the algebraic symbol is the Greek letter, Sigma pictured in Figure 2) is on the Tables and Borders toolbar (click View, Toolbars, Tables and Borders if it is not checked).

Figure 2

Click the Total row in the Budgeted column (cell B10), and then click AutoSum. Almost immediately, the total appears in the cell. Clicking in the bottom of the Actual column (C10) followed by the AutoSum icon displays its total, as well. Although the totals look as though you typed them in, they actually contain the Sum function.

A function is a built-in formula, or algebraic procedure, that uses values (called arguments) to determine a result. In the case of the functions created by the AutoSum feature, the SUM function was entered, with the special value ABOVE as its argument. To illustrate this function, Figure 3 shows that when you click on the total, the number (935) turns gray, indicating that it is a calculation, rather than an entered amount. In the next column, right-clicking the value displays a context menu from which the Toggle Field Codes option was selected. The cell now shows the function enclosed within braces. The next time the Toggle Field Codes option is entered, the function will disappear and the total will reappear.

Figure 3

Other formulas use the arithmetic operators + for addition, - for subtraction, * for multiplication, / for division, ^ for powers and roots, and % for percentages. In the above Word table, the Difference column must reflect how much over or under budget the actual amount spent is. In other words, the formula Difference = Budgeted - Actual must be entered. Always begin a formula by clicking in the cell to contain the result. Clicking the Table, Formula brings up a dialog box into which you type the formula. The equal sign (=) begins the formula and is automatically placed in the Formula text box. (If the Formula text box already has a formula in it, just delete it — but keep the equal sign!)

After the equal sign, type the text b3, a minus sign (-), and finally the text c3. Figure 4 shows the entry in the Formula dialog box before clicking OK.

Figure 4

Check out Figure 5 to see examples of the other arithmetic operators.

Figure 5

Function Whenever You Can
The Sum function added up a group of cells without you having to specify each of the cells’ addresses. There are eighteen other timesaving functions available in Word tables; Figure 6 illustrates some of the more useful ones.

Figure 6

You set up a function by placing it in a formula. Rather than typing the cell addresses and operators into the Formula text box, however, you click on the Paste function drop-down arrow and select the desired function. Word inserts the function followed by a set of parentheses. You then type the arguments, which are then inserted inside the parentheses. The arguments you type in depend on the function you choose. You saw how the argument ABOVE forced the Sum function to add all of the numbers above it. In Figure 6, the Average function takes the values stored in the referenced cell addresses and displays their average. Note that the cell addresses are separated from each other by commas.

The Count function displays how many non-blank cells there are. You supply the range of cells to be checked. You indicate the cells to be checked in the following ways:

• By typing individual cell addresses separated by commas, as in C7,D12,E6

• A range of cells by typing the first cell in the range, a colon (:), and the last cell in the range, as in B3:D8

• An entire row by typing the row number, a colon, and the row number again, as in 5:5 to indicate all row 5

• An entire column by typing the column letter, a colon, and the column letter again, as in C:C

• By typing the special arguments LEFT, RIGHT, ABOVE, or BELOW

Figure 6 also gives an example of the Max and Min functions. These functions display the largest and smallest values in the cells indicated within parentheses.

Improving Appearances
The Number Format option in the Formula dialog box lets you change the appearance of values in cells. The most common formats add a dollar sign in front of a number, suppress the display of leading zeros, force digits to be displayed, insert commas to separate thousands, and put a percent sign at the end of a number. Figure 7 gives an example of each of these formats.

Figure 7

You choose a format when you enter the formula. The following list shows what the characters represent in the number format:

\$ Insert a dollar sign before the value.
# Display the digit, as long as it is not a leading zero (a zero that starts the number, such as 009).
0 Display the digit, whether it is a leading zero or not.
% Put a percent sign at the end of the number. (This changes the value of the number. Make sure you multiply the number by 100 when you use this format, since 0.25 is not the same as 0.25 percent, but rather 25 percent.)
, Separate thousands by commas.
. Include following decimal places, as in .00 to force the display of two decimal places.

Odds and Ends

• Formulas with more than one operation with or without parentheses are evaluated using the following rules:
• parentheses evaluated first, left to right, innermost to outermost
• exponentiation, left to right
• multiplication and division, left to right
• addition and subtraction, left to right
• All cell addresses in Word table formulas are absolute — that is, the address does not change when the cell is moved or copied.

• You can type a function in your document outside of the table, as long as you name the table as a bookmark. For example, =sum(mytable B:B) will display the total in column B anywhere you enter it using the Formula option on the Table menu. In the Formula dialog box, a drop-down list lets you choose from bookmarks in your document.