The best solution depends on the version of Excel you have available.
Both approaches are explained below.
ISNUMBER returns TRUE for numeric values and FALSE for anything else.
Variable n
The generic form of this formula wherenis a variable is shown below.
To change the number of numeric values being averaged, just changento a different number.
In other words, we are asking TAKE for the lastncolumns instead of the lastnrows.
One option is to use a formula like this:
Notice theAVERAGE functionis the outermost function in the formula.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
Related videos
FILTER function basic example
