Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a Value between dates based on another Value
Sample data:
ColA ColB ColC Row1 22 1/16/2009 55 Row2 23 1/18/2009 17 Row3 22 1/20/2009 33 Row4 25 1/23/2009 9 I have several thousand rows with every column a named range, i.e. rng1, rng2, etc. Example: I want to return '55' and '33' from ColC, based on '22' from ColA between the dates 1/1/2009 and 1/31/2009 (ColB). Thx, casey |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a Value between dates based on another Value
I have several thousand rows
Any formula solution for that many rows will be slow to calculate. Have you considered using a filter? -- Biff Microsoft Excel MVP "casey" wrote in message ... Sample data: ColA ColB ColC Row1 22 1/16/2009 55 Row2 23 1/18/2009 17 Row3 22 1/20/2009 33 Row4 25 1/23/2009 9 I have several thousand rows with every column a named range, i.e. rng1, rng2, etc. Example: I want to return '55' and '33' from ColC, based on '22' from ColA between the dates 1/1/2009 and 1/31/2009 (ColB). Thx, casey |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a Value between dates based on another Value
I do use the filter for ad hoc analysis. However, I want to 'list' the
answer (plus many others) to another area for static charts using dynamic data. The extra time is ok with me as I have many reports that run weekly/monthly using this data already. thx, casey "T. Valko" wrote: I have several thousand rows Any formula solution for that many rows will be slow to calculate. Have you considered using a filter? -- Biff Microsoft Excel MVP "casey" wrote in message ... Sample data: ColA ColB ColC Row1 22 1/16/2009 55 Row2 23 1/18/2009 17 Row3 22 1/20/2009 33 Row4 25 1/23/2009 9 I have several thousand rows with every column a named range, i.e. rng1, rng2, etc. Example: I want to return '55' and '33' from ColC, based on '22' from ColA between the dates 1/1/2009 and 1/31/2009 (ColB). Thx, casey |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a Value between dates based on another Value
Try this...
Data in the range A1:C4. A1:A4 = named range Rng1 B1:B4 = named range Rng2 C1:C4 = named range Rng3 Enter these column headers: E1 = Lookup F1 = From G1 = To H1 = Total Records I1 = Results E2:G2 are pretty much self-explanatory. E2 = lookup value 22 F2 = start date 1/1/2009 G2 = end date 1/31/2009 Enter this formula in H2. This will return the count of records that meet the criteria: =SUMPRODUCT(--(Rng1=E2),--(Rng2=F2),--(Rng2<=G2)) Enter this array formula** in I2. This will extract those records. =IF(ROWS(I$2:I2)H$2,"",INDEX(Rng3,SMALL(IF((Rng1= E$2)*(Rng2=F$2)*(Rng2<=G$2),ROW(Rng3)),ROWS(I$2:I 2))-MIN(ROW(Rng3))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in I2 down until you get blanks meaning all records have been extracted. Depending on how many rows you copy the array formula to, this could take several seconds to calculate. -- Biff Microsoft Excel MVP "casey" wrote in message ... I do use the filter for ad hoc analysis. However, I want to 'list' the answer (plus many others) to another area for static charts using dynamic data. The extra time is ok with me as I have many reports that run weekly/monthly using this data already. thx, casey "T. Valko" wrote: I have several thousand rows Any formula solution for that many rows will be slow to calculate. Have you considered using a filter? -- Biff Microsoft Excel MVP "casey" wrote in message ... Sample data: ColA ColB ColC Row1 22 1/16/2009 55 Row2 23 1/18/2009 17 Row3 22 1/20/2009 33 Row4 25 1/23/2009 9 I have several thousand rows with every column a named range, i.e. rng1, rng2, etc. Example: I want to return '55' and '33' from ColC, based on '22' from ColA between the dates 1/1/2009 and 1/31/2009 (ColB). Thx, casey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return value based on another value | Excel Discussion (Misc queries) | |||
Return dates based on month and day of week | Excel Worksheet Functions | |||
return based on range of dates | Excel Worksheet Functions | |||
return value based upon another | Excel Worksheet Functions | |||
return array result in cell based on comparing dates | Excel Worksheet Functions |