Filtering Data With Formula

[Guest]

In Column D I have about 100 Cells filled with numbers. Some are greater to
or equal to zero, some are less than zero. I'm wondering if there are
separate formulas for each of the following:

1) Average of all data greater than or equal to 0.
2) Average of all data less than 0.
3) Count of data greater than or equal to 0.
4) Count of all data less than 0.

I can do this with auto filter, but that is time consuming. Any formulas?
Thanks very much.

 

[Guest]

Try something like this:

With a list of values in A1:A100, with the col heading in A1

C1: Avg >=0
D1: =SUMIF(A2:A100,">=0",A2:A100)/COUNTIF(A2:A100,">=0")
or =AVERAGE(IF(A2:A100>=0,A2:A100)) confirmed with [Ctrl][Shift][Enter]

C2: Avg<0
D2: =SUMIF(A2:A100,"<0",A2:A100)/COUNTIF(A2:A100,"<0")
or =AVERAGE(IF(A2:A100<0,A2:A100)) confirmed with [Ctrl][Shift][Enter]

C3: Cnt>=0
D3: =COUNTIF(A2:A100,">=0")

C4: Cnt<0
D4: =COUNTIF(A2:A100,"<0")


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Works great! Thanks very much.