Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Values Matching EXACT Date for Criteria
Hi All,
I would like a Formula that returns numeric values ONLY if they MATCH the EXACT date. The Formula below does work in part. But when the actual date does not exist or a value for that date does not exist, the Formula returns a value nearest that date. That is, 31/06/2006 does not exist as June only has 30 days. But the Formula retruns a value that has a date 01/07/2006. =INDEX(OFFSET(Data,0,0,,10),MATCH(DATE(YEAR($B22), MONTH($B22),DAY($AB$4)), Date,0),COLUMN(A:A)) The dynamic named range "Data" spans 10 columns and many rows - houses numeric values. I would like to have numeric values returned using an EXACT date for the criteria. "Date" is a single column dynamic range. The values in "Date" are formatted as 11/06/2006. The values in column "B" are also formatted as 11/06/2006. The Day in column $AB$4 can be a single or double- digit: 1, 11, 31 etc. Thanks Sam -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Values Matching EXACT Date for Criteria
Does AB4 contain a true date value formatted to display the day, or does
it actually contain a one or two digit number. If the former, try replacing... DATE(YEAR($B22),MONTH($B22),DAY($AB$4)) with (TEXT($AB$4,"d")&"/"&TEXT($B22,"mmm/yy"))+0 Otherwise, try... ($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 Note that the formula will return #VALUE! when the date doesn't exist, such as 31/06/2006, and will return #N/A when the date is not found. If so desired, the formula can be amended to trap error values. Hope this helps! In article <680e888d7f189@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I would like a Formula that returns numeric values ONLY if they MATCH the EXACT date. The Formula below does work in part. But when the actual date does not exist or a value for that date does not exist, the Formula returns a value nearest that date. That is, 31/06/2006 does not exist as June only has 30 days. But the Formula retruns a value that has a date 01/07/2006. =INDEX(OFFSET(Data,0,0,,10),MATCH(DATE(YEAR($B22), MONTH($B22),DAY($AB$4)), Date,0),COLUMN(A:A)) The dynamic named range "Data" spans 10 columns and many rows - houses numeric values. I would like to have numeric values returned using an EXACT date for the criteria. "Date" is a single column dynamic range. The values in "Date" are formatted as 11/06/2006. The values in column "B" are also formatted as 11/06/2006. The Day in column $AB$4 can be a single or double- digit: 1, 11, 31 etc. Thanks Sam |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Values Matching EXACT Date for Criteria
Hi Domenic,
Thanks for reply and your assistance. Cell AB4 does just contain a one or two digit number. So, I'm using your second suggestion: ($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 What does the +0 actually do? Cell AB4 contains the two digit number 31 and the month in B22 is June. It returns has #N/A. Should it have returned #VALUE as the 31 June does not exist? =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), COLUMN(A:A)) Cheers, Sam Domenic wrote: Does AB4 contain a true date value formatted to display the day, or does it actually contain a one or two digit number. If the former, try replacing... DATE(YEAR($B22),MONTH($B22),DAY($AB$4)) with (TEXT($AB$4,"d")&"/"&TEXT($B22,"mmm/yy"))+0 Otherwise, try... ($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 Note that the formula will return #VALUE! when the date doesn't exist, such as 31/06/2006, and will return #N/A when the date is not found. If so desired, the formula can be amended to trap error values. Hope this helps! -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Values Matching EXACT Date for Criteria
In article <68105295afa28@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote: Hi Domenic, Thanks for reply and your assistance. Cell AB4 does just contain a one or two digit number. So, I'm using your second suggestion: ($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 What does the +0 actually do? That part of the formula returns the date as a text string. The +0 bit coerces it into a true date value. Cell AB4 contains the two digit number 31 and the month in B22 is June. It returns has #N/A. Should it have returned #VALUE as the 31 June does not exist? =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), COLUMN(A:A)) Opening and closing brackets for the lookup value have been omitted. The formula should be as follows... =INDEX(OFFSET(Data,0,0,,10),MATCH(($AB$4&"/"&TEXT($B22,"mm/yy"))+0,Date,0 ),COLUMN(A:A)) Hope this helps! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Values Matching EXACT Date for Criteria
Hi Domenic,
Thank you very much for explanation and corrected Formula. That's Great! Cheers, Sam Domenic wrote: Hi Domenic, [quoted text clipped - 5 lines] What does the +0 actually do? That part of the formula returns the date as a text string. The +0 bit coerces it into a true date value. Cell AB4 contains the two digit number 31 and the month in B22 is June. It returns has #N/A. Should it have returned #VALUE as the 31 June does not exist? =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), COLUMN(A:A)) Opening and closing brackets for the lookup value have been omitted. The formula should be as follows... =INDEX(OFFSET(Data,0,0,,10),MATCH(($AB$4&"/"&TEXT($B22,"mm/yy"))+0,Date,0 ),COLUMN(A:A)) Hope this helps! -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search /Filter vertical Numeric pattern (down single column) | Excel Worksheet Functions | |||
Match 3 Criteria and Return Lowest Numeric Value | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions |