Jan. 24, 2011, 6:34 p.m.
posted by francis
Perform Calculations with Formula Fields
You can do a surprising amount of number crunching without resorting to an Excel spreadsheet. This hack introduces you to Word's formula fields.
Formula fields let you calculate and compare numbers, but not text. This hack shows you one way around this limitation.
The syntax for a formula field follows:
{=Formula [Bookmark] [\# Numeric Picture]}
For example, the following set of fields asks you to enter a number, assigns the number to a bookmark named MyNum, and then uses a formula field to calculate the square of the number:
{ QUOTE { ASK "Enter a Number" MyNum } { =MyNum^2 } }
To get the square of a different number, select the field and press F9.
To create this field, or any of the fields shown in this hack, press CtrlF9 or select InsertField for each set of braces in the field (most of the fields in this hack use several nested fields), and then fill in the field codes as shown. Line breaks are included to aid in readability, but they are not required for the fields to work properly.
The Numeric Picture includes formatting instructions that tell Word how to display the results of a calculation. These are discussed in [Hack #73] .
A formula can use any combination of numbers, bookmarked numbers, or fields that output numbers, along with any of Word's numeric operators and functions (shown in the following sections).
1 Formula Field Arithmetic Operators
To perform basic arithmetic operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, you can use any combination of the arithmetic operators listed in Figure with numeric values.
Operation 
Operator 

Addition 
+ 
Subtraction 
 
Multiplication 
* 
Division 
/ 
Percentage 
% 
Powers and roots 
^ 
For example, to add one value to another, use a field like the following:
{=2+2}
You can also combine operations. For example, to calculate a square root, use a field like the following:
{=3^(1/2)}
2 Formula Field Comparison Operators
You can compare two numeric values with any of the operators listed in Figure. The result of such a comparison is a logical value, either TRUE (1) or FALSE (0)
Operation 
Operator 

Equal to 
= 
Not equal to 
<> 
Less than 
< 
Less than or equal to 
<= 
Greater than 
> 
Greater than or equal to 
>= 
For example, to test whether two values are equal, use a field coded like the following:
{=3=2+1} or {=NOT(3<>2+1)}
3 Formula Field Functions
A formula field can use values returned by any of the functions listed in Figure.
Function 
Returns 

ABS(x) 
The positive value of a number or formula, regardless of its actual positive or negative value. For example, {=ABS(5)} and {=ABS(5)} both return 5. 
AVERAGE( ) 
The average of a list of values; e .g., {=AVERAGE(1,2,3)} returns 2. 
COUNT( ) 
The number of items in a list. For example, {=COUNT(1,2,3)} returns 3. 
DEFINED(x) 
The value 1 (true) if the expression x is valid, or the value 0 (false) if the expression can't be computed; e.g., {=DEFINED(1/0)} returns 0. 
FALSE 
The value 0. For example, {=FALSE} returns 0. 
INT(x) 
The numbers to the left of the decimal place in the value or formula x. For example, {=INT(5.15)} returns 5. 
MIN( ) 
The smallest value in a list. For example, {=MIN(1,2,3)} returns 1. 
MAX( ) 
The largest value in a list. For example, {=MAX(1,2,3)} returns 3. 
MOD(x,y) 
The remainder that results from dividing the value x by the value y a whole number of times. For example, {=MOD(5.15,2)} returns 1.15. 
PRODUCT( ) 
The result of multiplying a list of values. For example, {=PRODUCT(2,4,6,8)} returns 384. 
ROUND(x,y) 
The value of x rounded to the specified number of decimal places x,y can be either a number or the result of a formula. For example, {=ROUND(123.456,2)} returns 123.46, {=ROUND(123.456,1)} returns 123.5, {=ROUND(123.456,0)} returns 123, and {=ROUND(123.456,1)} returns 120. 
SIGN(x) 
The value 1 if x is a positive value, or the value 1 if x is a negative value. For example, {=SIGN(123)} returns 1, and {=SIGN(123)} returns 1. 
SUM( ) 
The sum of a list of values or formulas. For example, {=SUM(1,2,3)} returns 6. 
TRUE 
The value 1. For example, {=TRUE} returns 1. 

4 Formula Field Logical Functions
The logical functions that formula fields can use are listed in Figure.
Function 
Returns 

AND(x,y) 
The value 1 (true) if the logical expressions x and y are both true, and the value 0 (zero, false) if either expression is false. For example, {=AND(5=2+3,3=52)} returns 1. 
OR(x,y) 
The value 1 (true) if either or both of the logical expressions x and y are true, and the value 0 (zero, false) if both expressions are false. For example, {=OR(5=2+3,3=52)} returns 1. 
NOT(x) 
Reverses the logic of its argument. Returns the value 0 (false) if the logical expression x is true, or the value 1 (true) otherwise. For example, to test whether two values are equal, you could use {=NOT(3<>2+1)}, which is equivalent to {=(3=2+1)} and returns 1. 
IF(x,y,z) 
Specifies a logical test to perform, where x is any value or expression that can be evaluated to TRUE or FALSE, y is the value that is returned if x evaluates to TRUE, and z is the value that is returned if x evaluates to FALSE. For example, {IF(5=2+3,2*3,2/3)} returns 6 and {IF(5<>2+3,2*3,2/3)} returns 0.667. 
4.1 AND and OR: Testing multiple logical numeric expressions
Word's AND and OR functions can test only two logical numeric expressions at a time, and they can't directly test text strings at all. For testing more than two logical numeric expressions, you can nest multiple AND or OR functions, but there is a better way:
The logical function {=AND(AND(5=2+3,3=52),2=53)}, which returns 1, can just as readily be expressed as {=(5=2+3)*(3=52)*(2=53)}, which also returns 1 and avoids the AND function's limitations.
The logical function {=OR(OR(5=2+3,3=52),2=53)}, which returns 1, can just as readily be expressed as {=((5=2+3)+(3=52)+(2=53)>0)}, which also returns 1 and likewise avoids the OR function's limitations. Alternatively, to test whether only one of a range of possibilities is true (an "exclusive OR"), you can use {=((5=2+3)+(3=52)+(2=53)=1)}, replacing the final 1 with the required number of true results. The formula returns 0 here, because more than one test condition is true.
4.2 Testing or returning text strings with logical functions in bookmarks
Though you can't use formula fields directly to compare text values, you can fake it with IF fields. For example, the following set of fields asks you to enter your name. If the name you enter is Bob, the field displays "Hello, Bob." If the name you enter isn't Bob, the field displays "What have you done with Bob?"
{ QUOTE { ASK Name "What's your name?"} {IF{ Name }= "Bob" "Hello, Bob" "What have you done with Bob?"}}
To enter a different name, just select the field and press F9.
5 Referencing Data in a Table
As in Excel, in Word you can reference table cells for use in a formula.
5.1 Referencing cells containing numbers
When you use cell references in a table, you reference table cells using an alphanumeric column/row format (A1, A2, B1, B2, and so on).
For example, select TableInsert Table, choose two rows and two columns, click the OK button, and enter the values shown in Figure. Remember, to create the field braces, press CtrlF9 (or select InsertField) and then enter the text.
12 
23 
The value of the cell above is {=A1}. 
The sum of the values in the first two cells in the first row is {=A1+B1}. 
When you select the fields and press F9, the correct results will display in the bottom row. If you change the values in the first row, just update the fields again (select the fields and press F9) to see the new results.

5.2 Reference operators
You can combine ranges of cells in a table or across tables for calculations with either one or both of the reference operators listed in Figure.
Operator 
Description 
Example 

: (colon) 
Range operator. Returns all cells between and including the two reference cells. 
=SUM(A1:A5) 
, (comma) 
Union operator. Combines multiple discontinuous cell ranges in one reference. 
=SUM(A1:A5,A10:A15,A20) 
5.3 Referencing an entire row or column
You can also reference an entire row or column in a calculation:
Use a row or column range that includes only the row letter or column number. For example, use 1:1 to reference the first row in the table or A:A to reference the first column in the table. This form of referencing includes all the cells in the row or column, even if you add or delete rows or columns later.

Use a range that identifies specific cells or ranges of cells. For example, for a fourrow table, D1:D4 refers to the cells on rows one to four in column D. This form of referencing restricts the calculation to include only specific cells. If you add or delete cells later, you may need to edit the calculation.
5.4 Referencing table cells from outside the table
Only the following functions can accept references to table cells as arguments from outside that table:
AVERAGE()
COUNT()
MAX()
MIN()
PRODUCT()
SUM()
Before you can reference a cell value from outside a table, you need to create a bookmark in the table to identify it. With your cursor in the table, select InsertBookmark and give the table a name, such as Table1. You can now refer to the contents of this table in calculations elsewhere in your document.
To refer to the contents of a cell from outside a table, you always need to use one of the six functions shown above, even to get a single value. For example, if you put a bookmark named Table1 in the table you created earlier in this hack, you can reference the value in the first cell with the following field (remember, use CtrlF9 or InsertField to insert the field braces):
{=SUM {Table1 A1}}
This technique can be useful when you need to refer to one or more table values in the document's text, do math with them, or even refer to them in another table.

5.5 Referencing row and column totals from outside a table
A common use for referencing cells outside their tables is to report totals from specific columns in a table, where the last row in the table contains the totals of each column. If the number of rows might change, but the last row always contains the total, you can reference that total without needing to know the row number. Since the last row contains the total of all previous rows, if you sum the entire contents of the column and divide by two, you'll get the desired sum. For example, if you had a table bookmarked as Table1, you could use the following field to reference the total of the fourth (D) column in the table:
{=SUM{Table1 D:D)/2}
—Paul Edstein
 Comment