Return last and second last "populated" cell in column

  • Thread starter Thread starter Grant Tucker
  • Start date Start date
G

Grant Tucker

Display last and second last values from "populated" cells in a column. Say
A1:A10 with results in A11 and A12.

This could apply where you want to generate the number of hours of work in
each of the last two working weeks in a 10 week period. Not all 10 weeks will
have hours; some will be blank. The formula should remain true in another 10
week period with different weeks being blank. I have formulas which work well
in Google Docs Spreadsheet but I have not been able to modify them to make
them compatible with Excel.
 
To return last .. format the cell to hours..(if applicable)
=LOOKUP(10^10,A1:A10)

'second last..Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=LOOKUP(LARGE(IF(ISNUMBER(A1:A10),ROW(A1:A10)),2),ROW(A1:A10),A1:A10)
 
It sounds like you have numeric data in the range.

For the last number in the range:

=IF(COUNT(A1:A10),LOOKUP(1E100,A1:A10),"")

For the next to last number in the range:

Array entered** :

=IF(COUNT(A1:A10)>1,INDEX(A:A,LARGE(IF(A1:A10<>"",ROW(A1:A10)),2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Grant Tucker said:
Display last and second last values from "populated" cells in a column. Say
A1:A10 with results in A11 and A12.

This could apply where you want to generate the number of hours of work in
each of the last two working weeks in a 10 week period. Not all 10 weeks will
have hours; some will be blank. The formula should remain true in another 10
week period with different weeks being blank. I have formulas which work well
in Google Docs Spreadsheet but I have not been able to modify them to make
them compatible with Excel.
Click to expand...


Here's another way...

A11:

=LOOKUP(9.99999999999999E+307,A1:A10)

A12:

=LOOKUP(9.99999999999999E+307,A1:INDEX(A1:A10,MATCH(9.99999999999999E+307
,A1:A10)-1))
 
Back
Top