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)

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

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.

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