MAX help?

[ecarney14]

My simplified data set looks like this: ( the real one is much more confusing)

Time Ch. 16
1 1
2 0
3 2
4 1
5 6
6 7
7 8
8 7
9 5
10 0

I know that the max is 8, and the minimum is really 0. But what I want my macro to do. Is determine that the data goes up then comes down again before hitting the max point. Is there a way to determine that 2 is a max point, and to take the values in between 2 and 8 in order to find the minimum point, 1.

Eventually I would like to use the minimum, 1, in a formula later. As long as I can get the macro to identify it, I can figure it out from there though.

Thanks for any help.

 

[Krishnakumar]

Hi,

A formula based approach..

Book1
	A	B	C	D
2	1	1	1
3	2	0
4	3	2
5	4	1
6	5	6
7	6	7
8	7	8
9	8	7
10	9	5
11	10	0
Sheet2

Formulain C2,

=MIN(INDEX(B2:B11,MATCH(0,B2:B11,0)+1):INDEX(B2:B11,MATCH(0,INDEX(B2:B11,MATCH(0,B2:B11,0)+1):INDEX(B2:B11,MATCH(9.999999999E+307,B2:B11)),0)-1))

HTH

 

[ecarney14]

wow. how did you come up with that. i am going to check and see if it works for other data. but whz the 9.999e307?

thanks though

 

[Krishnakumar]

but whz the 9.999e307?

Largest allowed positive number