conditional MIN() function

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

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

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

HTH
--
AP

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

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

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


"markx" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> Hello once again,
> I've just found the solution:
>
> (Array entered)
> =MIN(IF(B:B>=D1;A:A))
>
>
> "markx" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Hello once again,
>> I've just found the solution:
>>
>> (Array entered)
>> =MIN(IF(B:B>=D1;A:A))
>>
>>
>> "markx" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>
>