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
Best How To :
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*):
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)
This expression will be embedded in a formula.
- You may have to change absolute/relative references.
- 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.
- *This aims at getting the last non-blank value instead of a reference to the cell, but some of the results posted are useful.