Average is the most useful parameter for any business calculation. Sometimes it requires entering new data and averaging with the newest entry to find the current trend or pattern. In this article, we discuss three quick solutions along with their alternative in cases to find the average of the last **N** rows in Google Sheets.

**Table of Contents**hide

## A Sample of Practice Spreadsheet

You can download the spreadsheets from the link below. After downloading you can practice on your own as we demonstrate in this article.

## 3 Suitable Ways to Calculate Average of Last N Rows in Google Sheets

First, we have to give a look at the dataset. In the dataset, prices of different products are given. We want to average the last N rows of the **Price** column. Here, N is the number of cells we want to average.

### 1. Using OFFSET and AVERAGE Functions

The aggregation of **AVERAGE**, **COUNT** and **OFFSET** functions can perform the task of averaging the last** N** rows in Google Sheets. The **OFFSET** function is used to find the range that needs to average. In this solution, we want to average the last **3** rows of the **Price** column.

__Steps:__

- First, we select the cell in which we want to show the average of the last
**N**rows. Here, we select cell**E5**to show the average of the last**3**rows.

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

- Then we insert the above formula in the formula bar.
- Finally, the output is shown in the selected cell. By tweaking the formula to suit your needs, you can
**get the average**of the last**N**number of rows.

__Formula Breakdown__

**COUNT(C5:C)**

First, the** COUNT** function counts the numeric entries of the entire **column C** from cell **C5**.

**OFFSET(C5,COUNT(C5:C)-3,0,3)**

Then, the **OFFSET** function returns the last three rows with entries.

**AVERAGE(OFFSET(C5,COUNT(C5:C)-3,0,3))**

Finally, the** AVERAGE function** averages the last three rows.

#### Alternative Solution

An alternative solution using the **OFFSET** function is below. You may try it if you find it easier.

__Steps:__

`=IFERROR(AVERAGE(OFFSET($C$5,MAX(0, COUNT($C$5:C)-3),0,3,1)),0)`

- First, we have to insert the above formula in the formula bar as earlier.
- Then we hit
**Enter**to get the desired output of getting the average of the last**3**rows. You can change the row number based on your dataset to get the desired average.

**COUNT($C$5:C)**

First of all, the **COUNT** function counts the numeric entries of the entire **column C** from cell **C5**.

**MAX(0,COUNT($C$5:C)-3)**

Then, **the MAX function** returns zero if the return of the **COUNT** function is less than three. This means there are not enough numeric entries to average last three rows.

**OFFSET($C$5,MAX(0,COUNT($C$5:C)-3),0,3,1)**

Next, the **OFFSET** function returns the last three entries.

**AVERAGE(OFFSET($C$5,MAX(0, COUNT($C$5:C)-3),0,3,1))**

Afterward, the **AVERAGE** function calculates the average of last three rows.

**IFERROR(AVERAGE(OFFSET($C$5,MAX(0, COUNT($C$5:C)-3),0,3,1)),0)**

Finally, **the IFERROR function** returns its second argument zero if there is any error in calculating the average.

**Read More: ****How to Average Cells from Different Sheets in Google Sheets**

### 2. Applying SORT and QUERY Functions

**SORT** and **QUERY** functions along with some other functions can also find the range of the last **N** rows for averaging in Google Sheets. The **SORT** function** **sorts rows by column and the **QUERY** function runs **Google Visualization API QUERY Language** query.

__Steps:__

- At first, we select the cell in which we want to show the average of the last
**N**rows. Here, we select cell**E5**to show the average of the last**4**rows.

`=ARRAYFORMULA(AVERAGE(QUERY(SORT(C5:C,ROW(C5:C)* ISNUMBER(C5:C),FALSE), "limit 4",0)))`

- Then we insert the above formula in the formula bar to get the average of the last
**4**rows. - Finally, the output is shown in the selected cell. You can alter the number of rows by modifying the formula to suit your needs.

** **

__Formula Breakdown__

**ROW(C5:C)**

Initially, **the ROW function** returns the numeric value of the first row. In our case, the numeric value of the first row is** 5** as** C5** is at row 5.

**ISNUMBER(C5:C)**

**The ISNUMBER function** returns **TRUE** if a cell contains a numeric entry. Otherwise, it returns **FALSE**.

**ROW(C5:C)*ISNUMBER(C5:C)**

It passes as an argument of the **SORT** function.

**SORT(C5:C, ROW(C5:C)*ISNUMBER(C5:C), FALSE**)

Then, the **SORT** function sorts the range in descending order.

**QUERY(SORT(C5:C, ROW(C5:C)*ISNUMBER(C5:C), FALSE), “limit 4”,0)**

Next, the **QUERY** function returns the last four numeric entries.

**AVERAGE(QUERY(SORT(C5:C, ROW(C5:C)*ISNUMBER(C5:C), FALSE), “limit 4”,0))**

The **AVERAGE **function averages the last four numeric entries.

**ARRAYFORMULA(AVERAGE(QUERY(SORT(C5:C, ROW(C5:C)*ISNUMBER(C5:C), FALSE), “limit 4”,0)))**

Finally, the **ARRAYFORMULA** returns the single output of the average in the selected cell by taking the formula within it as an array formula. If we don’t use **ARRAYFORMULA** it also works perfectly.

**Read More: ****How to Add Average Line in Google Sheets (With Detailed Steps)**

### 3. Utilizing FILTER and AVERAGE Functions

**The FILTER function** can be very useful in finding the range of the last **N** rows. In the following solution, we use the** FILTER** function together with the **AVERAGE** function to get the average of the last four rows.

__Steps:__

- First, we select the cell in which we want the average of the last
**N**rows. Here, we select cell**E5**to show the average of the last**4**rows.

`=AVERAGE(INDIRECT("C" & IFERROR(LARGE(FILTER(ROW(C5:C), LEN(C5:C)), 4), ROW(C5)) & ":C")`

- Then we insert the above formula in the formula bar to get the average of the last
**4**rows. - Finally, we get the average of the last four rows in the selected cell. You can modify the formula and change the number of rows for averaging the last
**N**rows in Google Sheets.

__Formula Breakdown__

**ROW(C5:C)**

Initially, the **ROW **function returns the numeric value of the first row. In our case, the numeric value of the first row is **5** as **C5** is at row 5.

**LEN(C5:C)**

Then, **the LEN function** returns the length of the string of **column C** from cell **C5** to the entire column.

**FILTER(ROW(C5:C), LEN(C5:C))**

Afterward, the** FILTER** function filters the row numbers of the nonempty entries within the range of **C5:C**

**LARGE(FILTER(ROW(C5:C), LEN(C5:C)), 4)**

Next, **the LARGE function** returns the** 4** largest row numbers.

**IFERROR(LARGE(FILTER(ROW(C5:C), LEN(C5:C)), 4), ROW(C5))**

**The IFERROR function** is used as a safeguard in the above formula. If there are fewer than four non-empty rows then it begins averaging from the cell **C5**.

**INDIRECT(“C” & IFERROR(LARGE(FILTER(ROW(C5:C), LEN(C5:C)), 4), ROW(C5))**

Then, **the INDIRECT function** forms the range of averaging which is the last four entries of this case.

**AVERAGE(INDIRECT(“C” & IFERROR(LARGE(FILTER(ROW(C5:C), LEN(C5:C)), 4), ROW(C5)) & “:C”)**

At last, the** AVERAGE** function does the averaging.

## Things to Remember

- Make sure you customize the solutions as your need to get the average of the last
**N**rows. - The first two solutions don’t require numeric entries in the last
**N**rows. These two will always give you the average of the last**N**numeric entries regardless of their row number. - The third solution needs numeric entries in the last
**N**rows. Make sure of that.

## Conclusion

So these are some ways to calculate the average of the last n rows in Google Sheets. I hope the solutions may help you to get your job done. Further, if you face any trouble in modifying the solutions feel free to comment below and I will try to reach out to you soon. You can also visit our website **OfficeWheel** for the most useful articles.