MIN within range based on criteria
 

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

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 <atyahoo<dotcom
----
"StevenL" wrote in message
...
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

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

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

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 <atyahoo<dotcom
----
"StevenL" wrote in message
...
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

Glad to hear that, Steven !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"StevenL" wrote in message
...
Thanks Max, worked like a charm.
Best Regards,
Steven

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 <atyahoo<dotcom
----
"StevenL" wrote in message
...
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

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:A1 00<=B1),B2:B100)))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"StevenL" wrote in message
...
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

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:A1 00<=B1),B2:B100)))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"StevenL" wrote in message
...
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

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"StevenL" wrote in message
...
Again, worked liked a charm. Thanks for your help MAX!

Best Regards