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

Data Analysis

  • How to calculate current stock or inventory in Excel
  • Error Bars in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to create Checklist in Excel

References

  • Basic INDEX MATCH approximate in Excel
  • How to get last column number in range in Excel
  • Left Lookup in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Vlookup Examples in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation don’t exceed total
  • Excel Data validation unique values only
  • Excel Data validation date in specific year

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.

Worked Example:   How to add sequential row numbers to a set of data in Excel

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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

  • How to use Excel FALSE Function
  • How to use Excel AND Function
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Extract multiple matches into separate rows in Excel
  • How to use Excel NOT Function

Date Time

  • Basic Overtime Calculation Formula in Excel
  • Count birthdays by month in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Assign points based on late time in Excel
  • How to calculate quarter from date in Excel

Grouping

  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to calculate percent of goal in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • Basic error trapping example in Excel
  • How to calculate percentage of total in Excel
  • Zoom Worksheet in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning