Find Max and Min Values between dates

[RickyF]

I'm trying to find the highest and lowest values between two dates.

Spreadsheet is configured with the columns Date, Time, Open, High, Low & Close. Rows contain Dates are between 1-Jan-2014 to present for each date there are 24 entries for each hours of the day. What I'm trying to find out is the highest value from the High column and the lowest value from the Low column between two dates and times.

For example, I would like to find out what is the highest and lowest values between 27/05/2014 at 06:00 to 28/05/2014 at 06:00. I've thought about using Lookup, Offset, Index and Match but from what I have read these require a range between where to search. As I'm first searching for the range before returning a value this where I'm stuck.

I'm completely stumped as to where to start with any kind of formula or VBA script that will bring me results, therefore any help is appreciated.

 

[steve the fish]

Where are you going to store 27/05/2014 etc? In some cells? Or in the formula?

 

[RickyF]

Where are you going to store 27/05/2014 etc? In some cells? Or in the formula?

Steve
The intention is to extract the data into some cells onto a seperate worksheet.

 

[Andrew Poulsom]

Welcome to MrExcel.

Example:

Excel 2010
	A	B	C	D	E	F
1	Date	Time	High	Low	Start	27/05/2014 06:00
2	25/05/2014	06:00:00	217	212	End	28/05/2014 06:00
3	25/05/2014	12:00:00	300	295	MaxHigh	286
4	25/05/2014	18:00:00	214	209	MinLow	211
5	26/05/2014	00:00:00	256	251
6	26/05/2014	06:00:00	219	214
7	26/05/2014	12:00:00	228	223
8	26/05/2014	18:00:00	295	290
9	27/05/2014	00:00:00	297	292
10	27/05/2014	06:00:00	249	244
11	27/05/2014	12:00:00	216	211
12	27/05/2014	18:00:00	222	217
13	28/05/2014	00:00:00	286	281
14	28/05/2014	06:00:00	279	274
15	28/05/2014	12:00:00	274	269
16	28/05/2014	18:00:00	228	223
17	29/05/2014	00:00:00	212	207
18	29/05/2014	06:00:00	209	204
Sheet1

Cell Formulas
Range	Formula
F3	{=MAX(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,C2:C18)))}
F4	{=MIN(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,D2:D18)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[snb_]

max:
arrayformula:


=MAX((A2:A18>F1)*(A2:A18 < F2)*(C2:C18))

 

[RickyF]

Welcome to MrExcel.

Example:

Excel 2010

	A	B	C	D	E	F
1	Date	Time	High	Low	Start	27/05/2014 06:00
2	25/05/2014	06:00:00	217	212	End	28/05/2014 06:00
3	25/05/2014	12:00:00	300	295	MaxHigh	286
4	25/05/2014	18:00:00	214	209	MinLow	211
5	26/05/2014	00:00:00	256	251
6	26/05/2014	06:00:00	219	214
7	26/05/2014	12:00:00	228	223
8	26/05/2014	18:00:00	295	290
9	27/05/2014	00:00:00	297	292
10	27/05/2014	06:00:00	249	244
11	27/05/2014	12:00:00	216	211
12	27/05/2014	18:00:00	222	217
13	28/05/2014	00:00:00	286	281
14	28/05/2014	06:00:00	279	274
15	28/05/2014	12:00:00	274	269
16	28/05/2014	18:00:00	228	223
17	29/05/2014	00:00:00	212	207
18	29/05/2014	06:00:00	209	204

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula F3 {=MAX(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,C2:C18)))} F4 {=MIN(IF((A2:A18+B2:B18)>=F1,IF((A2:A18+B2:B18)<=F2,D2:D18)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks Andrew works a treat. looks like I need to work on my array formulas.