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

Data Analysis

  • How to count table columns in Excel
  • Managing Conditional Formatting Rules in Excel
  • Subtotal function in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Understanding Anova in Excel

References

  • Get nth match with INDEX / MATCH in Excel
  • How to use Excel MMULT Function
  • Offset in Excel
  • Extract all partial matches in Excel
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation unique values only
  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total

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

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel NOT Function
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function

Date Time

  • Add months to date in Excel
  • Get day name from date in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • How to show last updated date stamp in Excel
  • Get work hours between dates in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Map text to numbers in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Count cells that do not contain errors in Excel
  • Check if range contains a value not in another range in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
  • Hide and Unhide Columns or Rows in Excel
  • How to increase by percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning