Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel VLOOKUP Function
  • Approximate match with multiple criteria in Excel
  • How to get relative row numbers in a range in Excel
  • How to get first row number in range in Excel
  • Vlookup Examples in Excel

Data Analysis Examples

  • How to Create Area Chart in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Create Scatter Chart in Excel
  • How to add Trendline to a chart in Excel
  • Example of COUNTIFS with variable table column in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only
  • How To Create Drop-down List in Excel
  • Excel Data validation with conditional list
  • Excel Data validation allow weekday only

Average last 5 values in columns in Excel

by

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

Formula

=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))

Explanation

To average the last 5 data values in a range of columns, you can use the AVERAGE function together with the COUNT and OFFSET functions. In the example shown, the formula in F6 is:

=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))

How this formula works

The OFFSET function can be used to construct dynamic ranges using a starting cell, and given rows, columns, height, and width.

The rows and columns arguments function like “offsets” from the starting reference. The height and width arguments, both optional, determine how many rows and columns the final range includes. We want OFFSET to return a range that begins at the last entry and expands “backwards” so we supply arguments as follows:

Worked Example:   Average last 5 values in Excel

reference – the starting reference is D5 – the cell directly to the right of the formula, and the first cell in the range of values we are working with.

rows – we use 0 for the rows argument, because we want to stay in the same row.

Worked Example:   How to create dynamic named range with OFFSET in Excel

columns – for the columns argument, we use the COUNT function to count all values in the range, then subtract 5. This shifts the beginning of the range 5 columns to the left.

height – we use 1 since we want a 1-row range as the final result.

width – we use 5, since we want a final range with 5 columns.

For the formula in C5, OFFSET returns a final range of F5:J5. This goes into the AVERAGE function which returns the average of the 5 values in the range

Worked Example:   Offset in Excel

Less than 5 values

If there are less than 5 values, the formula will return a circular reference error, since the range will extend back into the cell that contains the formula. To prevent this formula, you can adapt the formula as follows:

=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))

Here we use the MIN function to “catch” situations where there are less than 5 values, and use the actual count when there are.

Post navigation

Previous Post:

Average last 5 values in Excel

Next Post:

Average numbers 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
  • Complete List of Excel Text Functions References and Examples
  • T function: Description, Usage, Syntax, Examples and Explanation
  • Remove text by position in a cell in Excel
  • Get first name from name with comma — Manipulating NAMES in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • Convert Excel time to decimal seconds
  • How to calculate workdays per month in Excel
  • Pad week numbers with zeros in Excel
  • Next biweekly payday from date in Excel
  • Two ways to sum time over 30 minutes in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles