XL2000: Cell Ranges in Aggregate Formulas May Change Unexpectedly

• Microsoft Excel 2000

SYMPTOMS

When you add data to a worksheet that contains one or more aggregate formulas, some of these formulas may change unexpectedly.

For example, if your worksheet contains the following data

   A1: 2
   A2: 2
   A3: 2
   A4:
   A5:
   A6: =SUM(A1:A3) 

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

CAUSE

This behavior occurs when the following conditions are true:

• The Extend list formats and formulas check box is selected (on the Tools menu, click Options, and then click the Edit tab).
  
  -and-



• You are using relative cell references in the aggregate formula.
  
  -and-



• You type data into a blank cell that extends the range the aggregate formula refers to.
  
  -and-



• The aggregate formula is at the end of the range where you are typing the data.
  
  -and-



• The aggregate formula does not include the entire range of data.

WORKAROUND

To work around this behavior, use either of the following methods.

Method 1: Clear the Extend List Formats and Formulas Check Box

1. On the Tools menu, click Options.



2. On the Edit tab, click to clear the Extend list formats and formulas check box. Click OK.

Method 2: Use Absolute References in the Formulas

1. Select the cell containing the formula that has changed.



2. Press F2.



3. Select the range reference in the formula.



4. Make the appropriate row or column reference absolute.
   
   If your formula references a column of data, you might make the last row reference absolute. For example you would change =SUM(A1:A3) to =SUM(A1:A$3).
   
   If your formula references a row of data, you might make the last column reference absolute. For example, you would change =SUM(A1:C1) to =SUM(A1:$C1).



5. Press ENTER.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

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 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 an aggregate formula.
  
  -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.

Additional query words: XL2000

Keywords :
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug