I've got a series of increasing numbers (dates, actually) in a row. I use
=MAX to get the highest recorded date. Is there any way to get the second
highest date? If it helps, it will always be in the cell to the immediate
left of whichever cell qualifies to be the =MAX.


A B C D E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

Joe, try this, =LARGE(A1:D1,2)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Joe" wrote in message
...
I've got a series of increasing numbers (dates, actually) in a row. I use
=MAX to get the highest recorded date. Is there any way to get the second
highest date? If it helps, it will always be in the cell to the immediate
left of whichever cell qualifies to be the =MAX.


A B C D
E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

Try:

=MAX(IF(A1:A7<MAX(A1:A7),A1:A7))

Replace A1:A7 with the appropriate range. This is an array function and
must be confirmed w/ Cntrl+Shift+Enter instead of just hitting Enter after
keying it in.


"Joe" wrote:

I've got a series of increasing numbers (dates, actually) in a row. I use
=MAX to get the highest recorded date. Is there any way to get the second
highest date? If it helps, it will always be in the cell to the immediate
left of whichever cell qualifies to be the =MAX.


A B C D E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

Hi Joe,

Take a look at the LARGE function.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Joe" wrote in message
...
I've got a series of increasing numbers (dates, actually) in a row. I use
=MAX to get the highest recorded date. Is there any way to get the second
highest date? If it helps, it will always be in the cell to the immediate
left of whichever cell qualifies to be the =MAX.


A B C D E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

Hello,

Would you rather use a CSE formula than a native function? Do you mind me
asking why? Just curious..

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"JMB" wrote in message
...
Try:

=MAX(IF(A1:A7<MAX(A1:A7),A1:A7))

Replace A1:A7 with the appropriate range. This is an array function and
must be confirmed w/ Cntrl+Shift+Enter instead of just hitting Enter after
keying it in.


"Joe" wrote:

I've got a series of increasing numbers (dates, actually) in a row. I
use
=MAX to get the highest recorded date. Is there any way to get the
second
highest date? If it helps, it will always be in the cell to the
immediate
left of whichever cell qualifies to be the =MAX.


A B C D
E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

Only because I don't usually need the LARGE function (and hence, it wasn't
the first solution I thought of).

My preference regarding what formula to use are the one(s) that work.

"Zack Barresse" wrote:

Hello,

Would you rather use a CSE formula than a native function? Do you mind me
asking why? Just curious..

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"JMB" wrote in message
...
Try:

=MAX(IF(A1:A7<MAX(A1:A7),A1:A7))

Replace A1:A7 with the appropriate range. This is an array function and
must be confirmed w/ Cntrl+Shift+Enter instead of just hitting Enter after
keying it in.


"Joe" wrote:

I've got a series of increasing numbers (dates, actually) in a row. I
use
=MAX to get the highest recorded date. Is there any way to get the
second
highest date? If it helps, it will always be in the cell to the
immediate
left of whichever cell qualifies to be the =MAX.


A B C D
E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?

And the formulas do return different results if there are ties in the data (not
a problem with the OP's data, though).



JMB wrote:

Only because I don't usually need the LARGE function (and hence, it wasn't
the first solution I thought of).

My preference regarding what formula to use are the one(s) that work.

"Zack Barresse" wrote:

Hello,

Would you rather use a CSE formula than a native function? Do you mind me
asking why? Just curious..

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"JMB" wrote in message
...
Try:

=MAX(IF(A1:A7<MAX(A1:A7),A1:A7))

Replace A1:A7 with the appropriate range. This is an array function and
must be confirmed w/ Cntrl+Shift+Enter instead of just hitting Enter after
keying it in.


"Joe" wrote:

I've got a series of increasing numbers (dates, actually) in a row. I
use
=MAX to get the highest recorded date. Is there any way to get the
second
highest date? If it helps, it will always be in the cell to the
immediate
left of whichever cell qualifies to be the =MAX.


A B C D
E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the MAX?







--

Dave Peterson

Thanks, was just curious. Hope you don't mind. :)

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"JMB" wrote in message
...
Only because I don't usually need the LARGE function (and hence, it wasn't
the first solution I thought of).

My preference regarding what formula to use are the one(s) that work.

"Zack Barresse" wrote:

Hello,

Would you rather use a CSE formula than a native function? Do you mind
me
asking why? Just curious..

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"JMB" wrote in message
...
Try:

=MAX(IF(A1:A7<MAX(A1:A7),A1:A7))

Replace A1:A7 with the appropriate range. This is an array function
and
must be confirmed w/ Cntrl+Shift+Enter instead of just hitting Enter
after
keying it in.


"Joe" wrote:

I've got a series of increasing numbers (dates, actually) in a row. I
use
=MAX to get the highest recorded date. Is there any way to get the
second
highest date? If it helps, it will always be in the cell to the
immediate
left of whichever cell qualifies to be the =MAX.


A B C D
E
F
1 9-20-05 9-25-05 9-26-05 10-03-05
2
3

=MAX(A1:D1) should give me 10-03-05, right?

is there any way to return '9-26-05' or the cell to the left of the
MAX?