Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
I need to average ranges of cells within a row or column that fit a specific
criteria. The hard part is that I need to return a separate value for each averaged range. For instance, the column below has 3 separate but continuous ranges within it of cells greater than 5. I need something that will identify each of these ranges and then average them separately, returning 3 values that would fill 3 cells. Is there a way to do this without a programming loop, using only worksheet functions? 1 1 1 0 6 7 6 8 9 1 2 3 4 8 9 10 1 2 0 0 7 9 8 11 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
Eric H wrote:
I need to average ranges of cells within a row or column that fit a specific criteria. The hard part is that I need to return a separate value for each averaged range. For instance, the column below has 3 separate but continuous ranges within it of cells greater than 5. I need something that will identify each of these ranges and then average them separately, returning 3 values that would fill 3 cells. Is there a way to do this without a programming loop, using only worksheet functions? 1 1 1 0 6 7 6 8 9 1 2 3 4 8 9 10 1 2 0 0 7 9 8 11 Assuming your data starts in A2. Name your data "LIST". Enter the following: B2=LARGE((LIST5)*(OFFSET(LIST,-1,0)<=5)*ROW(LIST), SUM(--(((LIST5)*(OFFSET(LIST,-1,0)<=5)*ROW(LIST))0))-ROW(1:1)+1) This is the row number of the start of the range. C2=LARGE((LIST5)*(OFFSET(LIST,1,0)<=5)*ROW(LIST), SUM(--(((LIST5)*(OFFSET(LIST,1,0)<=5)*ROW(LIST))0))-ROW(1:1)+1) This is the row number of the end of the range. D2=AVERAGE(INDIRECT(ADDRESS(B2,COLUMN(LIST))&":"&A DDRESS(C2,COLUMN(LIST)))) B2 and C2 are both array formulas, so commit with CTRL+SHIFT+ENTER. Then copy B2:D2 down until errors are returned. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
Hi,
Question unclear. How do you identify the 3 ranges -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Eric H" wrote in message ... I need to average ranges of cells within a row or column that fit a specific criteria. The hard part is that I need to return a separate value for each averaged range. For instance, the column below has 3 separate but continuous ranges within it of cells greater than 5. I need something that will identify each of these ranges and then average them separately, returning 3 values that would fill 3 cells. Is there a way to do this without a programming loop, using only worksheet functions? 1 1 1 0 6 7 6 8 9 1 2 3 4 8 9 10 1 2 0 0 7 9 8 11 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
Excel 2007 PivotTable
Group consecutive criteria. http://www.mediafire.com/file/znukedjzikm/10_14_09.xlsx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
Thanks. This works, and was a good intro into some more advanced Excel
programming. I have two questions. 1) Why the need to double negate the argument in SUM? I see it doesn't work without it, but why? 2) I'm new to these communities and curious how they work. How did you find and answer my question so fast. Are you a random user that just happened upon my question and had time to answer it. Or do you work/volunteer/etc. in the community to watch the posts at certain times and answer as many as you can? Or something else? Eric "Eric H" wrote: I need to average ranges of cells within a row or column that fit a specific criteria. The hard part is that I need to return a separate value for each averaged range. For instance, the column below has 3 separate but continuous ranges within it of cells greater than 5. I need something that will identify each of these ranges and then average them separately, returning 3 values that would fill 3 cells. Is there a way to do this without a programming loop, using only worksheet functions? 1 1 1 0 6 7 6 8 9 1 2 3 4 8 9 10 1 2 0 0 7 9 8 11 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Ranges of Cells, Return Separate Value for Each Range
I assume you meant this for me. Hard to tell, because you replied to your own
post instead of my response. 1) http://www.mcgimpsey.com/excel/formulae/doubleneg.html 2) I am a "random user" that follows this group when I have time and posts when I think I have something to add. There are many "MVP's" who answer the majority of the posts, but I don't believe anyone is paid to do it. Eric H wrote: Thanks. This works, and was a good intro into some more advanced Excel programming. I have two questions. 1) Why the need to double negate the argument in SUM? I see it doesn't work without it, but why? 2) I'm new to these communities and curious how they work. How did you find and answer my question so fast. Are you a random user that just happened upon my question and had time to answer it. Or do you work/volunteer/etc. in the community to watch the posts at certain times and answer as many as you can? Or something else? Eric "Eric H" wrote: I need to average ranges of cells within a row or column that fit a specific criteria. The hard part is that I need to return a separate value for each averaged range. For instance, the column below has 3 separate but continuous ranges within it of cells greater than 5. I need something that will identify each of these ranges and then average them separately, returning 3 values that would fill 3 cells. Is there a way to do this without a programming loop, using only worksheet functions? 1 1 1 0 6 7 6 8 9 1 2 3 4 8 9 10 1 2 0 0 7 9 8 11 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating carriage return data into separate cells | Excel Discussion (Misc queries) | |||
Calculate Average with 'x' in Range of Cells | Excel Worksheet Functions | |||
Averaging cells in separate workbooks to make year-to-date average | Excel Discussion (Misc queries) | |||
average range of cells with zero and blanks | Excel Discussion (Misc queries) | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions |