Calculate Average Of Last 5 Or N Values In Columns in Google Sheets

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do?

If you are new to google sheets, then your first attempt might be doing this task manually, which is an acceptable way but only when the values of which you want to calculate the average is limited to 2 or 3 but when it comes to calculating the last 5 or N values in the columns then it becomes nearly impossible to do this cumbersome task on time!

Don’t worry about it because after reading this article, you will know the easiest way to calculate the average of the last 5 or N values from the columns within seconds.

So, let’s dive into the article.

Average last N values in columns in google sheets1

General Formula in google sheets

To get the average of the last N values in google sheets, use the formula below.

=AVERAGE(OFFSET(first cell,0,COUNT(range_values)-N,1,N))

Explanations For Syntax:

  • AVERAGE: In google sheets, the AVERAGE Function may be used to calculate the arithmetic mean of a set of integers.
  • OFFSET: This Function outputs a reference to a range made up of pieces such as a beginning point, a row, and column offset, and a final height and width in rows and columns. Learn more about the OFFSET Function.
  • COUNT: The COUNT Function returns the result as a number and counts the number of cells that contain numbers.
  • First Cell: It indicates the first cell in the provided input range.
  • Range: This is the input value from your google spreadsheet.
  • The comma sign (,): is a separator that separates a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • Minus Operator (-): This symbol subtracts 2 values.

Explanation

To average the latest 5 data values in a range of columns in google sheets, use the AVERAGE Function conjunction with the COUNT and OFFSET functions. The formula in I2 in the example is as follows:

=AVERAGE(OFFSET(A2,0,COUNT(A2:G2)-5,1,5))

  Average last N values in columns in google sheets1

The OFFSET function may create dynamic ranges from a beginning cell and specify rows, columns, height, and width.

The rows and columns parameters act as “offsets” from the beginning reference. The height and width parameters, all optional, decide how many rows and columns are included in the final range. We want the OFFSET function to return a range that starts at the last item and grows “backward,” therefore we provide the following arguments:

A2 is the initial reference — it is the cell directly to the right of the formula and the first cell in the range of values we are dealing with.

Rows – We use 0 for the rows option because we want to stay in the same row.

Columns – We use the COUNT function to count all values in the range for the columns input, then deduct 5. This moves the start of the range of 5 columns to the left.

Height – we use 1 since we want a 1-row range as the end result.

Width – we pick 5 since we want a final range with 5 columns.

For the formula in I2, OFFSET yields a final range of C2:G2. This is sent to the AVERAGE Function, which returns the average of the five values in the range.

To understand better, consider the following example, which follows a step-by-step procedure:

  • Consider the following example to calculate the average of the last 5 values.
  • This illustration will provide input data from Column A to Column G.
  • Next, enter the supplied formula in the formula bar section.
  • Finally, we shall obtain the result in the selected cell I2.

Less Than 5 Values Average in google sheets

If you are want to compute the average of the last 2, 3, or 4 data in google sheets, you don’t need to do it manually. Utilizing the formula discussed above will result in a circular reference mistake when computing the average of the 2, 3, or 4 values in the columns; the range will extend back into the cell that contains the formula. To avoid this mistake and complete your task, modify the formula as follows:

=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))

So If you want to average last N values in columns that less than 5 values in google sheets, you can use the following formula:

=AVERAGE(OFFSET(A2,0,COUNT(A2:C2)-MIN(5,COUNT(A2:C2)),1,MIN(5,COUNT(A2:C2))))

Average last N values in columns in google sheets1

In this case, we utilize the MIN function to “catch” cases where there are fewer than 5 values and the real count when there are.

Related Functions

  • Google Sheets AVERAGE function
    The Google Sheets AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Google Sheets COUNT function
    The Google Sheets  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…
  • Google Sheets OFFSET function
    The Google Sheets OFFSET function returns a range reference shifted a specified number of rows and columns from a starting cell reference.The syntax of the OFFSET function is as below:= OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])…
  • Google Sheets MIN function
    The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Google Sheets and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

 

 

Related Posts

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets
Average the last 3_ 5 or N numeric values in google sheets1

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Google Sheets MIN Function
google sheets MIN function1

This post will guide you how to use Google Sheets MIN function with syntax and examples in Google Spreadsheet. Description The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Count Cells that are Case Sensitive in Google Sheets
counting the cells that are case sensitive in google sheets1

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Assigning Points based on Late Time in Google Sheets
assign points based on late time in google sheets1

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Sidebar