Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Randomize/ Shuffle List in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Get column index in Excel Table
  • Example of COUNTIFS with variable table column in Excel
  • How to Use Solver Tool in Excel

References

  • How to use Excel MATCH Function
  • Multi-criteria lookup and transpose in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • Count rows with at least n matching values
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel
  • Excel Data validation must not contain

Average the last 3 numeric values in Excel

by

This tutorial shows how to work  Average the last 3 numeric values in Excel using the example below;

Formula

{=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))}

Explanation

To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example shown, the formula in D6 is:

{=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}), ROW(data), data))}

where “data” is the named range B5:B13.

Note: this is an array formula, and must be entered with control + shift + enter.

How this formula works

The AVERAGE function will calculate an average of numbers presented in an array, so almost all the work in this formula is to generate an array of the last 3 numeric values in a range. Working from the inside out, the IF function is used to “filter” numeric values:

IF(ISNUMBER(data),ROW(data))

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:

{5;6;FALSE;8;9;10;FALSE;12;13}

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:

{13,12,10}

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({13,12,10}, ROW(data), data))

LOOKUP then returns an array containing corresponding values in “data”, which is fed into AVERAGE:

=AVERAGE({100,92,90})

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:

ROW(INDIRECT("1:"&MIN(3,COUNT(data))))

Here, MIN is used  to set the upper limit of the array to 3 or the actual count of numeric values, whichever is smaller.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • How to use Excel TRUE Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert date to month and year in Excel
  • How to get Holiday Date from Year in Excel
  • Get days, months, and years between dates in Excel
  • Calculate expiration date in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers with VLOOKUP in Excel

General

  • List sheet names with formula in Excel
  • Mark Workbook as Final in Excel
  • Automatically fill series of cells in Excel using AutoFill
  • How to calculate percentage of total in Excel
  • How to choose page/paper size in Excel before Printing
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning