ID: Q119890
The information in this article applies to:
In Word for Windows, you can create simple spreadsheets using tables and the EXPRESSION (=) field. In these spreadsheets, you can add, subtract, multiply, and divide cells.
For more information on exact syntax, see the "Syntax" section below.
Individual cells are referenced by row and column number in the form of [RnCn], where R represents row, C represents column, and n represents the actual position starting from the upper-left cell.
For example, [R1C2] refers to the cell located in the first row in the second column. An entire row may be referenced by Rn without a column reference. For example, [R1] represents row 1. Columns are referenced in a similar manner.
Examples below refer to the following table and can be placed in any blank cell:
---------------------
| 2 | 5 | 4 |
---------------------
| 7 | 3 | 6 |
---------------------
| 8 | 1 | 0 |
---------------------
Add a number to a cell:
{=sum([r1c1]) + 3 } = 5
{=sum([r1c1:r2c1])} = 9
{=sum([r1c1]) + sum([r2c1])} = 9
{=sum([r1c1],[r3c1])} = 10
{=sum([c2])} = 9
{=sum([r1c2:r3c2])} = 9
Subtract a number from a cell:
{=sum([r1c3]) - 3 } = 1
{=sum([r3c1]) - sum ([r2c2])} = 5
Multiply a number by a cell:
{=sum([r1c1])*3 } = 6
{=product([r1c1:r2c1])} = 14
{=product([r1c1],[r3c1])} = 16
{=sum([r1c1])*sum([r3c1])} = 16
{=product([c2])} = 15
{=product([r1c2:r3c2])} = 15
Divide a number by a cell:
{=sum([r1c1])/3} = .67
{=sum([r2c3])/sum([r1c1])} = 3
In all cases, when referring to individual cells, columns, or rows, the actual cell reference(s) must be enclosed in brackets, and those brackets must be enclosed in parentheses. The only time a cell reference does not have to be in brackets is when it is in a range, as in {=sum([r1c1:r4c4])}.
Cells must always be referred to by the function SUM or PRODUCT. For example, {=([r2c2] + [r2c3])} does not work properly, but {=SUM([r2c2]) + SUM([r2c3])} does work. The only time you don't have to preface a cell reference with the function SUM or PRODUCT is if you substitute one of the following functions: MIN, MAX, COUNT, or AVERAGE.
If cells are in a contiguous block (they cover a rectangular area), you can shorten the reference to them. For instance, the 8-cell area from r1c1 to r2c4 can be referenced as [r1c1:r2c4].
Adding or Multiplying
---------------------
You can refer to each cell individually, or you can separate the
references themselves with commas. Using commas is only available for
the SUM and PRODUCT function.
{=sum([r1c1]) + sum([r2c3]) + sum([r3c2])}
is the same as
{=sum([r1c1],[r2c3],[r3c2])}
{=product([r1c1]) * product([r2c3]) * product([r3c2])}
is the same as
{=product([r1c1],[r2c3],[r3c2])}
Subtracting or Dividing
-----------------------
You must refer to each cell separately; commas cannot be used:
{=sum([r1c1]) - sum([r2c3]) - sum([r3c2])}
{=sum([r1c1]) / sum([r2c3]) / sum([r3c2])}
Relative references are useful when you want a generic formula that doesn't refer to specific cells. You can add and delete rows and maintain a correct result. They are also useful when you don't want to retype a formula. For instance, if you want each cell in the fifth column to add the numbers of the first four columns in the same row, you can use a relative reference. In this example, the field would look like:
{=sum([rc-4:rc-1])}
Relative references and absolute references (references to a specific cells) can be combined.
If you want to refer to an entire row or column, be sure to specify the row or column number and place the expression field in a different row or column. If you refer to an entire row or column and place the field within that range, Word will include the field result in the calculation. Each time the calculation is updated, it will include itself in the result, producing a different result, even if none of the other numbers in the row or column have changed. This will produce unreliable results.
Examples of this type of field to avoid
---------------------------------------
{=sum([c])} adds the entire column, including the result. Each time you
update this field, the result will be the sum of the column plus the
field's previous total. Instead, use {=sum([cn])}, where n is the column
number, and place the field in a different column.
{=sum([r])} adds the entire row, including the result. Each time you
update this field, the result will be the sum of the row plus the
field's previous total. Instead, use {=sum([rn])}, where n is the row
number, and place the field in a different row.
You can apply a bookmark to the contents of any cell or field and use that bookmark in a calculation. This can often simplify the construction of other fields and can save typing.
For example, if you apply a bookmark called "quantity_sold" to the contents of cell r3c1, you can refer to it in another cell.
{=quantity_sold*.10}
{=quantity_sold*sum([r2c2])}
{=quantity_sold*commission}
For more information, please see the following article(s) in the Microsoft Knowledge Base:
ARTICLE-ID: Q110656
TITLE : Calculation in Form Field Shows Wrong Result
ARTICLE-ID: Q157213
TITLE : Incorrect Value Referencing Table Cell
ARTICLE-ID: Q95618
TITLE : WinWord Err Msg: '! Zero Divide' when Using Expression Field
"Microsoft Word for Windows User's Reference," version 1.x, pages 26-27, 364
"Microsoft Word for Windows User's Guide," version 2.x, pages 307-308, 748
"Microsoft Word User's Guide," version 6.0, pages 300-302
KBCategory: kbusage KBSubcategory: kbfield kbtable Additional reference words: calculate calculation table math winword2 winword 1.0 1.10 1.10a 2.0 2.0a 2.0a-CD 2.0b 2.0c how classicword Version : 1.0 1.10 1.10a 2.0 2.0a 2.0a
Last Reviewed: August 5, 1997