Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Data With Formula
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Data With Formula
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 "SteveC" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering Data With Formula
Works great! Thanks very much.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Updating data makes formula wrong | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Any cell containing formula seen as data instead of formula | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |