what's the closest think to an "average if" function?

[Flutie99]

I have some data that I like to select and deselect using 1 or 0,
respectively. I'd like to look at the average of the values of the numbers I
select using 1. Essentially, I am looking for an "averageif" function.

[Don Guillett]

try something like this. This is an array formula which must be entered
using ctrl+shift+enter.
=average(if(a2:a200=1,b2:b200))

--
Don Guillett
SalesAid Software
[email protected]
"Flutie99" <[email protected]> wrote in message
news:[email protected]...
> I have some data that I like to select and deselect using 1 or 0,
> respectively. I'd like to look at the average of the values of the
numbers I
> select using 1. Essentially, I am looking for an "averageif" function.

[Arvi Laanemets]

Hi

P.e. with data you want to calculate the average of in range A2:A100, enter
into some cell not on same row with your data (p.e. you can use additional
rows in table header for it) the formula:
=SUBTOTAL(1,A2:A100)
Now use autofilter to filter all check values equal to 1, and you'll get the
average of filtered data returned by formula.


Arvi Laanemets



"Flutie99" <[email protected]> wrote in message
news:[email protected]...
> I have some data that I like to select and deselect using 1 or 0,
> respectively. I'd like to look at the average of the values of the
numbers I
> select using 1. Essentially, I am looking for an "averageif" function.