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 a
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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544621

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

HTH
--
AP

"markx" a écrit dans le message de news:
...
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 a
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

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

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


"markx" wrote in message
...
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 a
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

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" wrote in message
...
Hello once again,
I've just found the solution:

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


"markx" wrote in message
...
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 a
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

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,
"Bob Phillips" wrote in message
...
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" wrote in message
...
Hello once again,
I've just found the solution:

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


"markx" wrote in message
...
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 a
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