Find the last, 2nd last and 3rd last data in a column

Find the last, 2nd last and 3rd last data in a column

Post by jamiejgas » Wed, 31 Dec 2008 04:37:09


I have data listed in several colums and need to create a formula to
detail the last (most recent) 2nd last and 3rd last piece of data in
the column.

I have used the following formula to display the last but cant edit
this or create another formula to get the 2nd and 3rd last pieces of
data.

=LOOKUP(2,1/(A:A<>0),A:A)

Thanks in advance

Jamie
 
 
 

Find the last, 2nd last and 3rd last data in a column

Post by Bernard Li » Wed, 31 Dec 2008 05:02:18

If there are no empty cells in the range:
last cell: =INDEX(A:A,COUNTA(A:A))
penultimate: =INDEX(A:A,COUNTA(A:A)-1)
semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://www.yqcomputer.com/
remove caps from email

 
 
 

Find the last, 2nd last and 3rd last data in a column

Post by jamiejgas » Wed, 31 Dec 2008 07:43:09

On 29 Dec, 20:02, "Bernard Liengme" < XXXX@XXXXX.COM >




My coloumns do have blanks, is there any function that can ignore
blank/empty cells.

Cheers Jamie
 
 
 

Find the last, 2nd last and 3rd last data in a column

Post by T. Valk » Wed, 31 Dec 2008 08:45:07

Try this array formula** :

Entered in a cell then copied down this will return in order: last, 2nd to
last, 3rd to last items. No error checking!

With data in the range A1:A15 and the array formula** entered in C1 and
copied down:

=INDEX(A$1:A$15,LARGE(IF(A$1:A$15<>"",ROW(A$1:A$15)-MIN(ROW(A$1:A$15))+1),ROWS(C$1:C1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP