How to Average the Last N Values in Excel

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. We can apply it to calculate the average of numbers from a given range reference. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. For example, get the average of sales in the nearest 3 days. In this article, we will let you know the method to apply AVERAGE function together with OFFSET function and COUNT function to get the average of last N values from a dynamic range.

In this article, we will introduce you the syntax, arguments, and basic usage of above three functions. We will also explain how the formula works with these functions.

EXAMPLE

How to Average the Last N Values 1

In this case, we want to calculate the average of the last 3 values in “Amount” column. If “Amount” range is a fixed range, we can directly create formula “=AVERAGE(C4:C6)” to get the average of last 3 values. If this range is a dynamic range, for example add a new line for date 5/31/2021, the last 3 values are changed due to a new row is inserted. So, the simple formula with only AVERAGE function doesn’t work.

To get the average of last N values for a dynamic range, we can apply AVERAGE function together with other functions to help us. In this article, to approach our goal, we also apply OFFSET function together with COUNT function to help us set our average range.

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(OFFSET(C1,COUNT(C:C),0,-3)), then press Enter, average of last three amounts is $6166.67.

How to Average the Last N Values 2

(5500+4000+9000)/3=6166.666666, keep two decimal places 6166.67. The formula works correctly.

FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Usage:

How to Average the Last N Values 3

OFFSET returns a number or a dynamic range based on given five parameters: starting point, row offset, column offset, height in rows and width in rows.

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Usage 1:

How to Average the Last N Values 4

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height & Width: optional, no value

Usage 2:

How to Average the Last N Values 5

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height: 2, it is used together with width, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

Width: 2, it is used together with height, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

COUNT returns the count of numbers from a given range.

Syntax:

 =COUNT(value1, [value2], …)

Usage:

How to Average the Last N Values 6

EXPLANATION

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-3))

In this formula, C:C is a full column reference, it represents the entire C column; if user want to cover entire C, D and E columns, we can enter C:E to represent multiple columns, just enter the start column index and end column index, use colon “:” to concentrate them. A full row reference is similar with a full column reference, for example, 1:3 means the first three entire rows. We often use full column or row reference when the selected range is a dynamic range, and user may add or delete column or row at any moment.

In this case, COUNT(C:C) returns 5, there are five cells with numbers in column C.

  How to Average the Last N Values 7

Now, for OFFSET part, the five inputs are:

Reference: C1 (header of column C, it will not be included when counting row and columns)

Rows: 5. 5 rows below the starting point. The returned range is started in row 6.

Cols: 0. No column to offset to the starting point.

Based on above “Rows” and “Cols” two parameters, we can confirm one point of our returned range, it is C6.

How to Average the Last N Values 8

Height: -3. It represents that the returned range is 3 rows in height. Currently, Excel OFFSET function can handle the case height and width are negative numbers. In this case, height value is “-3”, so from C6, OFFSET function expands the range 3 rows backwards to the starting point C1.

Width: No value. Width value is optional for OFFSET function. If it is omitted, the returned range is only limited in current column.

Based on above “Height” and “Width”, we can confirm the returned range, it is C4:C6.

How to Average the Last N Values 9

Now, =AVERAGE(OFFSET(C1,COUNT(C:C),0,-3)) is equal to =AVERAGE(C4:C6). After above steps, we get correct value 6166.67.

How to Average the Last N Values 10

SUMMARY

1. AVERAGE function is used for returning the average of some numbers from a given range in Excel.

2. OFFSET function returns a number or a dynamic range based on given five inputs. Actually, rows, cols, height and width can be input negative numbers.

3. COUNT function only returns count of numbers.

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
Related Posts
If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

Sort Positive Numbers and Negative Numbers by Absolute Values

If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way ...

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Sidebar