# Question about using MIN/MAX, INDEX with Date range

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.

P

#### Pete_UK

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

M

#### ML Srini

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!

M

#### ML Srini

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!