Perform Calculations with Formula Fields





Perform Calculations with Formula Fields

figs/moderate.gif figs/hack72.gif

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 Ctrl-F9 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.

Arithmetic operators for formula fields

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)

Comparison operators for formula fields

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.

Functions for formula fields

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.


Functions shown with empty parentheses can accept any number of arguments separated by commas (,) or semicolons (;). Arguments can be numbers, other formulas, or bookmark names.


4 Formula Field Logical Functions

The logical functions that formula fields can use are listed in Figure.

Logical functions for formula fields

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=5-2)} 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=5-2)} 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=5-2),2=5-3)}, which returns 1, can just as readily be expressed as {=(5=2+3)*(3=5-2)*(2=5-3)}, which also returns 1 and avoids the AND function's limitations.

  • The logical function {=OR(OR(5=2+3,3=5-2),2=5-3)}, which returns 1, can just as readily be expressed as {=((5=2+3)+(3=5-2)+(2=5-3)>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=5-2)+(2=5-3)=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 Ctrl-F9 (or select InsertField) and then enter the text.

Put the following table in a document to see cell referencing in action

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.

Cell references in Word, unlike those in Excel, are always absolute and are not shown with dollar signs. For example, referring to a cell as A1 in Word is the same as referring to a cell as $A$1 in Excel.


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.

Table reference operators

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.

If you use this form of referencing within the row or column being referenced, your formula will include a circular reference to itself in the evaluation, which will cause arithmetic errors that will increase every time the field updates.


  • Use a range that identifies specific cells or ranges of cells. For example, for a four-row 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 Ctrl-F9 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.

If you use a number as the last character in a bookmark's name, make sure the name includes at least three text characters before the number. Otherwise, Word might interpret the bookmark name as a cell reference.


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


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows