Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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?

Thanks
Lars

Re: Average function excluding 0's with an array formula

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

Use =SUBTOTAL(1,T15:T614)

...and filter the 0's too!

Re: Average function excluding 0's with an array formula

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?

Thanks

Re: Average function excluding 0's with an array formula

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...

{"Field1"
;5
;1
;0
;3
;4}

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

{"Field1","Field1"
;"<=4","<>0"}

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.