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

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to Create Gantt Chart in Excel
  • Excel Line Chart
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to calculate correlation coefficient Correlation in Excel

References

  • How to use Excel TRANSPOSE Function
  • How to use Excel MMULT Function
  • Two-way lookup with VLOOKUP in Excel
  • Count rows that contain specific values in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only
  • Excel Data validation date in next 30 days
  • Excel Data validation specific characters 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:

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.

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

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:

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

  • Invoice status with nested if in Excel
  • OR function Examples in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • Extract multiple matches into separate rows in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to calculate next scheduled event in Excel
  • Display Date is same month in Excel
  • Add months to date in Excel
  • Calculate days remaining in Excel
  • Get days between dates ignoring years in Excel

Grouping

  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel

General

  • Sum by group in Excel
  • AutoRecover file that was never saved in Excel
  • How to get amount with percentage in Excel
  • Check if multiple cells have same value in Excel
  • Customize Ribbon In Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning