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