Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Maximum between Range of Dates | Excel Discussion (Misc queries) | |||
maximum over a non continuous range | Excel Worksheet Functions | |||
maximum number from a text range | Excel Worksheet Functions | |||
vlookup maximum cell range? | Excel Discussion (Misc queries) | |||
How do I get the maximum absolute value of a range of numbers? | Excel Discussion (Misc queries) |