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
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387513

please. help.


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387513

Hi Paul! If your column of numbers is in Column B, I entered this test
equation in cell C5.
It is looking at the cell to its left (B5).
Suppose "n" refers to a cell with a value of 3 (3 days +- the reference
value)
Offset is used to set a new reference, and Min looks for the minimum in the
range.
If the Cell to the left is that minimum, then column C has an "X".
Is this something that would work?

=IF(MIN(OFFSET(B5,-n,0,2*n+1))=B5,"X","")

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Paul987" wrote in
message ...

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
------------------------------------------------------------------------
Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387513

Dana -

Thanks for the help. I think that solution will work. One furthe
question:

Would it be possible to restrict the formula to one cell, and just hav
it return the most recent x day min? My spreadsheet is gettin
extremely complicated, and I want to avoid having to change reference
in macros already written. Thanks again.

-Pau

--
Paul98
-----------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...fo&userid=2485
View this thread: http://www.excelforum.com/showthread.php?threadid=38751