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