Hi!

This works on the example you posted with 2 zero points:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(INDIRECT("A"&MIN(IF(A1:A13=0,ROW(A1:A13))+1)& ":A"&MAX(IF(A1:A13=0,ROW(A1:A13))-1)))

The number of zero points and the number of cells between them will
change.

You might want a cell to hold the count of "zero points":

=COUNTIF(A:A,0)

Then use 2 cells to hold the range of zero points you want to use. For
example, you want the MAX between zero point 4 and zero point 5:

E1 = 4
F1 = 5

Array entered:

=MAX(INDIRECT("A"&SMALL(IF(A1:A13=0,ROW(A1:A13)),E 1)+1&":A"&SMALL(IF(A1:A13=0,ROW(A1:A13)),F1)-1))

No error checking in this!

Biff

"gkaste" wrote in
message ...

I am trying to find a maximum value in a range that will be changing (in
starting position, and length). Here is an example, this is cells A1 to
A13:

2
1
0
1
2
3
4
3
2
1
0
1
2

I want to find the max between zero points (In this case, there would
be one max, and it would be 4). The number of zero points and the
number of cells between them will change. Anyone have some idea of how
to go about this, or anything to even get me started?

Thank you!


--
gkaste
------------------------------------------------------------------------
gkaste's Profile:
http://www.excelforum.com/member.php...o&userid=24525
View this thread: http://www.excelforum.com/showthread...hreadid=392715