ExcelBanter - View Single Post - Return last and second last "populated" cell in column

Steve Nickerson

Quote:

Originally Posted by Domenic[_2_]

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