Word Hacks [Electronic resources] نسخه متنی

Hack 72 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
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).

8.4.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 Table 8-1 with numeric values.

+

-

*

/

%

^

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)}

8.4.2 Formula Field Comparison Operators

You can compare two numeric values with any of the
operators listed in Table 8-2. The result of such
a comparison is a logical value, either TRUE
(1) or FALSE
(0)

=

<>

<

<=

>

>=

For example, to test whether two values are equal, use a field coded
like the following:

{=3=2+1} or {=NOT(3<>2+1)}

8.4.3 Formula Field Functions

A formula field can use values returned by any of the functions
listed in Table 8-3.

ABS(x)

AVERAGE( )

COUNT( )

DEFINED(x)

FALSE

INT(x)

MIN( )

MAX( )

MOD(x,y)

PRODUCT( )

ROUND(x,y)

SIGN(x)

SUM( )

TRUE

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.

8.4.4 Formula Field Logical Functions

The logical functions that formula fields can use are
listed in Table 8-4.

AND(x,y)

OR(x,y)

NOT(x)

IF(x,y,z)

8.4.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.

8.4.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.

8.4.5 Referencing Data in a Table

As in Excel, in Word
you can reference table cells for
use in a formula.

8.4.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 Table 8-5. Remember, to create the field
braces, press Ctrl-F9 (or select InsertField) and then
enter the text.

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.

8.4.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 Table 8-6.

=SUM(A1:A5)

=SUM(A1:A5,A10:A15,A20)

8.4.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.

8.4.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.

8.4.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