Re: Compute an Average by category

You can use subtotal function and a filter

=SUBTOTAL(1,range)


will average visible rows


--

Regards,

Peo Sjoblom


<glen.e.mettler@xxxxxxxx> wrote in message
news:1143572898.499927.22580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: select data from a table
    ... "Peo Sjoblom" wrote: ... in one sheet and manually put the result you expect in another sheet and ... Adv filter will not do this. ... I have had help that says use advanced filter but I need to keep ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to Sort Invalid Email Addresses?
    ... Peo Sjoblom wrote in message ... Now I'm ready to bring the cows home and want to auto filter my email ...
    (microsoft.public.excel.newusers)
  • Re: Counting Unique Values
    ... OTOH you can easily get this using the subtotal function and advanced filter, assume that your sample data included headers are in A1:D8, in let's say E1 put ... > Peo Sjoblom Wrote: ... >>sumproduct will sum them to return 6, if we change the last number 4 ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to Sort Invalid Email Addresses?
    ... OK, from autofilter dropdown select custom, then does not contain @ ... Now I'm ready to bring the cows home and want to auto filter my email ... Peo Sjoblom wrote in message ...
    (microsoft.public.excel.newusers)
  • Re: Need help creating FILTER
    ... same site has instructions for programming autofilter as well ... > How do I create a dropdown list, which will generate the filter. ... > "Peo Sjoblom" wrote in message ...
    (microsoft.public.excel.worksheet.functions)