Excel formula to get the penultimate value in a row

Solution 1:

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

Solution 2:

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)

Solution 3:

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