look up maximum date in a range
 

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005 and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

€žHank€� ezt Ã*rta:

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005 and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt írta:

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

Thanks Stefi, I tried and it returned the result exactly same value as End
date, it actually didn't perform comparison.


Lease # Start date End date RESULT
(=MAX(--(A2:A5="001")*C2:C5))
001 2/1/2002 1/31/2005 1/31/2005
001 2/1/2005 1/31/2008 1/31/2008
002 1/1/2006 4/30/2009 4/30/2009
002 5/1/2009 10/31/2012 10/31/2012

it is not what I want to come up... ?
Hank



"Stefi" wrote:

=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

€žHank€� ezt Ã*rta:

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005 and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

Hi Biff

I tried your formula as well =MAX(IF(A2:A5000="001",C2:C5000))
it comes up the maximum of column of "end date" which is 10/31/2012,
regardless lease #

It is not what I want to get... can u please help me more?
Thanks
Hank




"T. Valko" wrote:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt Ã*rta:

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

All of the formulas suggested are array formulas.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hank" wrote in message
...
Hi Biff

I tried your formula as well =MAX(IF(A2:A5000="001",C2:C5000))
it comes up the maximum of column of "end date" which is 10/31/2012,
regardless lease #

It is not what I want to get... can u please help me more?
Thanks
Hank




"T. Valko" wrote:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt írta:

Hi there,

I have a few lease contracts, they have different lease periods, some
of
them has different renewal terms. I would like to look up the latest
date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between
1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

Thanks Biff, I never used array formula before, it certainly work by hit crtl
shift enter. Thanks for your help... Also I come up another solution by use
lookup and max together. =MAX(LOOKUP(A4,A:A,C:C)) A4 is lease #, AA is lease
# column and CC is end date column, it comes the same result.

Hank

"T. Valko" wrote:

All of the formulas suggested are array formulas.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hank" wrote in message
...
Hi Biff

I tried your formula as well =MAX(IF(A2:A5000="001",C2:C5000))
it comes up the maximum of column of "end date" which is 10/31/2012,
regardless lease #

It is not what I want to get... can u please help me more?
Thanks
Hank




"T. Valko" wrote:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt Ã*rta:

Hi there,

I have a few lease contracts, they have different lease periods, some
of
them has different renewal terms. I would like to look up the latest
date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between
1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

=MAX(LOOKUP(A4,A:A,C:C))

You don't need the MAX function:

=LOOKUP(A4,A:A,C:C)

Just be careful with that. It will do what you want as long as the data in
column A is sorted in ascending order *and* there is always an exact match
of the lookup value.

--
Biff
Microsoft Excel MVP


"Hank" wrote in message
...
Thanks Biff, I never used array formula before, it certainly work by hit
crtl
shift enter. Thanks for your help... Also I come up another solution by
use
lookup and max together. =MAX(LOOKUP(A4,A:A,C:C)) A4 is lease #, AA is
lease
# column and CC is end date column, it comes the same result.

Hank

"T. Valko" wrote:

All of the formulas suggested are array formulas.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hank" wrote in message
...
Hi Biff

I tried your formula as well =MAX(IF(A2:A5000="001",C2:C5000))
it comes up the maximum of column of "end date" which is 10/31/2012,
regardless lease #

It is not what I want to get... can u please help me more?
Thanks
Hank




"T. Valko" wrote:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date
(a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt írta:

Hi there,

I have a few lease contracts, they have different lease periods,
some
of
them has different renewal terms. I would like to look up the
latest
date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008
(that's
compare two end date in lease 001 in maximum end date between
1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank

look up maximum date in a range
 

Thanks for your advices!
Stefi


€žT. Valko€� ezt Ã*rta:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt Ã*rta:

Hi there,

I have a few lease contracts, they have different lease periods, some of
them has different renewal terms. I would like to look up the latest date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between 1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank