Calculating averages in sorted columns

[Nathan]

Hi,

I have a spreadsheet of a survey I conducted with teachers at various
schools. Across row 3 I have Name, School, and then all of the questions. In
row 4 down, I have all of the data. In row 2 I would like to calculate
averages for all of the answers. I am sorting (through auto filter) the data
by school, and would like to enter an average formula that adjusts every time
I sort by a different school. I think this uses an IF function, but am not
sure. Any thoughts?

Thanks, Nathan.

 

[T. Valko]

You want to use the SUBTOTAL function.

Look in help for SUBTOTAL. It has many variations depending on your needs.
For example, to get an average of filtered data in the range A1:A100 -

=SUBTOTAL(1,A1:A100)

 

[Bob Phillips]

If you filter, that means some would be hidden , so use the 100+ range of
function_num

=SUBTOTAL(101,A1:A100)

 

[T. Valko]

Just so the OP isn't confused...

The 100 series arguments in SUBTOTAL are only available in versions of Excel
starting in Excel 2003. The 100 series will work on both filtered data *and*
data that is hidden as the result of manually hiding rows of data or hiding
rows through the use of macros.

=SUBTOTAL(1,A1:A100) works on *filtered* data only

=SUBTOTAL(100,A1:A100) works on both filtered data and data hidden manually

 

[Mike H]

Biff,

This is GREAT for the functions that SUBTOTAL supports. Is there anything
similar for COUNTIF (and other non math based functions) in filtered data?

Thanks!
Mike