array based MAX func

[cogen]

I have the following data in the table below, the data continues to the end of the day to 7/2/2006 0:00 but I couldn't post all of it. What I'd like to do is a MAX function that will look for the max value in column B for 0:30 to 8am and 8pm to 0:00 on the next day and return just a single max value. I tried doing an array function in column D that would look at the hour values for each hour and based on that would do the max, but only got the first piece of the puzzle right, any help would be greatly appreciated.

Book1.xls
	A	B	C	D
1	7/1/20060:30	45839.721	0	47931.066
2	7/1/20061:00	45686.338	1
3	7/1/20061:30	45369.195	1
4	7/1/20062:00	45368.564	2
5	7/1/20062:30	45347.447	2
6	7/1/20063:00	45196.195	3
7	7/1/20063:30	44844.885	3
8	7/1/20064:00	44922.355	4
9	7/1/20064:30	44800.143	4
10	7/1/20065:00	44957.953	5
11	7/1/20065:30	44784.994	5
12	7/1/20066:00	44825.023	6
13	7/1/20066:30	44775.127	6
14	7/1/20067:00	44766.795	7
15	7/1/20067:30	44870.195	7
16	7/1/20068:00	45383.115	8
17	7/1/20068:30	45341.924	8
18	7/1/20069:00	46030.713	9
19	7/1/20069:30	46599.770	9
20	7/1/200610:00	47938.781	10
21	7/1/200610:30	48576.488	10
22	7/1/200611:00	48586.834	11
23	7/1/200611:30	48899.383	11
24	7/1/200612:00	49425.951	12
25	7/1/200612:30	49121.492	12
26	7/1/200613:00	49131.764	13
27	7/1/200613:30	48998.008	13
28	7/1/200614:00	49282.037	14
29	7/1/200614:30	49231.770	14
30	7/1/200615:00	49393.326	15
Sheet1

Thanks in advanced

 

[BatmanUK]

I have named your times range as 'Times' and your values range as 'Values'. I have also assumed that the same effect can be had by assuming that you want to exclude all times between 08:30 and 19:30 rather than include all times in the two ranges either side of that. If not, the formula can be adapted to cope.

In my test, the start and end times are held in cells J2 and J3.

=MAX(--(NOT(Times>J2))+--(NOT(Times>J3))*Values) [see note below]

entered as an array formula.

Hope this helps.

NB: The Board software seems to be truncating my formula if I put the < sign in the formula. You need to replace the second > with the less than sign.

 

[barry houdini]

NB: The Board software seems to be truncating my formula if I put the < sign in the formula. You need to replace the second > with the less than sign.

sometimes the < and > signs are interpreted as HTML tags

either use the code tags so that formula looks like this

=MAX((Times>=J2)*(Times<=J3)*values)

or put spaces before and after < or > signs

 

[BatmanUK]

Thanks for the tip.

 

[cogen]

Thank you for the help, I honestly didn't think of that. Simply enough.