finding the final cell with a value in a specific column?

Hi Trey,

More precise detail needed in your description:

Your subject line says "the final cell with a value," implying there may be empty cells above or below the target cell.

In the body of your message you say, "the final cell in a column."

These specifications could cover at least three different situations, each of which would require a different technique to locate the target cell and return it's stored value.

Can you give a more precise description of the possible cases?

Regards,

barry

Create a column next to "O" - column "P". Enter 0 in P1.

Enter =IF(ISBLANK(O2),"",P1+1) in P2. Fill down as far as you need to go. This will give a column of numbers that increments by 1 and stops at the first blank cell.

Enter =MAX(P1:P100) to find the highest number in this column.

Use that number in your lookup formula to find the corresponding number in column "O".

Hope that helps! (Barry will probably have a more elegant solution!)

Trey,

Here's a method...

One Header Row, one Header Column, one Footer Row.

Expression: =OFFSET(B1, COUNT(B), 0) for last value in Column B.

Regards,

Jerry

Trey,

Thanks for the feedback. Glad that solved your problem.

Jerry

Hi Trey,

Thanks for the clarification of the issue. Once all the details were there, I was sure the solution would fall into place before long, probably before I returned from my 'afternoon chores.'

Given that every cell in column O that is above the target cell contains a numerical value, Jerry's solution and mine would be essentially identical.

But the comment in your reply to John, "the first 4 games don't have a rolling 5 game average, so right from the get go, it's blanking out," leads me to wonder if these first four rows also throw Jerry's count off?

Taking Jerry's example, and moving the supplied data down to leave four empty cells (representing the first four games, where there's yet to be a rolling average), I get this result:

Data table on the left. Summary table on the right with Jerry's unmodified formula in A2

Count & Offset::A2: =OFFSET(Data :: B1,COUNT(Data :: $B),0)

The formula correctly counts the cells containing numerical data in column B of the Data table (5), then returns the result from the cell that is offset 5 rows (down) and 0 columns (right) from the top cell in that column (B1). The result is the 5 from the yellow-filled cell. The expected result is the 2 in the green-filled cell, four rows below.

One way to make the formula return the value from the correct cell is to enter (countable) dummy data into the four empty cells (B2::B5). Another is to revise the formula in one of the two manners below.

In Count & Offset::B2, the formula is revised to include a constant (4) to account for the four empty cells before the first rolling average:

Count & Offset::B2: =OFFSET(Data :: B1,COUNT(Data :: $B)+4,0)

In Count & Offset::C2, OFFSET's base cell is changed to reference the cell above the one containing the first rolling average:

In Count & Offset::C2 =OFFSET(Data :: B5,COUNT(Data :: $B),0)

Regards,

Barry

I knew there would be a more elegant solution. Thanks!

Hi John,

You've got the start of a working solution there. You first formula creates an index, and the second (not shown in your post) uses the maximum value as a search-for value to find the target cell in column O.

It's main problem is that the arithmetic operators, +, -, * and /, choke on non-numeric values, so as Trey noted, the formula fails on the first rows where column O contains empty cells. Instead of using + and referencing a specific cell (which might contain text—including the possibility of a null string), use MAX, which treats text values as zeros.

P2 (original): =IF(ISBLANK(O2),"",P1+1)

P2 (revised): =IF(ISBLANK(O2),"",MAX($P$1:P1)+1)

Here's the result, using mostly the data from the previous example, but changing one of the 'averages' to a zero, placing a null string in the next row, and leaving the next cell empty. (See the labels in column A) Cell references in the formulas above have been changed to use the data column (B instead of O) and Index column (C instead of P) in the sample table. The LOOKUP formula is shown at the top of the image.

This method is a bit more robust than the one using COUNT and OFFSET in that it will return the value from the "last cell in the column containing a value" no matter what type of value that cell contains, and without regard to whether cells above it are empty or contain values of any type.

For Trey's case, where no cells other than the known 4 at the beginning are empty of contain non-numeric values, this method will work, but provides no visible advantage over using COUNT and OFFSET.

Regards,

Barry

Here's a fairly simple equivalent for ignoring blanks and null strings, I believe:

In Column C: =IF(LEN(B)>0, ROW(),"")

In Footer of Column B: =OFFSET(B1, MAX(C)-1, 0)

Regards,

Jerry

Thanks a lot. Was really helpful. 🙂