Return last and second last "populated" cell in column

G

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

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

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

Domenic

Grant Tucker said:
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.
Click to expand...


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
find last populated cell in range of cells 6
Return column header based on last value in row 3
Formula to choose from the last column of a given row 2
Find Last Item Entered in Row 4
How do I add up the last 12 cells (containing values) in a column. 4
Find second to last value in cell 4
Extract first value and last value from a table 1

Top