How to Average Last N Values in Multiple Columns

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. 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 in multiple columns.

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

Average Last N Values in Multiple Columns 1

In this case, we want to calculate the average of last 3 numbers in Row2. The most simple way, we can directly create formula “=AVERAGE(C2:E2)” to get the average as we are already clear that the last values are from range C2:E2. If we want to create a general formula that can cover “returns the average of last N value from multiple columns”, obviously the simple formula with only AVERAGE function cannot meet our demands.

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

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3)), then press Enter, average of last three numbers is 40.

Average Last N Values in Multiple Columns 2

(20+60+40)/3=40, the formula works correctly.

FUNCTION INTRODUCTION

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

Syntax:

=AVERAGE(number1, [number2], …)

Usage:

Average Last N Values in Multiple Columns 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:

Average Last N Values in Multiple Columns 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:

Average Last N Values in Multiple Columns 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: 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:

Average Last N Values in Multiple Columns 10

EXPLANATION

=AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3))

In this case, COUNT(A2:E2) returns 5, there are five cells with numbers in range reference A2:E2.

Average Last N Values in Multiple Columns 10

For OFFSET function in this case, the five inputs are:

Reference: A2. It is the starting point in row2.

Rows: 0. That means 0 row below the starting point. It determines the returned range reference is started in row2.

Cols: (COUNT(A2:E2)-3). Returned value is 2, that means 2 columns to offset to the starting point.

Based on above “Reference”, “Rows” and “Cols”, we can confirm the returned range is still stayed in row2, and two columns to offset to A2, so the first column for this range is C column, and the starting cell is C2.

Let’s verify if out thought is correct. Enter =OFFSET(A2,0,2) in B7, then press Enter, formula returns C2 value 20, see screenshot below.

Average Last N Values in Multiple Columns 10

Height: 1. It determines that the returned range is 1-row in height. We stayed in row2 as there is only 1-row in height.

Width: 3. It determines that the returned range is 3-columns in width. From C2, the returned range is expanded from C column to E column, total 3 columns.

 Based on above “Height” and “Width”, we can confirm the returned range is C2:E2. Enter =OFFSET(A2,0,2,1,3) into B6. As it is an array formula, so press Ctrl+Shift+Enter to get result.

Average Last N Values in Multiple Columns 10

Select {=OFFSET(A2,0,2,1,3)} in formula bar and press F9, {20,60,40} is displayed. {20,60,40} are the values in C2:E2.

Average Last N Values in Multiple Columns 10

Now, =AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3)) is equal to =AVERAGE(C2:E2).

Let’s see how the formula works step by step:

Step 1: Average Last N Values in Multiple Columns 11

Step 2: Average Last N Values in Multiple Columns 13

Step 3: Average Last N Values in Multiple Columns 13

Step 4: Average Last N Values in Multiple Columns 14

Above all, to get average from last N number in multiple columns, we can use below formula:

=AVERAGE(OFFSET(Starting Point,0,COUNT(Range)-N,1,N))

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 Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

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 ...

Sidebar