Finding the low value of a range of values

[ADE2]

Hi
i have the formula below to find the high value(columnC) between tw
times as shown in the 1 hour table at the bottom.

How would i need to modify this formula to search for the LO
value(column D),just swapping MAX to MIN and altering the references t
the C column to D is not enough,what else do i need to do?

=SUMPRODUCT(MAX(($A$3:$A$1000>=M3)*($A$3:$A$1000<=N3)*($C$3:$C$1000)))


5 MIN
TIME OPEN HIGH LOW CLOSE
15:50 1.8394 1.8396 1.8393 1.8396
15:45 1.8396 1.8396 1.8393 1.8394
15:40 1.8389 1.8396 1.8389 1.8396
15:35 1.8389 1.8391 1.8389 1.8389
15:30 1.8385 1.8393 1.8385 1.8389
15:25 1.8386 1.8388 1.8383 1.8385
15:20 1.8393 1.8395 1.8386 1.8386
15:15 1.8393 1.8394 1.8393 1.8393
15:10 1.8390 1.8394 1.8390 1.8393
15:05 1.8394 1.8396 1.8390 1.8390
15:00 1.8397 1.8397 1.8393 1.8394
14:55 1.8408 1.8409 1.8397 1.8397
14:50 1.8407 1.8410 1.8404 1.8408
14:45 1.8405 1.8407 1.8404 1.8407
14:40 1.8405 1.8406 1.8404 1.8405
14:35 1.8405 1.8407 1.8404 1.8405
14:30 1.8404 1.8407 1.8403 1.8405
14:25 1.8401 1.8405 1.8401 1.8404
14:20 1.8402 1.8405 1.8401 1.8401
14:15 1.8409 1.8410 1.8402 1.8402
14:10 1.8408 1.8412 1.8407 1.8409
14:05 1.8400 1.8411 1.8400 1.8408
14:00 1.8393 1.8404 1.8393 1.8400
13:55 1.8395 1.8396 1.8390 1.8393
13:50 1.8396 1.8398 1.8394 1.8395
13:45 1.8392 1.8398 1.8392 1.8396
13:40 1.8396 1.8397 1.8392 1.8392
13:35 1.8394 1.8397 1.8394 1.8396
13:30 1.8392 1.8396 1.8390 1.8394
13:25 1.8399 1.8400 1.8389 1.8392
13:20 1.8397 1.8399 1.8396 1.8399
13:15 1.8393 1.8398 1.8392 1.8397
13:10 1.8393 1.8394 1.8391 1.8393
13:05 1.8395 1.8395 1.8393 1.8393
13:00 1.8390 1.8398 1.8390 1.8395
12:55 1.8394 1.8396 1.8390 1.8390
12:50 1.8391 1.8399 1.8391 1.8394
12:45 1.8395 1.8396 1.8388 1.8391
12:40 1.8386 1.8399 1.8384 1.8395
12:35 1.8376 1.8386 1.8376 1.8386
12:30 1.8381 1.8381 1.8375 1.8376
12:25 1.8374 1.8382 1.8373 1.8381
12:20 1.8374 1.8377 1.8373 1.8374
12:15 1.8364 1.8374 1.8362 1.8374





1 HOUR
high
23:05:00 00:00:00 0.0000
22:05:00 23:00:00 1.8401
21:05:00 22:00:00 1.8386
20:05:00 21:00:00 1.8390
19:05:00 20:00:00 1.8386
18:05:00 19:00:00 0.0000
17:05:00 18:00:00 0.0000
16:05:00 17:00:00 0.0000
15:05:00 16:00:00 1.8396
14:05:00 15:00:00 1.8412
13:05:00 14:00:00 1.8404
12:05:00 13:00:00 1.8399
11:05:00 12:00:00 1.8379
10:05:00 11:00:00 1.8423
09:05:00 10:00:00 1.8428
08:05:00 09:00:00 1.8448
07:05:00 08:00:00 1.8386
06:05:00 07:00:00 1.8386
05:05:00 06:00:00 1.8391
04:05:00 05:00:00 1.8381
03:05:00 04:00:00 1.8378
02:05:00 03:00:00 1.8390
01:05:00 02:00:00 1.8396
00:05:00 01:00:00 1.8398


Thanks for your help

Ad

 

[Aladin Akyurek]

For max...

=MAX(IF($A$3:$A$1000>=M3)*($A$3:$A$1000<=N3),$C$3:$C$1000))

For min...

=MIN(IF($A$3:$A$1000>=M3)*($A$3:$A$1000<=N3),$C$3:$C$1000))

Both formulas must be confirmed with control+shift+enter instead of just
with enter.

 

[ADE2]

Please ignore that last post,i was making a silly mistake,got it workin
no

 

[ADE2]

Thanks for the help Aladi