| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Nearest and Furthest Date
My data table is like so. My date format is YY/M
Today 7-Aug Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Trying to create this table. Basically I am trying to find the date in the data table that is the closest and furthest away from today's date. DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding Nearest and Furthest Date
=MIN(IF(B2:B20<TODAY(),B2:B20))
HTHwhich is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and similar for MAX =MAX(IF(B2:B20TODAY(),B2:B20)) -- --- Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... My data table is like so. My date format is YY/M Today 7-Aug Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Trying to create this table. Basically I am trying to find the date in the data table that is the closest and furthest away from today's date. DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding Nearest and Furthest Date
On Mon, 20 Aug 2007 09:30:05 -0700, carl
wrote: My data table is like so. My date format is YY/M Today 7-Aug Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Trying to create this table. Basically I am trying to find the date in the data table that is the closest and furthest away from today's date. DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug Thank you in advance. You could use a Pivot Table. Below is the result I got with your data Stock DateMax DateMin OIH 9-Jan 7-Aug SPY 10-Dec 7-Oct I dragged Stock to the Rows Area I dragged Date to the Data area, and changed the field setting to Min of Dates, renaming DateMin I dragged Date to the Data area (again), and changed the field setting to Max of Dates, renaming DateMax I then selected a report format that was similar to what you used above. --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding Nearest and Furthest Date
Thanks Again Bob.
I did not explain my problem very well. The new table DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug For the formula in B2. I need it to look at A2 and find the cooresponding Max or Min Date in the data table. I tried your formula but could not get it to return the correct values. Here's the data table again: Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Thanks Again. "Bob Phillips" wrote: =MIN(IF(B2:B20<TODAY(),B2:B20)) HTHwhich is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and similar for MAX =MAX(IF(B2:B20TODAY(),B2:B20)) -- --- Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... My data table is like so. My date format is YY/M Today 7-Aug Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Trying to create this table. Basically I am trying to find the date in the data table that is the closest and furthest away from today's date. DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug Thank you in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding Nearest and Furthest Date
=MIN(IF((A2:A20="SPY")*(B2:B20<TODAY()),B2:B20))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... Thanks Again Bob. I did not explain my problem very well. The new table DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug For the formula in B2. I need it to look at A2 and find the cooresponding Max or Min Date in the data table. I tried your formula but could not get it to return the correct values. Here's the data table again: Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Thanks Again. "Bob Phillips" wrote: =MIN(IF(B2:B20<TODAY(),B2:B20)) HTHwhich is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and similar for MAX =MAX(IF(B2:B20TODAY(),B2:B20)) -- --- Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... My data table is like so. My date format is YY/M Today 7-Aug Stock Date SPY 7-Oct SPY 7-Dec SPY 10-Dec OIH 7-Aug OIH 9-Jan Trying to create this table. Basically I am trying to find the date in the data table that is the closest and furthest away from today's date. DateMax DateMin SPY 10-Dec 7-Oct OIH 9-Jan 7-Aug Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding nearest match from an array | Excel Discussion (Misc queries) | |||
| Find the nearest Christmas to a date | Excel Worksheet Functions | |||
| how do I deduct furthest south cell from furthest north cell? | Excel Discussion (Misc queries) | |||
| finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
| How do I select the nearest date from a ranges of dates? | Excel Discussion (Misc queries) | |||
Linear Mode
