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

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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
 
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).
 
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
 
Back
Top