| 0 |
Subject: Filters and Averages in Excel
Posted by: Razor
- [4833729] Thu, Jul 22, 2004, 16:40
In Excel, how can I get an average to compute if I have a filter on? Let's say I have 50 rows of data with 5 columns. How do I get Excel to compute an average or other stats after I have turned a filter on so that only 15 rows are showing? Thanks. |
| 1 | J
Leader
ID: 49346417
Sat, Jul 31, 2004, 19:42
|
you can highlight the cells you want to mess with and in the bottom of the screen it will give you an average or a sum of only the filtered data.
|
| 2 | Richard Cullip
ID: 35913237
Fri, Aug 06, 2004, 11:32
|
Try using the subtotal command. Here's a cut&paste from Excel's Help on this function:
SUBTOTAL
Returns a subtotal in a list or database.
Syntax
SUBTOTAL(function_num,ref1,ref2,...)
Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.
Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.
Remarks
If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.
If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
Richard
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|