Getting the maximum & Minimum value in a range

Hi:

I have a series of data (Open - High - Low - Close) based on date.

I need the maximum & minimum value between certain dates.

I am attaching an excel sheet explaining what I mean.

Looking forward to help...

Thanks & Best Regards,

Array formulas
=MAX(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))
and
=MIN(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Adjust ranges accordingly

EDIT: Changed range to Column C

Sligthly quicker:
=MAX(IF($A$2:$A$2041>=A3,IF(G2:$G$2041<=G3,$C$2:$C$2041)))
=MIN(IF($A$2:$A$2041>=A3,IF(G2:$G$2041<=G3,$C$2:$C$2041)))

Confirm COntrol+Shift+Enter

It does not work becoz I am getting the maximum and minimum values in column C & D respectively...

Originally Posted by Ace_XL

Array formulas
=MAX(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$C$2:$C$2041))
and
=MIN(IF(($A$2:$A$2041>=A3)*(G2:$G$2041<=G3),$D$2:$D$2041))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Adjust ranges accordingly

What's your expected result for let say M3 and Q3?

Apologies to the forum.... There was an error in the formula.... I will be correcting the same and attaching a new file...

Thanks friends...

I have managed to resolve the issue with some permutations & combinations....

Best Regards,