Field codes: = (Formula) field

The = (Formula) field code calculates a number by using a mathematical formula. You can use the Formula command on the Layout tab, which appears when you click inside a table.

If you have a spreadsheet application, like Excel, embedding all or part of a worksheet in a document is often easier than using the = (Formula) field.

Syntax

When you view = (Formula) field code in your document, the syntax looks like this:

{ = Formula [Bookmark ] [\#Numeric Picture ] }

Note   A field code tells the field what to show. Field results are what is shown in the document after having evaluated the field code. To toggle between viewing the field code and the field code results, press Alt+F9.

Instructions

A formula is an expression that can contain any combination of numbers, bookmarks that refer to numbers, fields resulting in numbers, and the available operators and functions. The expression can refer to values in a table and values returned by functions.

Learn about:

Operators

In an = (Formula) field, you can use any combination of values and the following mathematical and relational operators.

+

Addition

Subtraction

*

Multiplication

/

Division

%

Percentage

^

Powers and roots

=

Equal to

<

Less than

< =

Less than or equal to

>

Greater than

> =

Greater than or equal to

< >

Not equal to

Functions

The = (Formula) field can use values returned by the following functions. Functions with empty parentheses can accept any number of arguments. Arguments must be separated by the list separator defined in the regional settings in Microsoft Windows Control Panel, either a comma (,) or semicolon (;). Arguments can be numbers, formulas, or bookmark names. The functions AVERAGE(), COUNT(), MAX(), MIN(), PRODUCT(), and SUM() can also accept references to table cells as arguments.

ABS(x)

Returns the positive value of a number or formula, regardless of its actual positive or negative value.

AND(x,y)

Returns the value 1 if the logical expressions x and y are both true, or the value 0 (zero) if either expression is false.

AVERAGE( )

Returns the average of a list of values.

COUNT( )

Returns the number of items in a list.

DEFINED(x)

Returns the value 1 (true) if the expression x is valid, or the value 0 (false) if the expression cannot be computed.

FALSE

Returns 0 (zero).

INT(x)

Returns the numbers to the left of the decimal place in the value or formula x.

MIN( )

Returns the smallest value in a list.

MAX( )

Returns the largest value in a list.

MOD(x,y)

Returns the remainder that results from dividing the value x by the value y a whole number of times.

NOT(x)

Returns the value 0 (zero) (false) if the logical expression x is true, or the value 1 (true) if the expression is false.

OR(x,y)

Returns the value 1 (true) if either or both logical expressions x and y are true, or the value 0 (zero) (false) if both expressions are false.

PRODUCT( )

Returns the result of multiplying a list of values. For example, the function { = PRODUCT (1,3,7,9) } returns the value 189.

ROUND(x,y)

Returns the value of x rounded to the specified number of decimal places y; x can be either a number or the result of a formula.

SIGN(x)

Returns the value 1 if x is a positive value, or the value –1 if x is a negative value.

SUM( )

Returns the sum of a list of values or formulas.

TRUE

Returns the value 1.

Table references

When you perform calculations in a table, you reference table cells as A1, A2, B1, B2, and so on, with the letter representing a column and the number representing a row. Cell references in Microsoft Word, unlike those in Microsoft Excel, are always absolute references 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.

Table illustrating cell references

Reference individual cells

To reference cells in formulas, use a comma to separate references to individual cells and a colon to separate the first and last cells in a designated range, as shown in the following examples.

To average these cells:

Table with cell range selected

=average(b:b) or =average(b1:b3)

Table with cell range selected

=average(a1:b2)

Table with cell range selected

=average(a1:c2) or =average(1:1,2:2)

Table with cell range selected

=average(a1,a3,c2)

Reference an entire row or column

You can reference an entire row or column in a calculation in the following ways:

  • Use a range that includes only the letter or number that represents it — for example, 1:1 to reference the first row in the table. This designation allows the calculation to automatically include all the cells in the row if you decide to add other cells later.

  • Use a range that includes the specific cells — for example, a1:a3 to reference a column with three rows. This designation allows the calculation to include only those particular cells. If you add other cells later and you want the calculation to include them, you need to edit the calculation.

Reference cells in another table

To reference cells in another table, or to reference a cell from outside a table, identify the table with a bookmark. For example, the field { =average(Table2 b:b) } averages column B in the table marked by the bookmark Table2.

Bookmark

The name of a bookmark that refers to one or more values.

\# Numeric picture

Specifies the display of a numeric result. This switch is called a "picture" switch because you use symbols to represent the format of the field result.

For example, the switch \# $#,##0.00 in { = SUM(ABOVE) \# $#,##0.00 } displays a result such as "$4,455.70." If the result of a field is not a number, this switch has no effect.

Note   Quotation marks are not required around simple numeric pictures that do not include spaces — for example, { MarchSales \# $#,##0.00 }. For more complex numeric pictures and those that include text or spaces, enclose the numeric picture in quotation marks, as shown in the following picture item examples. Word adds quotation marks to numeric picture switches if you insert a field by using the Field command on the Insert tab or the Formula command (on the Layout menu that appears when you click inside a table).

Combine the following picture items to build a numeric picture switch.

Picture items

0 (zero)

Specifies the requisite numeric places to display in the result. If the result does not include a digit in that place, Word displays a 0 (zero). For example, { = 4 + 5 \# 00.00 } displays "09.00".

#

Specifies the requisite numeric places to display in the result. If the result does not include a digit in that place, Word displays a space. For example, { = 9 + 6 \# $### } displays "$ 15".

x

Drops digits to the left of the "x" placeholder. If the placeholder is to the right of the decimal point, Word rounds the result to that place. For example:
{ = 111053 + 111439 \# x## } displays "492".
{ = 1/8 \# 0.00x } displays "0.125".
{ = 3/4 \# .x } displays ".8".

. (decimal point)

Determines the decimal point position. For example, { = SUM(ABOVE) \# $###.00 } displays "$495.47".

Note   Use the decimal symbol specified as part of the regional settings in Microsoft Windows Control Panel.

, (digit grouping symbol)

Separates a series of three digits. For example, { = NetProfit \# $#,###,### } displays "$2,456,800".

Note   Use the digit grouping symbol specified as part of the regional settings in Windows Control Panel.

- (minus sign)

Adds a minus sign to a negative result, or adds a space if the result is positive or 0 (zero). For example, { = 10 - 90 \# -## } displays "-80".

+ (plus sign)

Adds a plus sign to a positive result, a minus sign to a negative result, or a space if the result is 0 (zero). For example, { = 100 - 90 \# +## } displays "+10", and { = 90 - 100 \# +## } displays "-10".

%, $, *, and so on

Includes the specified character in the result. For example, { = netprofit \# "##%" } displays "33%".

"positive; negative"

Specifies different number formats for positive and negative results. For example, if the bookmark Sales95 is a positive value, the field { Sales95 \# "$#,##0.00;-$#,##0.00" } displays the value with regular formatting — for example, "$1,245.65". A negative value is displayed with bold formatting and a minus sign — for example, "-$ 345.56".

"positive; negative; zero"

Specifies different number formats for a positive result, a negative result, and a 0 (zero) result. For example, depending on the value of the Sales95 bookmark, { Sales95 \# "$#,##0.00;($#,##0.00);$0" } displays positive, negative, and 0 (zero) values as follows: $1,245.65, ($ 345.56), $0

'text'

Adds text to the result. Enclose the text in single quotation marks. For example, { = { Price } *8.1% \# "$##0.00 'is sales tax' " } displays "$347.44 is sales tax".

`numbereditem`

Displays the number of the preceding item that you numbered by using the Caption command (Insert menu, Reference submenu) or by inserting a SEQ field. Enclose the item identifier, such as "table" or "figure," in grave accents (`). The sequential number is displayed in Arabic numerals. For example, { = SUM(A1:D4) \# "##0.00 'is the total of Table' `table`" } displays "456.34 is the total of Table 2".

Examples

Example 1

The following field subtracts from a value represented by the bookmark GrossSales. The numeric picture switch displays the results with a currency sign — for example, $14,786.17:

{ = GrossSales-29,897.62 \# "$#,##0.00" }

Example 2

The following = (Formula) field computes the total number of pages in a document whose page numbering starts at 47; it displays a result such as "Page 51 of 92":

Page { PAGE } of { = (47 - 1) + { NUMPAGES } }

Example 3

The following examples refer to values in a table. The cell references in the examples refer to cells in the table containing the = (Formula) field. If the field isn't in the referenced table, mark the table with a bookmark and specify the bookmark, followed by the cell references.

{ = SUM(ABOVE) } (inserted in a table cell)

The sum of cells, from the cell above the field to the top of the column or to the first blank cell or illegal value.

{ = SUM(Table3 C3) }

The contents of the cell in the third column of the third row of the table marked by the bookmark "Table3."

{ = MIN(Table3 A3:D3) }

Smallest value in the first four cells in the third row of the table marked by the bookmark "Table3."

Applies To: Word 2010, Word 2016 for Mac, Word 2007, Word 2013



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language