where “data” is the named range B5:B13. Note: this is an array formula, and must be entered with control + shift + enter. The ISNUMBER function returns TRUE for numeric values, and FALSE for other values (including blanks), and the ROW function returns row numbers, so the result of this operation is an array row numbers that correspond to numeric entries: This array goes into the LARGE function with the array constant {1,2,3} for k. LARGE automatically ignores the FALSE values and returns an array with the largest 3 numbers, which correspond to the last 3 rows with numeric values: This array goes into the LOOKUP function as the lookup value. The lookup array is provided by the ROW function, and the result array is the named range “data”: LOOKUP then returns an array containing corresponding values in “data”, which is fed into AVERAGE:
Handling fewer values
If the number of numeric values drops below 3, this formula will return the #NUM error since LARGE won’t be able to return 3 values as requested. One way to handle this is to replace the hard-coded array constant {1,2,3} with a dynamic array created using INDIRECT like this: Here, MIN is used to set the upper limit of the array to 3 or the actual count of numeric values, whichever is smaller. Note: I ran into this clever approach over on chandoo.org, in a reply by Sajan to a similar question.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
