Average Ranges of Cells, Return Separate Value for Each Range

[Eric H]

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

 

[Glenn]

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((LIST>5)*(OFFSET(LIST,-1,0)<=5)*ROW(LIST),
SUM(--(((LIST>5)*(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((LIST>5)*(OFFSET(LIST,1,0)<=5)*ROW(LIST),
SUM(--(((LIST>5)*(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))&":"&ADDRESS(C2,COLUMN(LIST))))

B2 and C2 are both array formulas, so commit with CTRL+SHIFT+ENTER. Then copy
B22 down until errors are returned.

 

[Ashish Mathur]

Hi,

Question unclear. How do you identify the 3 ranges

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Group consecutive criteria.
http://www.mediafire.com/file/znukedjzikm/10_14_09.xlsx

 

[Eric H]

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

 

[Glenn]

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.