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

## 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.
• 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))`

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))))`

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

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],…)….
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…
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])…
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])….

## Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

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 values you want to calculate the average exceeds to 3, 5 or N numeric values then what would you do? If you would tend to do this task manually, then it would be your foolish decision because doing these kinds of lengthy tasks manually is very hard, and when it comes to N numeric values, then It would not be wrong to say that this is near to impossible to do it manually.

But don’t worry about it because after carefully reading this article, you can easily calculate the average values of the last 3, 5, and most important N numeric values within seconds.

So without any further delay, let’s dive into it;

## Calculate The Average Of The Last 3 Values In Google Sheets

The General Formula is as below based on the AVERAGE function, LOOKUP function, LARGE function and IF function:

`=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}),ROW(Values_Range), Values_Range))`

Note: this above is an array formula, you need to press “CTRL+SHIFT+ENTER” short cuts to make it as array formula.

## Let’s See How This Formula Works

You can use an array formula for getting the average of the last 3 values in the range. The formula in E3 in the example is as follows:

`=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Values_Range),ROW(Values_Range)),{1,2,3}), ROW(Values_Range), Values_Range))`

Here “ Values_Range ” refers to the designated name range B4:B11.

This is an array formula, so input it using control + Shift + enter.

Because the AVERAGE function computes an average of numbers supplied in an array, practically all of the effort in this formula is to construct an array of the latest three numeric values in a range.

=IF(ISNUMBER(Values_Range),ROW(Values_Range))

The IF function in the formula is used to “filter” numeric numbers from the inside out.

Because the ISNUMBER function returns TRUE for numeric values and FALSE for other values (including blanks), and the ROW function produces row numbers, the outcome of this operation is an array of row numbers corresponding to numeric entries:

`={4;5;FALSE;7;8;FALSE;10;11}`

=LARGE({4;5;FALSE;7;8;FALSE;10;11},{1,2,3})

The above array is sent to the LARGE function, using the array constants 1,2,3 for k. LARGE ignores FALSE values and returns an array with the greatest three integers, which correspond to the last three rows with numeric values:

`={11,10,8}`

=LOOKUP({11,10,8}, ROW(Values_Range), Values_Range))

The above array is used as the lookup value in the LOOKUP function. The ROW function provides the lookup array, and the return array is the named range “ Values_Range “:

`={435,657,356}`

The array of similar values in “ Values_Range ” returned by LOOKUP is then put into AVERAGE:

`=AVERAGE({435,657,356})`

## Calculate The Average Of The Last 5 Values IN Google Sheets

Please use the following array formula, which would assist you in calculating the last 5 values in Google Sheets:

In a blank cell, type the following formula:

`=IF(COUNT(B:B),AVERAGE(INDEX(B:B,LARGE(IF(ISNUMBER(B1: B10000),ROW(B1: B10000)),MIN(5,COUNT(B1: B10000)))):B10000),"no data found")`

Note: B:B is the column that holds the data you used, B1: B10000 is a dynamic range that you may stretch as long as you need, and the number 5 represents the latest n values, and then press Ctrl + Shift + Enter to make the formula as array formula to obtain the average of the last 5 numbers. See the following screenshot:

And now, when you enter new numbers underneath the old data, the average is updated as well, as seen in the screenshot:

### Related Functions

The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Google Sheets and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
The Google Sheets ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
The Google Sheets LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
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],…)….
The Google Sheets LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
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…
The Google Sheets INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
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])….
The Google Sheets ROW function returns the row number of a cell reference.The ROW function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

This post will guide you how to use Google Sheets COUNT function with syntax and examples in Google Spreadsheets.

## Description

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.

The COUNT function is a build-in function in Google Spreadsheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the COUNT function is as below:

`= COUNT(value1, [value2],…)`

Where the COUNT function arguments are:

• `value1 `-This is a required argument.  The first item, cell reference, or range within which you want to count numbers.
• `Value2 `– This is an optional argument. You can enter up to 255 value arguments.

Note:

• Arguments that are numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as “1”) are counted.
• If the argument is an array or reference, only numbers in that array are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.
• You can use the COUNTA function to count logical values, text, error values.
• You can use the COUNTIF function to count only numbers that meet certain criteria.

## Google Sheets COUNT Function Examples

The below examples will show you how to use Google Sheets COUNT Function to count the number of cells that contain numbers in a range.

#1 To count the number of cells in the range B1:B4, just using the following Google Sheets formula:

`=COUNT(B1:B4)`

### Related Functions

The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …

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

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))`

## 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)`

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))`

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.