MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Average function excluding 0's with an array formula

Posted by Lars on July 31, 2001 2:09 PM

I have a filtered list..and at the bottom of the list I want a column averaged. Using average(if{(t15:t614<>0.t15Lt614))} to exclude 0's from the average. The problem is when I filter and the data shows only a part of all the data the formula doesn't change to reflect only the data I have selected because it is an array formula. How can I fix this?


Posted by Mark W. on July 31, 2001 2:15 PM

Use =SUBTOTAL(1,T15:T614)

...and filter the 0's too!

Posted by Lars on July 31, 2001 2:47 PM

I'm using a criteria range and it won't allow me to filter ="FLL-FLL" and =<> at the same time? Is this possible?


Posted by Mark W. on July 31, 2001 3:21 PM

> Is this possible?

Yes. OR-ing is accomplished by adding rows to
the criteria range. AND-ing is accomplished by
adding columns. For example, suppose your database
range, A1:A6, contained...


and your criteria range, D1:E2, contained...


Once filtered the 5 and 0 won't be displayed,
and =SUBTOTAL(1,A2:A6) equals 2.66666666666667.