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

R

Ron Coderre

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)
 
R

Ron Rosenfeld

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


"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
 
C

coolerthancool

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).
 
R

Rick Rothstein \(MVP - VB\)

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
 
Top