Excel 2007 SUBTOTAL anomaly when filtering

[Tyro]

In Excel 2007, I have discovered a SUBTOTAL function anomaly when filtering
records. I have headings in row 2 and data in rows 3 through 27 with costs
in dollars in column K. In K2 I enter the formula =SUBTOTAL(1,G3:G27) which
produces the answer $15.76. There are records for March and May in the
data. When I filter for March, the SUBTOTAL function in K2 produces the
answer $15.82 which is the average of the filtered records. After filtering,
I add another identical formula in K3: =SUBTOTAL(1,G3:G27). This formula
also produces the answer $15.82. I remove the filter. The formulas in K2 and
K3 now show $15.76. Now I filter on column E, Center, for unique records
and 6 unique records appear. The formula in K2 still shows $15.76 as does
the formula in K3. I select K3 and press F2 then enter. The formula in K3
now shows $16.22, the average of the 6 filtered records but the identical
formula in K2 shows the average of all 25 records, $15.76. When filtering on
dates, both formulas, one entered before filtering and one after filtering,
show the same result, but when filtering on unique values they show
different results. This appears to happen only when filtering for unique
records. I have uploaded the workbook to savefile.com in Excel 97-2003
format. The file is at: http://www.savefile.com/files/1356754 Any
ideas?

Tyro

 

[Tyro]

Correction: The costs are in column G

Tyro

 

[Tyro]

New information!!:

When filtering for unique records. I when press F9 (Calculate all formulas
in all open workbooks) or Shift + F9 (Calculate formulas in the active
worksheet), the formula in K2 shows $15.76 and the formula in K3 shows
$16.22. I press either Ctrl+Alt+F9 (Force complete recalculation of all
formulas) or Ctrl+Alt+Shift+F9 (Re-evaluate all formula dependencies and
force a complete recalculation), the formula in K2 shows $16.22 as does the
formula in K3.

Tyro

 

[Roger Govier]

Hi

The outcome is exactly the same in XL2003.
The only way to select Unique records from your set of data, is to use
Advance Filter, with the source range being E3:E28, not the whole data set
of B2:G28
This does select just 6 rows.
However, at this point in time, the Filter is applied to column E only.
the formula =SUBTOTAL(3,E4:E28) shows a correct result of 6

The Subtotal formula calculation is applied to column G, which does not have
a filter applied at this point so it's visible range of cells includes the
whole data set from column G.
Carrying out a full recalculation of dependencies does refresh the range of
visible cells, and the Subtotal calculation then reflects this.

I wouldn't call this a bug.
It certainly is not specific to XL2007.
I have not tested it in all versions, but XL97 shows the same behaviour so I
imagine the behaviour has been the same in all versions of Excel.