Simple Enquiry with Complex Answer - How do I Select RowA6-Row(last non-blank) for a simple formula

Question

I have many columns all labeled with many many values underneath, which can be words or numbers

Here is the current equation =INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0))) I have this on the in cell 5 of each column.

The number of values in each column may increase or decrease. If i reference the entire column (until the end of the worksheet) the blank spaces interfere with an accurate output.

How do I reference cell A6 to Last Non-Blank

Answer 1

You need to determine the row of last non-blank cell in the column. The method for this would depend on whether there are blank cells in the middle, for instance.

Two alternatives are (taken from here*):

=SUMPRODUCT(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))))
=INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0)

Then you can use this value with OFFSET to get a reference to the target cell. So your range will be (using the second form)

A6:OFFSET(AK1,INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0)-1,0)

This expression will be embedded in a formula.

Notes:

1. You may have to change absolute/relative references.
2. Depending on the formula you embed the expression in, I foresee you might need to enter your formula as an array formula, with Ctrl+Shift+Enter.
3. *This aims at getting the last non-blank value instead of a reference to the cell, but some of the results posted are useful.

Answer 2

Would counting the non blank cells work, then use offset to move that number of rows. Have a look at this: MATCH(1,A6:OFFSET(A6,COUNTIF(A6:A600,">0"),)) the offset & count resolve to complete A6:A14 on my simple test sheet.

Answer 3

One option is to increase the number of rows in the formula to be as high as you might need, and add an extra IF function in the formula to handle blanks, e.g. this version will allow you up to 995 rows of data

=INDEX(AK6:AK1000,MODE(IF(AK6:AK1000<>"",MATCH(AK6:AK1000,AK6:AK1000,0))))

.....but will still work if you have fewer rows and blanks in that range

confirm with CTRL+SHIFT+ENTER

Answer 4

There are much more efficient - and non-volatile - set-ups available for determining the last non-blank cell in a range than, for example, the SUMPRODUCT/MAX one given by sancho.s, though only if the blank cells within that range are all "genuine" blanks, and not the null string "" e.g. as a result of formulas in those cells.

If this can be guaranteed, then, for a range containing mixed datatypes (some text, some numerics) you can use:

=MAX(MATCH(REPT("z",255),A:A),MATCH(9.9E+307,A:A))

which will be far more efficient than any solution (such as the SUMPRODUCT/MAX set-up) which tests each individual cell within the specified range as to whether it is blank or not.

What's more, the above construction can reference the entirety of column A with no detriment to calculation speed, thus eliminating the need to select a limited range. (Note that using the same range, i.e. A:A, within SUMPRODUCT (or any other array formula) would not at all be a good idea, since this would be forcing Excel to calculate more than a million cells individually, leading to noticeably slower workbook performance).

As for forming a dynamic range, I'm constantly surprised that so many sources around the internet continue to advocate set-ups involving volatile functions such as OFFSET and INDIRECT (I've even seen several sites using ADDRESS for this purpose), especially when there is a perfectly good non-volatile (actually, not fully non-volatile, but near enough) INDEX set-up available, viz:

AK6:INDEX(A:A,LastRow)

where LastRow is a Defined Name given the formula I posted above.

Regards