Imagine I have a row of data in excel, like this:
[A] [B] [C] [D] [E] [F] [G] [H]
[1] Data 5 8 12 32 15 9 89
I want a formula that will return the value 9
.
Then, if I add a new value to I1
, it should update to return the value 89
, which would be the new penultimate value.
Ideally this should work on arbitrary ranges, not just entire rows.
Optional bonus points if the method can return an arbitrary offset from the end of the row (say to return the 5th from last item).