calculating in a changing range

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!

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)),E1)+1&":A"&SMALL(IF(A1:A13=0,ROW(A1:A13)),F1)-1))

No error checking in this!

Biff

"gkaste" <gkaste.1t7l30_1123106984.834@excelforum-nospam.com> wrote in
message news:gkaste.1t7l30_1123106984.834@excelforum-nospam.com...
>
> 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
>

Biff, thank you very much for the help. That worked excecllent!

I do, however, have one more situation that this dosent test for, that I would appreciate some help with. Here is some example data:

1
0 <-zero 1
1
2
3
2
1
0 <-zero 2
1
2
3
2
1
0 <- zero 3
1
2
3
2

I can use the formula above to find the max between zero 1 and 2, 2 and 3, but, this data is actually data on a circle(example: first cell is a recording at 0 degrees, and the last cell is a recording 359.9 degrees), so, is there a way to find the max between zero 3 and zero 1?

Thank you,
Garrett

Yow!

That will be extremely difficult (if at all possible) to do in a single
formula. Let me tinker around and see if I can come up with something. It
may take a separate formula to check from zp 3 to zp 1.

Biff

"gkaste" <gkaste.1tawii_1123261515.8444@excelforum-nospam.com> wrote in
message news:gkaste.1tawii_1123261515.8444@excelforum-nospam.com...
>
> Biff, thank you very much for the help. That worked excecllent!
>
> I do, however, have one more situation that this dosent test for, that
> I would appreciate some help with. Here is some example data:
>
> 1
> 0 <-zero 1
> 1
> 2
> 3
> 2
> 1
> 0 <-zero 2
> 1
> 2
> 3
> 2
> 1
> 0 <- zero 3
> 1
> 2
> 3
> 2
>
> I can use the formula above to find the max between zero 1 and 2, 2 and
> 3, but, this data is actually data on a circle(example: first cell is a
> recording at 0 degrees, and the last cell is a recording 359.9
> degrees), so, is there a way to find the max between zero 3 and zero
> 1?
>
> Thank you,
> Garrett
>
>
> --
> gkaste
> ------------------------------------------------------------------------
> gkaste's Profile:
> http://www.excelforum.com/member.php...o&userid=24525
> View this thread: http://www.excelforum.com/showthread...hreadid=392715
>

Hi!

Ok, this is a separate formula and works on the selected zero points in
reverse order: ie- 3:1.

Again, use 2 cells to hold the range of interest:

E2 = 3
F2 = 1

Array entered:

=MAX(IF(A1=0,0,OFFSET(A1,,,SMALL(IF(A1:A18=0,ROW(A1:A18)),F1)-1)),INDIRECT("A"&SMALL(IF(A1:A18=0,ROW(A1:A18)),E1)+1&":A"&MAX(IF(A1:A18<>"",ROW(A1:A18)))))

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:OebbisemFHA.1204@TK2MSFTNGP12.phx.gbl...
> Yow!
>
> That will be extremely difficult (if at all possible) to do in a single
> formula. Let me tinker around and see if I can come up with something. It
> may take a separate formula to check from zp 3 to zp 1.
>
> Biff
>
> "gkaste" <gkaste.1tawii_1123261515.8444@excelforum-nospam.com> wrote in
> message news:gkaste.1tawii_1123261515.8444@excelforum-nospam.com...
>>
>> Biff, thank you very much for the help. That worked excecllent!
>>
>> I do, however, have one more situation that this dosent test for, that
>> I would appreciate some help with. Here is some example data:
>>
>> 1
>> 0 <-zero 1
>> 1
>> 2
>> 3
>> 2
>> 1
>> 0 <-zero 2
>> 1
>> 2
>> 3
>> 2
>> 1
>> 0 <- zero 3
>> 1
>> 2
>> 3
>> 2
>>
>> I can use the formula above to find the max between zero 1 and 2, 2 and
>> 3, but, this data is actually data on a circle(example: first cell is a
>> recording at 0 degrees, and the last cell is a recording 359.9
>> degrees), so, is there a way to find the max between zero 3 and zero
>> 1?
>>
>> Thank you,
>> Garrett
>>
>>
>> --
>> gkaste
>> ------------------------------------------------------------------------
>> gkaste's Profile:
>> http://www.excelforum.com/member.php...o&userid=24525
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=392715
>>
>
>

Thanks Biff! I have everything working now. The world may be at peace again. Thanks for all the help.