How do I get excel to choose the second to last # in a series?
 

How do I get excel to choose the second to last # in a series?
 

This formula returns the 2nd largest number from A1:A100
=LARGE(A1:A100,2)

Is that something you can work with?

Regards,

Ron
Microsoft MVP (Excel)


"coolerthancool" wrote in message
...

How do I get excel to choose the second to last # in a series?
 

On Sun, 15 Jul 2007 17:10:00 -0700, coolerthancool
wrote:


"Series" can have a number of different meanings.

If your series is listed in column A, and there are no blanks, this formula
will return the second to last entry:

=INDEX(A:A,COUNT(A:A)-1)

If your series is in column A, and there might be blanks or non-numbers listed,
then this **array-entered** formula will return the second to last number.

To array-enter a formula, after typing it into the formula bar, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.

=INDEX(A:A,LARGE(ROW(A1:A65535)*ISNUMBER(A1:A65535 ),2))
--ron

How do I get excel to choose the second to last # in a series?
 

No. I don't want second largest. I want second to last.

If there are a series of numbers: 1, 2, 4, 5, 5

The second to largest is 5 because 5 and 5 are two separate #'s. I only
want to identify the second (or 3rd or 4th to last).

"Ron Coderre" wrote:

This formula returns the 2nd largest number from A1:A100
=LARGE(A1:A100,2)

Is that something you can work with?

Regards,

Ron
Microsoft MVP (Excel)


"coolerthancool" wrote in message
...

How do I get excel to choose the second to last # in a series?
 

Before you protest any further, try the formula out that Ron posted (for the
numbers you are showing, the 2nd largest is the second to the last).

Rick


"coolerthancool" wrote in message
...
No. I don't want second largest. I want second to last.

If there are a series of numbers: 1, 2, 4, 5, 5

The second to largest is 5 because 5 and 5 are two separate #'s. I only
want to identify the second (or 3rd or 4th to last).

"Ron Coderre" wrote:

This formula returns the 2nd largest number from A1:A100
=LARGE(A1:A100,2)

Is that something you can work with?

Regards,

Ron
Microsoft MVP (Excel)


"coolerthancool" wrote in
message
...