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

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

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

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

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

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.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.9.23.1885\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Super User works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </noscrip"], "copyright__copyright__copyright": ["e not tested, could raise an error. Also, if you want to count cells with formulas that return empty:\n\n=OFFSET(A1, 0, COLUMNS(A1:I1) - COUNTBLANK(A1:I1) - 2)\n\nSource for replacement COUNTA: http://excel.tips.net/Pages/T002996_Counting_NonBlank_Cells.html\n\nSame warning as above.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}