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

Lookup and Reference Examples

  • How to get last column number in range in Excel
  • Two-column Lookup in Excel
  • Basic INDEX MATCH approximate in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Perform case-sensitive Lookup in Excel

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • How to Sort by Color in Excel
  • How to Create Thermometer Chart in Excel
  • How to create dynamic reference table name in Excel
  • Reverse List in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation whole percentage only
  • How To Create Drop-down List in Excel
  • Excel Data validation unique values only
  • Excel Data validation no punctuation

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:   Count visible rows only with criteria 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:   Sum range with INDEX 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:   9 Mathematical Computations Example using Excel Statistical Function

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
  • Join cells with comma in Excel
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Remove file extension from filename in Excel
  • Calculate number of hours between two times in Excel
  • Get project end date in Excel
  • Sum race time splits in Excel
  • Check If Two Dates are same month in Excel
  • Get first day of month in Excel
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles