Simple Query with Complex Answer - How to choose RowA6-Row (last non-empty) for simple formula
I have many columns, all labeled with many values which can be words or numbers
Here is the current equation =INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0)))
I have this in cell 5 of each column.
The number of values in each column can increase or decrease. If I refer to the entire column (to the end of the worksheet), spaces get in the way of the exact output.
How to reference a cell A6 to Last Non-Blank
source to share
You need to define the row of the last nonblank cell in the column. The method for doing this will depend on whether there are empty cells in the middle for example.
Two alternatives (taken from here *):
=SUMPRODUCT(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))))
=INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0)
You can then use that 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 nested in the formula.
Notes:
- You may need to change absolute / relative links.
- Depending on the formula you are inserting into the expression, I foresee that you may need to enter the formula as an array formula using Ctrl + Shift + Enter.
- * This aims to get the last non-blank value instead of a cell reference, but some of the posted results are helpful.
source to share
There are more efficient and non-volatile settings for determining the last nonblank cell in a range than, for example, SUMPRODUCT / MAX given by sancho.s, although only if the blank cells in that range are "genuine" spaces and not a null string "such as the result formulas in these cells.
If this can be guaranteed, then for a range containing mixed data types (some texts, some numbers), you can use:
=MAX(MATCH(REPT("z",255),A:A),MATCH(9.9E+307,A:A))
which will be much more efficient than any solution (like the SUMPRODUCT / MAX setting) that checks every single cell in the specified range as to whether it is empty or not.
, , . ( , , A: A, SUMPRODUCT ( ) , Excel , ).
In terms of dynamic range shaping, I am constantly amazed that so many sources on the Internet continue to promote settings that include volatile features like OFFSET and INDIRECT (I've even seen a few sites use ADDRESS for this purpose), especially when there is a perfectly good non-volatile ( in fact, not completely non-volatile, but close enough) the INDEX setting, namely:
AK6:INDEX(A:A,LastRow)
where LastRow is the designated name based on the formula posted above.
Hello
source to share
One option is to increase the number of lines in the formula, which should be as high as you might need, and add an additional formula IF
to the formula to handle spaces, for example. this version will allow you up to 995
data lines
=INDEX(AK6:AK1000,MODE(IF(AK6:AK1000<>"",MATCH(AK6:AK1000,AK6:AK1000,0))))
..... but will work if you have fewer lines and spaces in this range
confirm with CTRL+ SHIFT+ENTER
source to share