Google Sheets – Formula to Find Second to Last Cell with Value

formulasgoogle sheetsgoogle-sheets-arrayformula

I have a spreadsheet that I used to run in Excel but due to other family members needing access to it I've moved it over to Sheets.

Anyway, I have the following formula that was running in Excel that I need to replicate in Google Sheets:

=INDEX(D6:D1500,AGGREGATE(14,6,(ROW(D6:D1500)-ROW(D6)+1)/(D6:D1500<>""),2))

Basically I have a series of sheets in a spreadsheet that contain data about various reptiles that we keep (snakes, geckos, lizards).
At the very front of the spreadsheet is a summary page so I can look at key data at a glance.
Anyway, one of the new bits of key data I want to add to the summary page is their weight from the previous weigh in so at a glance I can see how they are doing.
So, in order to do this I was hoping on each animals individual page that I could add a formula to a particular cell (the one above) which looked at the column of weights but only returned the second weight up from the bottom of the column.

If anyone can help to get this to work in Google Sheets it would be much appreciate.

Stay safe in these awkward times.

Link to a stripped down version of the spreadsheet as follows.

Jeffery

Best Answer

Edited answer

(following OP's comment)

Your formula works a treat but I failed to say that the column of numbers will contain empty cells - is it possible to tweak your formula so that it ignores empty cells?

=INDEX(D:D,LARGE(IF(D:D<>"",ROW(D:D)),2))

The content of the second to last row with value in range D:D is provided by the above formula.
For the 3rd up change 2 to 3, the 4th 2 to 4, etc.

Original request You said:

add a formula to a particular cell (the one above) which looked at the column of weights but only returned the second weight up from the bottom of the column.

=indirect("A"&max(ARRAYFORMULA(row(A:A)*--(A:A<>"")-1)))

The above formula will return the content/value of the second cell up from the bottom of column A. You can place it wherever you like. It doesn't even have to be in column A.

enter image description here

Related Topic