Sunday, January 4, 2009

Finding the Last Value in a Table Column Range in Excel 2007

I was trying to accomplish something I thought would be pretty simple in Excel, finding the last value in a row within a range in Excel.

I was using a balance column and just wanted the last value (the balance) to show up in my totals row at the bottom, thereby when I filtered the list the value would still be the outstanding balance for the whole list.

There were a couple of options I found through Googling, but I didn't like any of them as they didn't work well with blank rows.

My resolution was to format the range of cells as a Table with the "Format as Table" option in Excel 2007.

By doing this it creates ranges for each column. My table looks like this:

To get the last value in (C7) for this table I used this formula:

=INDEX([Balance], ROWS([Balance]),1)

By using the Rows function here it gives me the last row within the range I am looking for and then the index provides the value.