Excel: Last Entry

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

So to start with I needed to know how many entries were in the list.  The COUNTA function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The OFFSET function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1. The function we would use is simply

=OFFSET(A1,COUNTA(A:A)-1,0,1,1)

However this does give us a #REF! if the list has no entries - which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an IF.

=IF(COUNT(A:A)=0,"",OFFSET(A1,COUNTA(A:A)-1,0,1,1))
Author: Stephen Millard
Tags: | excel |

Buy me a coffeeBuy me a coffee



Related posts that you may also like to read