Subtotal Average excluding zero

[M.Siler]

I found how to do an average excluding values that are zero
For example: =AVERAGE(IF(A1:A10=0,"",A1:A10))
but I'm not sure how to do this when I need to use subtotal because the
sheet has the autofilter. I use =subtotal(9,A1:A10) to get a subtotal that
changes when the filter changes. And I know there is an average =subtotal(1,
A1:A10), but how do I do a subtotal average excluding zeros?

I'm using excel 2007

Thanks,
Mark

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW($A$1:$A$10)-ROW($A$1),,1)),--($A$1:$A$10<>0))/
SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$1:$A$10)-ROW($A$1),,1)),--($A$1:$A$10<>0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[M.Siler]

I'll give that a try... Hey Microsoft, how about a =subtotal(12, A1:A10)
whereby 12 is average that exclused zeros.

Thanks Bob

 

[Bob Phillips]

and 13 excludes 1, 14 2, 15 > 1, etc.? See the problem I am getting at?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[M.Siler]

I see what you are saying, but zero is something special. Zero, blank,
null, nothing... when you have that you would very much like to excluse
that. But you could expand on what you were saying and have 12 be, exclude
whatever you include in an array. This would do Zero and then whatever else
you wanted.

 

[Dave Peterson]

Another option would be to insert a helper column with a formula like:

=if(a2=0,"",a2)
and drag down

Then you use the builtin =subtotal(1,a2:a999) to get the average.

And you could modify that formula to excluded whatever you wanted.

 

[Harlan Grove]

I see what you are saying, but zero is something special. Zero,
blank, null, nothing... when you have that you would very much
like to excluse that. . . .

Well, you would.

Zero is *NOT* something special. It's the integer between -1 and 1. In
general, if your averages would include both positive and negative
numbers, they should include zeros. If your averages should include
only positive or negative numbers, then the condition is >0 or <0. But
from a mathematical perspective, excluding the condition =0 is always
suspicious and likely wrong.

If you want to skip cells in descriptive statistics, make them "".

. . . But you could expand on what you were saying and have 12
be, exclude whatever you include in an array. This would do Zero
and then whatever else you wanted.

....

Or just use the formula Bob already gave you. The capability already
exists.

What Excel DOES really need rather than a rococo SUBTOTAL function is
a function (possibly named ISFILTERED) taking a single area range
argument and returning an array of the same size an shape as the range
argument with 1 entries if the corresponding cell is in a visible row
and 0 entries if it's not. Then you could dispense with SUBTOTAL and
use formulas like the array formula

=AVERAGE(IF(ISFILTERED(Rng)*(Rng>0),Rng))

This would allow people to perform calculations on filtered data
beyond the few functions SUBTOTAL provides, e.g., calculating the
MEDIAN or SUMXMY2 of filtered data. This is essentially what Bob's
formula does, but it'd be nice not to have to use volatile OFFSET
calls to accomplish this.

 

[M.Siler]

Thanks Dave... I like this solution.

Another option would be to insert a helper column with a formula like:

=if(a2=0,"",a2)
and drag down

Then you use the builtin =subtotal(1,a2:a999) to get the average.

And you could modify that formula to excluded whatever you wanted.

 

[M.Siler]

How about adding AVERAGEIF to SUBTOTAL.

AVERAGEIF work perfectly until I apply a filter.

 

[orlando herrera dizon jr.]

I found a solution that works:

=IFERROR(IF(D96>0,D96,"")+IF(E96>0,E96,""),"")

or just use IFERROR function to try eliminate the zeros.

Cheers!