calculating in a changing range

[gkaste]

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!

 

[Biff]

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]

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

 

[Biff]

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

 

[Biff]

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

 

[gkaste]

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