If you want to determine the last relative position for a mixed data that has no empty cells then the Excel built-in function called the COUNTA can be used to perform this operation.
Note on last *relative * position
- When you need to create more advanced formulas, then it is always to find the last location of the data in the specified list.
- Depending on data, the last location can be the last column containing data or last row containing data or intersection of them.
- Also, note that we are always after the last “relative position “ in a given range and not in the row number on the spreadsheet.
How does the Generic formula work?
- The COUNTA built-in function of Excel is usually used to count values in a range. Therefore this function can count both numbers and texts so it goes well with the mixed data.
- Note that this approach will not pass if the range consists of blank cells.
To construct a dynamic range we can use this formula with OFFSET and INDEX function.
Let us consider a list of games (texts) and numeric values such as dates, numbers etc. The Generic formula with the range parameter contains the range of cells from A2 to A50. Let the last value entered be at A8. Therefore, when used this range inside a COUNTA function, generates the last relative Position I.e,7, as shown In the diagram below.
Figure 1. The COUNTA function used to return the relative position of the last row