Microsoft KB Archive/119890

{|
 * width="100%"|

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

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:

<pre class="CODESAMP">     {=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:

<pre class="CODESAMP">  {=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.

<pre class="CODESAMP">  {=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"