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.

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)

--
Biff
Microsoft Excel MVP


"Nathan" wrote in message
...
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.

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

=SUBTOTAL(101,A1:A100)

--
__________________________________
HTH

Bob

"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Nathan" wrote in message
...
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.

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

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
If you filter, that means some would be hidden , so use the 100+ range of
function_num

=SUBTOTAL(101,A1:A100)

--
__________________________________
HTH

Bob

"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Nathan" wrote in message
...
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.

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
If you filter, that means some would be hidden , so use the 100+ range of
function_num

=SUBTOTAL(101,A1:A100)

--
__________________________________
HTH

Bob

"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Nathan" wrote in message
...
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.