Finding smallest value after certain value in an unsorted range

  • Thread starter Thread starter moily
  • Start date Start date
M

moily

Hi there,

Appreciate any help that's formula based - I can't use VBA - for 2003.

I've got a range of data between B10 and B253 (with equivalent dates in A10
to A253). I have a year in B255 and month in B256. In cell B259 I have a
formula that finds the largest number after the given date in cells B255 and
B256. In cell B260 I would like to find the smallest number after the date
in cells B255 and B256 AND AFTER the largest number as found in cell B259.
My formula below does the first condition but not the second. Could anyone
ammend it to ensure that the number it finds is AFTER the number found in
B259?

=MIN(IF(DATE(YEAR(A$10:A$253),MONTH(A$10:A$253),1)>=DATE($B$255,$B$256,1),B$10:B$253,""))

Many thanks in advance for any help!

Cheers,
Ann
 
Your second condition statement just needs to check that the returned row of
the value you want is after the returned row of your first condition (max
statement).

The MATCH function embedded in the statement will return the row in the
array as the result. So I added the condition of MATCH to both the MAX
function and the MIN function with a second IF statement provided your first
statement is satisfied.



=MIN(IF(DATE(YEAR($A$10:$A$253),MONTH($A$10:$A$253),1)>=DATE($B$255,$B$256,1),IF(MATCH((IF(DATE(YEAR($A$10:$A$253),MONTH($A$10:$A$253),1)>=DATE($B$255,$B$256,1),$B$10:$B$253,"")),$B$10:$B$253,0)>MATCH(MAX(IF(DATE(YEAR($A$10:$A$253),MONTH($A$10:$A$253),1)>=DATE($B$255,$B$256,1),$B$10:$B$253,"")),$B$10:$B$253,0),$B$10:$B$253,""),""))
 
Oops,

You mentioned you already had the MAX value in $B$259. I made it all one big
equation.

Substitute the part of the equation that has the embedded MAX statement with
$B$259:

=MIN(IF(DATE(YEAR($A$10:$A$253),MONTH($A$10:$A$253),1)>=DATE($B$255,$B$256,1),IF(MATCH((IF(DATE(YEAR($A$10:$A$253),MONTH($A$10:$A$253),1)>=DATE($B$255,$B$256,1),$B$10:$B$253,"")),$B$10:$B$253,0)>MATCH($B$259,$B$10:$B$253,0),$B$10:$B$253,""),""))
 
Back
Top