Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL2000: Formulas Not Adjusted Automatically When You Enter New Data


View products that this article applies to.

This article was previously published under Q257451

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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

↑ Back to the top


Keywords: KB257451, kbprb, kbfix

↑ Back to the top

Article Info
Article ID : 257451
Revision : 2
Created on : 1/27/2007
Published on : 1/27/2007
Exists online : False
Views : 458