Average Last N Values in Google Sheets

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, and you just want the average result to be updated automatically when new data is entered. This means you would like the average to always represent the latest N values in your data list, regardless of whether or not you add new data points.

Average last 5 values in google sheets1

The Generic formula is as follow:

=AVERAGE(OFFSET(C2,COUNT(C:C)-N_Values,0,N_Values))

Summary 

The AVERAGE function, in conjunction with the COUNT and OFFSET functions, may be used to calculate an average of the latest 5 data points. You may use this strategy to average the most recent N data points, such as the most recent 3 days, the most recent 6 measures, and so on. The following is the formula in F6 in the case shown:

=AVERAGE(OFFSET(C2,COUNT(C:C)-5,0,5))

Average last 5 values in google sheets1

Let’s See How This Formula Works

This function may be used to create dynamic rectangular ranges using a beginning reference and the values of the rows, columns, height, and width parameters. The rows and columns parameters are treated as “offsets” from the initial reference in the same manner. The number of rows and columns in the final range is determined by the height and width parameters (both of which are optional). For the sake of this example, OFFSET is set as follows:

  • reference = C2
  • rows = COUNT(C:C)
  • cols = 0
  • height = -5
  • width = (not provided)

The beginning reference is supplied as C2, which is the cell immediately above the actual data in the table. Because we want OFFSET to provide a range of values starting with the last item in column C. We use the COUNT function to count all of the values in column C to get the needed row offset for OFFSET. COUNT solely counts numeric numbers, which means that the heading in row 1 is discarded automatically.

OFFSET resolves to the following when there are 10 numerical values in column C:

=OFFSET(C2,COUNT(C:C)-5,0,5)

Average last 5 values in google sheets1

This combination of parameters begins at C2, offsets 11 rows to C12, and then uses -5 to expand the rectangular range up “backwards” 5 rows to form the range C8:C12, which is the range C2:C12.

At the end of the process, the OFFSET function passes the range C8:C12 to the AVERAGE function, which computes the average values in the range.

With a dynamic range

For the sake of simplicity, the example in this section utilises a complete column reference for the data that is being averaged – when new information is added to column C. The formula will continue to function correctly since reference C includes the whole column C. However, you may also utilise a dynamic range in the following manner:

 =AVERAGE(OFFSET(C2,COUNT(DATA_Range)-5,0,5))

Average last 5 values in google sheets1

Where ” DATA_Range ” refers to a dynamic named range or a reference to a column in an google sheets Table, respectively. google sheets Tables and dynamic named ranges can dynamically expand as new data is entered, which is crucial for using this method. Take note that the reference to OFFSET is still in the row before the data appears.

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])…
Related Posts

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

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

Add Workdays in Google Sheets
Add workdays no weekends in google sheets1

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays ...

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

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

Google Sheets Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)
google sheets nested if function1

This tutorial will guide you how to use nested Google Sheets IF function  (include multiple If statements in Google Sheets formula) with syntax and provide about 15 nested IF formula examples with the detailed explanation in Google Spreadsheets. Description The ...

Sidebar