XL2000: Formulas Not Adjusted Automatically When You Enter New Data (257451)


The information in this article applies to:

  • Microsoft Excel 2000 Service Release 1 (SR-1)
This article was previously published under Q257451

SYMPTOMS

When you add data to a worksheet that contains one or more aggregate formulas, the formulas do not automatically adjust to include the new data that you added to the worksheet. This behavior occurs even though the Extend list formats and formulas check box on the Edit tab of the Options dialog box is selected.

CAUSE

In Microsoft Excel 2000 Service Release 1 (SR-1), Excel changed the rules that it uses to determine when to modify a formula based on data that you type in a worksheet. See the "More Information" section of this article for a description of the new rules.

MORE INFORMATION

If you select the Extend list formats and formulas check box, Excel automatically formats new items that you add to the end of a list to match the format of the rest of the list. To be extended, formats must appear in at least three of the rows or columns preceding the new row or column. Excel also copies formulas that are repeated in every row or column. To be extended, formulas must also appear in at least four of the rows or columns preceding the new row or column.

As well as extending list entries, Excel may also automatically adjust an aggregate formula that refers to a list. This occurs when you extend a list that has an aggregate formula at the end of a data range (to the right of a row, or at the bottom of a column of data). When you add new data to a range that an aggregate formula refers to, Excel assumes that you also intend to aggregate the new data, and adjusts the formula.

To determine whether it should adjust a formula, Excel checks if the following constraints are true:
  • You are using one of the following functions:

    • AVERAGE
    • COUNT
    • COUNTA
    • MAX
    • MIN
    • PRODUCT
    • STDEV
    • STDEVP
    • SUBTOTAL
    • SUM
    • VAR
    • VARP


    -and-

  • The formula contains a reference to only one cell or one range of cells.

    -and-

  • You are not using absolute references.

    -and-

  • The new data that you are adding is consistent with data already to the left or above the aggregate formula.

Example of the Behavior

The following steps illustrate one scenario where Excel 2000 SR-1 does not adjust a formula that the original release of Excel 2000 would adjust:
  1. Type the following data into a new worksheet:
             A1: 1  B1: 2 C1: 3  D1:    E1: =SUM(A1,B1,C1)
  2. Type 4 in cell D1 and then press ENTER.
The formula in cell E1 does not adjust to include the data that you typed in cell D1. Because the formula refers to multiple cell ranges, Excel did not adjust the formula when you typed a new value in cell D1.

REFERENCES

For additional information about the rules that the original release of Excel 2000 uses to automatically adjust formulas, click the article number below to view the article in the Microsoft Knowledge Base:

238482 XL2000: Cell Ranges in Aggregate Formulas May Change Unexpectedly

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbprb KB257451
©2002 Microsoft Corporation. All rights reserved.