Return last and second last "populated" cell in column

[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.

 

[Jacob Skaria]

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)

 

[T. Valko]

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.

 

[Domenic]

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.

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