Return last and second last "populated" cell in column
 

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.

Return last and second last "populated" cell in column
 

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)

--
Jacob


"Grant Tucker" wrote:

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.

Return last and second last "populated" cell in column
 

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.

--
Biff
Microsoft Excel MVP


"Grant Tucker" <Grant wrote in message
...
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.

Return last and second last "populated" cell in column
 

In article ,
Grant Tucker <Grant wrote:

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,MATC H(9.99999999999999E+307
,A1:A10)-1))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Domenic,

Great formula! I'm using it to capture a daily production statistic for the past 6 days. Can you make it work when the cell is blank or contains a 0 (zero)?

Steve