finding min or max for a given date range when the values are repeating

[VinayB]

Hello Guys,
I need some help. I have some commodity dates and price data in two separate columns and I am trying to find "min" and "max" values for various date ranges. I also need the specific date on which the minimum or maximum value is found.
I am using below formula to find "min" in a given date range: Its a array function!
{=MIN(IF(($C$4:$C$1000<=F15)*($C$4:$C$1000>=G15),$B$4:$B$1000,""))}
The "F" and "G" columns contain dates that I am specifying for date ranges, "C" column has all dates and "B" column has corresponding prices...
Now in order to find the specific date corresponding to the minimum value found through above formula I am using below formula: (again Array!!)
{=INDEX($C$4:$C$1000,MATCH(MIN(IF(($C$4:$C$1000<=F15)*($C$4:$C$1000>=G15),$B$4:$B$1000,"")),$B$4:$B$1000,0))}

Now the problem is that the minimum value in a given date range is identified correctly however since such value is repeated at multiple times the date shown is NOT WITHIN THE DATE RANGE specified to fetch the minimum value....

Can someone please help me out??

With Best Regards,
Vinay

 

[Chief Ace]

I think I ask the forum close to what you are asking. Please see link below:

http://chandoo.org/forum/threads/extract-dates-depart-and-return.26924/#post-160993

Hopefully this will help out.

r/
Chief Ace

 

[VinayB]

Hi Chief,
I tried using IFERROR function as well but somehow its still returning the date which is out of range..
Am I missing something?..still new at this..

Best Regards,
Vinay

 

[p45cal]

1. Minor point but makes it a bit more robust, change the formula in column 11 to
=MIN(IF(($C$3:$C$993<=F11)*($C$3:$C$993>=G11),$B$3:$B$993))

(instead of a "" it comes back with FALSE)

2. Change formula in column K to
=INDEX($C$3:$C$993,MATCH(J11,IF(($C$3:$C$993<=F11)*($C$3:$C$993>=G11),$B$3:$B$993),0))

There's no need to recalculate the min value again, just use what's in column J instead.

You will have to do similar for column I.

 

[VinayB]

Thanks a ton p45cal!!!..It worked..
Best Regards,
Vinay