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

  • 2
    I had to look up "penultimate". – Hello71 Jan 26 '11 at 21:53
  • 3
    @Hello, sorry about that :) - for anyone else who doesn't want to look it up, penultimate is "the one before the last one". – DMA57361 Jan 26 '11 at 21:57

This works with both text and numbers and also doesn't care if there are blank cells, i.e., it will return the 2nd to last non-blank cell. It needs to be array-entered, meaning that you press ctrl-shft-enter after you type or paste it in. Adjust the last argument to change the offset:

  • Ah ha, now that's good - it's not overly complex, handles arbitrary offsets and handles the exceptional case where there's gaps in the data set. – DMA57361 Jan 28 '11 at 8:26

Assuming there are no empty cells:



INDEX returns a certain value within a range by specifying:

  • array: an arbitrary range, or the whole row (1:1)
  • row_num: the row number within the range (1)
  • column_num: the column number within the range. That's the tricky part.

In my example, I used COUNT() to count how many numeric entries you have in the row, which is 7. COUNTA() would also include your first non-numeric row "Data" and return 8, so you have to substract 1.

Note that this won't work if any cell is left blank. Rather than leaving a cell blank, you could put a dash (-) to indicate an empty entry and use COUNTA().

BONUS: Get the 5th last entry. Easy:

=INDEX(1:1,1,COUNT(1:1)-3) or INDEX(1:1,1,COUNTA(1:1)-4)

  • 1
    +1 for using INDEX instead of OFFSET and for mentioning 1:1 – Hello71 Jan 27 '11 at 22:36

This might get you close to what you want - the negative number at the end adjusts the offset to the cell whose value is returned.


This is an array formula so you have to press CTRL + SHIFT + ENTER to enter it

  • 2
    Works, but I don't think INDEX has to be used as an array formula, it worked as expected when just entered normally. – DMA57361 Jan 26 '11 at 22:29

You should be able to do something like this:

=OFFSET(A1, 0, COUNTA(A1:I1) - 2)

Note: Code not tested, could raise an error. Also, if you want to count cells with formulas that return empty:


Source for replacement COUNTA: http://excel.tips.net/Pages/T002996_Counting_NonBlank_Cells.html

Same warning as above.

  • 2
    Great COUNTBLANK suggestion. – mtone Jan 26 '11 at 22:25
  • FYI, =OFFSET(A1, 0, COUNTA(A1:I1) - 1) returns the last item, you need to use -2. – DMA57361 Jan 26 '11 at 22:29

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.