Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about using MIN/MAX, INDEX with Date range
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about using MIN/MAX, INDEX with Date range
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 On Oct 3, 10:55 pm, ML Srini wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about using MIN/MAX, INDEX with Date range
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! On Oct 3, 3:06 pm, Pete_UK wrote: 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 On Oct 3, 10:55 pm, ML Srini wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about using MIN/MAX, INDEX with Date range
On Oct 3, 4:28 pm, ML Srini wrote:
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! On Oct 3, 3:06 pm, Pete_UK wrote: 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 On Oct 3, 10:55 pm, ML Srini wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index() & Row() Question | Excel Discussion (Misc queries) | |||
Index/match question. | Excel Discussion (Misc queries) | |||
Date Range Question | Excel Discussion (Misc queries) | |||
Index question | Excel Worksheet Functions | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) |