WD: Setting Up a Table as a Spreadsheet in Word 1.0 and 2.0 |
Q119890
The information in this article applies to:
-
Microsoft Word for Windows, versions 1.0, 1.1, 1.1a, 2.0, 2.0a, 2.0a-CD, 2.0b, 2.0c
SUMMARY
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.
Referencing Cells
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 |
---------------------
Adding Cells
Add a number to a cell:
{=sum([r1c1]) + 3 } = 5
Add two adjacent cells:
{=sum([r1c1:r2c1])} = 9
{=sum([r1c1]) + sum([r2c1])} = 9
Add two nonadjacent cells:
{=sum([r1c1],[r3c1])} = 10
Add a range of cells:
{=sum([c2])} = 9
{=sum([r1c2:r3c2])} = 9
Subtracting Cells
Subtract a number from a cell:
{=sum([r1c3]) - 3 } = 1
Subtract two cells:
{=sum([r3c1]) - sum ([r2c2])} = 5
Multiplying Cells
Multiply a number by a cell:
{=sum([r1c1])*3 } = 6
Multiply two adjacent cells:
{=product([r1c1:r2c1])} = 14
Multiply two non-adjacent cells:
{=product([r1c1],[r3c1])} = 16
{=sum([r1c1])*sum([r3c1])} = 16
Multiply a range of cells:
{=product([c2])} = 15
{=product([r1c2:r3c2])} = 15
Dividing Cells
Divide a number by a cell:
{=sum([r1c1])/3} = .67
Divide two cells:
{=sum([r2c3])/sum([r1c1])} = 3
Notes on Referring to Cells
Syntax
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.
Referencing Cell Ranges
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].
Referencing Noncontiguous Cells
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
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])}
The letter "r" without a number next to it indicates that the current row
is to be used. The "c-4" indicates the cell that is four columns to the
left.
Relative references and absolute references (references to a specific
cells) can be combined.
References to Avoid
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.
Using bookmarks
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}
MORE INFORMATION
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
Q110656 Calculation in Form Field Shows Wrong Result
Q157213 Incorrect Value Referencing Table Cell
REFERENCES
"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
Additional query words:
calculate calculation table math winword2 winword 1.0 1.10a 2.0 how classicword