conditional MIN() function

[markx]

Hello,

I have two columns: "Starting date" (A) and "Ending date" (B) with values in
more than 100 rows.
In addition, I have current date in D1 cell.

I want to run on the A column a conditional MIN() function and do the
following:
Find MIN in column A, but excluding (from the calculation) the rows where
the value in B column (ending date) is smaller than the current date
(specified in D1 cell).


If D1 = 01 May 2006
and the rows are:
column A column B
1 Jan 02 1 Dec 2005
1 Mar 03 1 Jan 2006
1 Dec 03 1 Mar 2008
1 Apr 05 1 Aug 2007
then the normal MIN(A:A) would give me 1 Jan 02

However, I'm looking for 1 Dec 03 (=the smallest value in column A among the
rows where the value in column B is equal or bigger than D1).

Could you give me some hints how to achieve this?
Many thanks for your help!

Mark

 

[daddylonglegs]

Try this

=MIN(IF(B1:B100>=D1,A1:A100))

confirmed with CTRL+SHIFT+ENTER

note: you can't use whole column references like a:a

 

[Ardus Petus]

This is an array formula, to validate with Ctrl+Shift+Enter:
=MIN(IF(B1:B4>=D1,A1:A4))

HTH

 

[markx]

Hello once again,
I've just found the solution:

(Array entered)
=MIN(IF(B:B>=D1;A:A))

 

[Bob Phillips]

That won't work, array formulae cannot work on a whole column, you need to
define the range such as

=MIN(IF(B1:B100>=D1,A1:A100))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[markx]

Thank you so much for all your feedback.

Regarding my second post, I've just wanted to write the general syntax, but
you're right that it could be confusing for others. Sorry for this. I'll try
to avoid this kind of 'shortcuts' in the future.

Anyway,