Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
2  
I had to look up "penultimate". –  Hello71 Jan 26 '11 at 21:53
2  
@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
    
@DMA57361 -or the one after the antepenultimate one ;-) –  pnuts Dec 19 '12 at 12:22
add comment

4 Answers 4

up vote 3 down vote accepted

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:

=INDEX(1:1,LARGE((1:1<>"")*(COLUMN(1:1)),2))
share|improve this answer
    
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
add comment

Assuming there are no empty cells:

=INDEX(1:1,1,COUNT(1:1))

=INDEX(1:1,1,COUNTA(1:1)-1)

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)

share|improve this answer
    
+1 for using INDEX instead of OFFSET and for mentioning 1:1 –  Hello71 Jan 27 '11 at 22:36
add comment

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.

=INDEX(A1:Z1,COUNTA(A1:Z1)-0)

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

share|improve this answer
1  
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
add comment

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:

=OFFSET(A1, 0, COLUMNS(A1:I1) - COUNTBLANK(A1:I1) - 2)

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

Same warning as above.

share|improve this answer
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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