2

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

2

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

| improve this answer | |
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Keep healthy. – marikamitsos Apr 12 at 8:59
  • Morning Marikamitsos. First of all thank you very much for taking the time to look into my query. 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? – Torcrayth Apr 14 at 6:40
  • Hi. Please accept the answer and even upvote it, since the formula answers your original question. As for the follow-up ("...I failed to say that the column of numbers will contain empty cell..."), please read how to and share a test sheet so as you can be easier helped. – marikamitsos Apr 14 at 6:54
  • Further to the suggestion above, I've now added a link to a stripped down version of the sheet in the original post above as I don't see how to add a link in these smaller comment boxes (I'm new to stack exchange so still trying to find my way around it). The formula I'm looking for would be used in cell O1 and it would look up column D for snake weights) – Torcrayth Apr 14 at 7:34
  • 1
    I've edited the title making it more "user friendly". I you disagree please feel free to change it back. – marikamitsos Apr 14 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.