Question about using MIN/MAX, INDEX with Date range

  • Thread starter Thread starter ML Srini
  • Start date Start date
M

ML Srini

Hello,
Hope everybody is doing well. I am trying to find a solution to the
problem I am having for some time. I seem to have found half of the
solution from one of the threads and stuck finding the other half.
Here is what I have:

ColumnA ColumnB MIN MAX
ColumnC ColumnD
Date1 Date2 ???? MAX(INDEX)
Date1 value1
*(see below)
Date1a value1a

Date1b value1b

Date1c value1c

Date1d value1d

Date1e value1e

Date2 value2

Date3 value3

Date4 value4

Date5 value5
Problem: To find MIN and MAX value (Column D) between dates Date1 and
Date2 (Column A&B)
I have the solution for MAX from one of the threads. I am using,
=MAX(INDEX((A2<=$C$2:$C$7)*(B2>=$C$2:$C$7)*($D$1:$D$7),0)) which gives
the maximum of ColumnD between Date1 and Date2. (I am still trying to
understand how this works.)
But, if I use =MIN(INDEX((!$C$2:$C$7>=A2)*($C$2:$C$7<=B2)*($D$1:$D$7),
0)) for MIN, I am getting 0 (there are no 0s or -ve numbers in
ColumnD). Does anybody know how I can resolve this?
I really appreciate help regarding this.

Thanks and have a wonderful day.
 
You are picking the 0 up from the end of your MIN formula - you need
to change this to a larger number. As you are dealing with dates, I
would suggest 40,000, like so:

=MIN(INDEX((!$C$2:$C$7>=A2)*($C$2:$C$7<=B2)*($D$1:$D$7),40000))

Hope this helps.

Pete
 
Hello Pete,

Thanks. But if you look at the formula, 40000 is not used for MIN
value (as in the MAX formula, the 0 is not used for MAX function. It
is one of the parameters for INDEX function).
So, it is not working!
 
Hello Pete,

Thanks. But if you look at the formula, 40000 is not used for MIN
value (as in the MAX formula, the 0 is not used for MAX function. It
is one of the parameters for INDEX function).
So, it is not working!
Click to expand...
 
Back
Top