Eric said:

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

B2

2 down until errors are returned.