XL2000: Cell Ranges in Aggregate Formulas May Change Unexpectedly

View products that this article applies to.

This article was previously published under Q238482

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)

and you type 2 into cell A4, the formula in cell A6 changes to =SUM(A1:A4).

NOTE: An aggregate formula is a summed series of three or more adjacent cells, such as =A1+A2+A3, or a formula containing one or more of the following aggregate functions:

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

Back to the top

CAUSE

This problem 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.

Back to the top

WORKAROUND

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

Back to the top

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

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

Back to the top

Method 2: Use Absolute References in the Formulas

Select the cell containing the formula that has changed.
Press F2.
Select the range reference in the formula.
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).
Press ENTER.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

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 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.

Back to the top