Finding min/max of adjacent data in a range of cells

[Paul987]

I am trying to find the min/max number in an x number of adjacent cells.
For instance in the following data set:

7/15/2005 10
7/16/2005 9
7/17/2005 8
7/18/2005 8
7/19/2005 7 *Less than any of the 4 days before/after*
7/20/2005 9
7/21/2005 10
7/22/2005 11
7/23/2005 15
7/24/2005 9 *Less than any of the 1 days before/after*
7/25/2005 10
7/26/2005 8
7/27/2005 10
7/28/2005 6
7/29/2005 2 *Less than any of the 3 days before/after*
7/30/2005 3
7/31/2005 4
8/1/2005 5


If I were looking for 2 day mins, it would return the dates 7/29/2005,
7/19/2005. It is important that the value is the lowest number *at
least * x days before and after, though it could be more. In the
example above, the value in 7/19/2005 is smaller than any number 4 days
before *and * after. Eventually, I would also like this function to
only return the most recent occurance. I would also like to have "x"
be a reference, so I can change it on the fly. It will also be
necessary for me to limit the search to a date range, but I may be able
to work these details out later. A cell formula (or two) would work
best for me, because it may need to be minipulated a bit before I get
it exactly right for my situation. If it can only be done with VBA let
me know. If you can help me with any or all of this I would appreciate
it. Either way, I (we?) need to figure this out. Thanks,
Paul

 

[Paul987]

please help

 

[Morrigan]

I am not very good in explaining, here I attach a file.

Hope it helps.


+-------------------------------------------------------------------+
|Filename: Min Max.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3603 |
+-------------------------------------------------------------------+

 

[Paul987]

at work, shouldn't open it. THanks, though

 

[Morrigan]

I forgot to limit the upper range. (Old spreadsheet searches for data
out of the spreadsheet range if the search range goes too high and
yields incorrect result.) Here is a new one.


Hope it helps.


+-------------------------------------------------------------------+
|Filename: Min Max.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3604 |
+-------------------------------------------------------------------+