MIN within range based on criteria

[StevenL]

I have two rows of dates, row A - "Start" date & row B - "Due" date. In cell
A1 user can select a start date from the range in row A below. There will be
multiple lines that have same "Start" date but the "Due" date could vary. I
would like the MIN "Due" date value to appear in B1 based on the
cooresponding "Start" date user chooses in cell A1.
ie.
A1. user chooses start date / B1. MIN value row B appears
A2. 12/1/5 B2. 12/4/5
A3. 12/2/5 B3. 12/6/5
A4. 12/3/5 B4. 12/6/5
A5. 12/3/5 B5. 12/5/5
if user chooses start date of 12/3/5 the MIN value within row B range is
12/5/5. Can someone help me write a formula for B1.
Thanks, Steven

[Max]

One way .

Put in the formula bar for B1 and array-enter
(i.e. press CTRL+SHIFT+ENTER):
=IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> I have two rows of dates, row A - "Start" date & row B - "Due" date. In
cell
> A1 user can select a start date from the range in row A below. There will
be
> multiple lines that have same "Start" date but the "Due" date could vary.
I
> would like the MIN "Due" date value to appear in B1 based on the
> cooresponding "Start" date user chooses in cell A1.
> ie.
> A1. user chooses start date / B1. MIN value row B appears
> A2. 12/1/5 B2. 12/4/5
> A3. 12/2/5 B3. 12/6/5
> A4. 12/3/5 B4. 12/6/5
> A5. 12/3/5 B5. 12/5/5
> if user chooses start date of 12/3/5 the MIN value within row B range is
> 12/5/5. Can someone help me write a formula for B1.
> Thanks, Steven
>

[CLR]

If you would sort your data using column A as the primary key ascending and
column B as the secondary dey ascending, then the following in B1 will
do........(ranges based on your sample data, change as required)

=IF(AND(ISNUMBER(A1),A1>=MIN(A2:A5)),VLOOKUP(A1,A2:B5,2,FALSE),"No
acceptable date in A1")

Vaya con Dios,
Chuck, CABGx3


"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> I have two rows of dates, row A - "Start" date & row B - "Due" date. In
cell
> A1 user can select a start date from the range in row A below. There will
be
> multiple lines that have same "Start" date but the "Due" date could vary.
I
> would like the MIN "Due" date value to appear in B1 based on the
> cooresponding "Start" date user chooses in cell A1.
> ie.
> A1. user chooses start date / B1. MIN value row B appears
> A2. 12/1/5 B2. 12/4/5
> A3. 12/2/5 B3. 12/6/5
> A4. 12/3/5 B4. 12/6/5
> A5. 12/3/5 B5. 12/5/5
> if user chooses start date of 12/3/5 the MIN value within row B range is
> 12/5/5. Can someone help me write a formula for B1.
> Thanks, Steven
>

[StevenL]

Thanks Chuck, but relying on others to fill in the data and can expect no
consistency. The formula will be for multiple others and not always there to
explain below. I need to stay away from index and Vlookup formulas.

Brgds and thanks for the quick response.
Steven


"CLR" wrote:

> If you would sort your data using column A as the primary key ascending and
> column B as the secondary dey ascending, then the following in B1 will
> do........(ranges based on your sample data, change as required)
>
> =IF(AND(ISNUMBER(A1),A1>=MIN(A2:A5)),VLOOKUP(A1,A2:B5,2,FALSE),"No
> acceptable date in A1")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date. In
> cell
> > A1 user can select a start date from the range in row A below. There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date / B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5. Can someone help me write a formula for B1.
> > Thanks, Steven
> >
>
>
>

[StevenL]

Thanks Max, worked like a charm.
Best Regards,
Steven

"Max" wrote:

> One way .
>
> Put in the formula bar for B1 and array-enter
> (i.e. press CTRL+SHIFT+ENTER):
> =IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date. In
> cell
> > A1 user can select a start date from the range in row A below. There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date / B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5. Can someone help me write a formula for B1.
> > Thanks, Steven
> >
>
>
>

[Max]

Glad to hear that, Steven !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:6EAE1899-008E-49A9-9EFD-32677765953C@microsoft.com...
> Thanks Max, worked like a charm.
> Best Regards,
> Steven

[StevenL]

Max, I left one thing out... there are two cells for the user to set
criteria. A "Start" and "End" date.
A2:A100>=A1
and
A2:A100<=B1

Don't suppose you have a solution? (thought I'd be able to sort this out
myself)
Rgds, Steven

"Max" wrote:

> One way .
>
> Put in the formula bar for B1 and array-enter
> (i.e. press CTRL+SHIFT+ENTER):
> =IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date. In
> cell
> > A1 user can select a start date from the range in row A below. There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date / B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5. Can someone help me write a formula for B1.
> > Thanks, Steven
> >
>
>
>

[Max]

With the start and end dates in A1 and B1,

Try this revised formula in C1 (array-entered as before):
=IF(OR(A1="",B1=""),"",MIN(IF((A2:A100>=A1)*(A2:A100<=B1),B2:B100)))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:D19FF2A7-BB19-4EDC-879C-8D68DA840B3E@microsoft.com...
> Max, I left one thing out... there are two cells for the user to set
> criteria. A "Start" and "End" date.
> A2:A100>=A1
> and
> A2:A100<=B1
>
> Don't suppose you have a solution? (thought I'd be able to sort this out
> myself)
> Rgds, Steven

[StevenL]

Again, worked liked a charm. Thanks for your help MAX!

Best Regards

"Max" wrote:

> With the start and end dates in A1 and B1,
>
> Try this revised formula in C1 (array-entered as before):
> =IF(OR(A1="",B1=""),"",MIN(IF((A2:A100>=A1)*(A2:A100<=B1),B2:B100)))
>
> Adapt the ranges to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:D19FF2A7-BB19-4EDC-879C-8D68DA840B3E@microsoft.com...
> > Max, I left one thing out... there are two cells for the user to set
> > criteria. A "Start" and "End" date.
> > A2:A100>=A1
> > and
> > A2:A100<=B1
> >
> > Don't suppose you have a solution? (thought I'd be able to sort this out
> > myself)
> > Rgds, Steven
>
>
>

[Max]

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:A2DE1023-51DF-430B-9661-C364BC678512@microsoft.com...
> Again, worked liked a charm. Thanks for your help MAX!
>
> Best Regards