Compute an Average by category

[glen.e.mettler]

Here is my formula
=IF(ISERROR(AVERAGE(IF(F5:F112<>0,F5:F112,""))),0,AVERAGE(IF(F5:F112<>0,F5:F112)))

It calculates an average that is not 0 and not an error - works just
fine.
Each of the rows is also a member of a category
ECP, ROM, REA etc

Is there a way I can calculate the average of each of them separately
by setting a filter?

Thus, if I filtered on ECP, then the average calculated would be only
for ECP.

Is that possible without a macro?

Glen

 

[Peo Sjoblom]

You can use subtotal function and a filter

=SUBTOTAL(1,range)


will average visible rows

 

[glen.e.mettler]

That will average visible rows but it includes the 0 and empty cells in
the calculation.
How can I get it to ignore 0 and empty cells?

Glen

 

[Peo Sjoblom]

Why don't you filter that column as well, use custom and not equal to 0,
that will hide zeros
blanks are never included unless you use arrays and this wouldn't be that


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com