Find the Average of the Last N Values in Google Sheets

You can follow these tips regardless of the orientation of your data. Your data can be either in a row or column. I have got the solution. You can find the average of the last n values in Google Sheets with my below flexible formula.

Yes! I wish to call it flexible, as it can exclude blank cells and 0’s in the average calculation to return the correct result. Since it’s the last N rows or columns, the normal Average or Averageif functions may not be useful.

So I am bringing to you a Query (based) formula. If you follow the Offset based one – that I am not going to touch here – you may get a wrong output because of blank cells.

Here is that smarter formula and the tips to use it in your set of data.

How to Find the Average of the Last N Values in Google Sheets

I know I should provide you two types of formulas to find the average of last n number of items. The last n number can be like 1 number, 2 numbers, 3 numbers, 4 numbers and so on and that also be either in a row or column.

If the values are in a column the formula is easy to use. If it’s in a row we have to modify the formula a little bit.

I am first going to find the average of the last 4 values in a column. This number four can be altered to any number in the formula.

Find the Average of the Last 4 Values in a Column Excluding Blank Cells and 0’s

Sample Data:

The following screenshot shows the sample data that I am going to test. I want to find the average of the last 4 values which are highlighted.

I want to exclude blank cells and 0 values in the average. So you can not say the average of the last four rows. I think it’s better to say the average of the last four values.

Average of the Last 4 Values in a Column

Formula:

Below is the formula I am talking about.

=ArrayFormula(iferror(average(query(if(len(A1:A),{ROW(A1:A),B1:B},),"Select Col2 where Col2>0 order by Col1 Desc limit 4"))))

The above formula would return the result 16.75 as the average of the last 4 values. You can manually calculate the same as below and make sure that the formula output is correct.

=(B10+B11+B14+B15)/4

You may want to know how to adjust this Query formula to match your range.

See the last part of the formula. The “limit 4” is the one that you want to focus on to find the average/mean of the last N values in Google Sheets.

Here this 4 is the N. Change 4 to 10 to find the average of last 10 values in a column.

Now the range. If your data range is B2:C and the values that you want to find the average are in column C2:C, in the formula, change the range as follows.

1. Change LEN(A1:A) to LEN(B2:B)

2. Change ROW(A1:A) to ROW(A2:A)

Now let’s do this with the row-wise values.

Find the Average of the Last 4 Values in a Row Excluding Blank Cells and 0’s

Sample Data:

Formula:

=ArrayFormula(iferror(average(query(transpose(if(len(A1:1),{COLUMN(A1:1);A2:2},)),"Select Col2 where Col2>0 order by Col1 Desc limit 4"))))

This formula is almost the same as the above first formula. Here the major difference is an additional Transpose function. Other than this the ranges are shifted from column to row and the Row function is replaced by the Column function.

How can I use this formula?

I will explain the usage of this formula in a different range. That can help you to accommodate this formula to find the average/mean of the last N values in any rows in Google Sheets.

Suppose you have the same data in the range E5:S6. The changes in the formula would be as follows.

1. Change len(A1:1) to len(E5:5)

2. Change COLUMN(A1:1) to COLUMN(E1:1)

3. Then changeA2:2 toE6:6 and further, if you want to find the average of the last 5 values change the limit clause accordingly.

Conclusion

In this tutorial, you have learned two awesome tips, i.e. finding the average of the last n values in a row as well as in a column.

As you may know, the cells that contain the value 0 can make a wrong average result. The reason it counts those cells containing 0s in the average calculation and the average is equal to sum/count.

Hope you have enjoyed the stay!

More Average (Mean) Related Resources

32 COMMENTS

Reagan Amaranson December 22, 2021 At 4:23 am

Thank you for this article. This is just what I’ve been looking for.

I want to average the last 5 entries of my row. You can see a sample here: link removed by admin

But it tells me the average is 3.84 when it should be 3.9.

Can you help?

- Prashanth December 22, 2021 At 11:41 am
  
  Hi, Reagan Amaranson,
  
  It’s because cell AE1 is empty. The header row must contain field labels in all the columns.
  
  - Reagan Amaranson December 22, 2021 At 10:47 pm
    
    Thank you so much! That solved it!
    
Simon December 6, 2021 At 4:19 pm

Works like a charm!
Thank you for sharing 🙂

Marc Z June 15, 2021 At 1:03 am

What would I need to change to make the calculation of the mean of N be from the front side of a row of scores…I will add a new score column each week in the same column (E) and push the older scores to the right….this is the formula I have working for now but it computes the mean of the last 3 scores…I want this so that the newest scores are in the front of the spreadsheet.

=ArrayFormula(iferror(average(query(transpose(if(len(E2:2),{COLUMN(E2:2);E3:3},)),"Select Col2 where Col2>0 order by Col1 Desc limit 3"))))

- Prashanth June 15, 2021 At 12:24 pm
  
  Hi, Marc Z,
  
  Use the Array_Constrain to constrain the rows and use average on them.
  
  Syntax: =average(ARRAY_CONSTRAIN(E2:E,n,1))
  
  - Marc Z June 15, 2021 At 8:26 pm
    
    Thank you. This does average the first 3 numbers, however…the exclusion of “0” or blanks is removed…how do I get that back in there?
    tyia
    
    - Prashanth June 16, 2021 At 9:50 am
      
      Hi, Marc Z.
      
      By including Filter() as below.
      
      =average(ARRAY_CONSTRAIN(filter(E2:E,E2:E>0),n,1))
      
Cameron August 31, 2020 At 10:00 am

Hey, thanks for the above btw, amazing stuff.

I am trying to get this formula working and have had some success.

I have two problems though, how do I get it to only ignore blank cells but include 0s?

Also, I would like to return the average in the same row as the data. However, I get “Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet settings.”

Should I adjust this setting or should I be updating one of the ranges so that it stops before the cell containing the formula?

Any help would be really appreciated.

- Prashanth September 2, 2020 At 1:38 pm
  
  Hi, Cameron,
  
  If I know the data range and the cell in which you wish to key the formula in, I would be able to help you (or share the URL of a mockup sheet).
  
Nick August 29, 2020 At 10:53 am

Hi,

I am trying to get a rolling 42-day average and include days for which there is no entry.

The entries so far I only have entries for 10 day so when I apply =ArrayFormula(average(indirect("B"&MATCH(2,1/(B1:B<>""),1)+1-42&":B"&MATCH(2,1/(B1:B<>""),1)))) there is a problem, cannot have a B0 cell.

Is it possible to increase the range on the average to the up last entry then roll the average after 42 days?

- Prashanth August 30, 2020 At 1:33 pm
  
  Hi, Nick,
  
  Your numbers to find the rolling average are in B1:B where B1 contains a label.
  
  So I assume the numbers are in B2:B and here is my formula.
  
  =average(query(ArrayFormula({indirect("B2:"&"B"&MATCH(2,1/(B1:B<>""),1)), row(indirect("B2:"&"B"&MATCH(2,1/(B1:B<>""),1)))}), "Select Col1 order by Col2 desc limit 42"))
  
  This formula will return the average of the numbers in B2:B, if the count of numbers is less than or equal to 42. Else it would return the rolling average of the last 42 numbers.
  
  The above formula would exclude blanks. To include blanks, additionally use the function N with the above formula. It would be;
  
  =ArrayFormula(average(N(query({indirect("B2:"&"B"&MATCH(2,1/(B1:B<>""),1)), row(indirect("B2:"&"B"&MATCH(2,1/(B1:B<>""),1)))}, "Select Col1 order by Col2 desc limit 42"))))
  
  I hope these formulas help?
  
Fernando Davidez August 25, 2020 At 4:23 pm

Hello, I have a running closing price sheet that pulls price from 1 to 30 days, but it also pulls week-ends!! which messes up the SMA calculation as they repeat the Friday closing price on Saturday and Sunday.

How do I eliminate weekends which mess up the average as they are repeated values? I am trying to compare the SMA 10 vs SMA 20.

- Prashanth August 26, 2020 At 12:42 pm
  
  Hi, Fernando Davidez,
  
  You want to do two things – excluding weekends (Saturday and Sunday) then extract last ‘N’ values.
  
  So obviously you have a date column that makes our task easy as we can sort this column in Z-A order for the last ‘N’ values.
  
  Let’s assume the range is A2:B and A2:A contains dates. In my example, the ‘N’ is 10.
  
  You can either use the Filter or the relatively simple Query to extract the values.
  
  Filter
  
  =array_constrain(filter(sort(B2:B,A2:A,0),(weekday(sort(A2:A,1,0))<>1)*(weekday(sort(A2:A,1,0))<>7)),10,1)
  
  Query
  
  =query({A2:B},"Select Col2 where dayofweek(Col1)<>7 and dayofweek(Col1)<>1 order by Col1 desc limit 10")
  
  Wrap the above formulas with the average()
  
Kent Lassolet June 22, 2020 At 9:48 am

Hi. I’m trying to avoid adding formulas using Google Apps Script. However, that’s a possibility if my idea doesn’t work.

I’ve got a sheet where a new blank row is added at the bottom each week. I need to average the last n values in a specific column (column E) and return the result in column L.

For example, here is the sequence of events: (Column headers are in Row 1)
Row 2 is added, and a value is put in cell E2, all by script
Row 3 is added, and a value is put in cell E3, all by script
An average of E2:E3 is calculated and put in cell L3
Row 4 is added, and a value is put in cell E4, all by script
An average of E3:E4 is calculated and put in cell L4
Row 5 is added, and a value is put in cell E5, all by script
An average of E4:E5 is calculated and put in cell L5

This sequence of events continues each week, with the sheet getting a new tab each year. Currently, I am processing other data in other columns, and each column’s “arrayformula” formula will evaluate the data in each new row created each week.

Should I add formulas by the script instead? If I use an arrayformula, what is the formula that I should use?

- Prashanth June 22, 2020 At 3:29 pm
  
  Hi, Kent Lassolet,
  
  I have a formula in mind (involves Hlookup and MMULT). I’ll test it and let you know. But I have no problem sharing the logic right now.
  
  I’ll split/copy every two numbers to columns based on the tips provided on this post – Move Single Column to Multiple Columns Using Hlookup in Google Sheets.
  
  Then transpose the results and MMULT/2. That would be the required average results in a column.
  
  I may require to find a way to distribute it to the correct row by inserting one blank cell below each value in the above result. I need to work on this last part.
  
  I’ll update you soon.
  
- Prashanth June 23, 2020 At 7:11 pm
  
  Here is the said tutorial – Array Formula to Return Average of Every N Cells in Google Sheets.
  
Stephen Harris June 22, 2020 At 9:35 am

I’m using Sheets to track Blood Sugar for diabetes, BP, and weight.

Cannot figure out the edits to get an AVG Sugar reading for the last 7, 30, 60, 90 entires.

Sugars read Start ColB, Row3.

Thanks

- Prashanth June 22, 2020 At 2:49 pm
  
  Hi, Stephen Harris,
  
  To calculate the average of the last 7 entries, use the below Average + Query combo.
  
  =average(IFERROR(query(sort(B3:B,row(A3:A),0),"Select * where Col1>0 limit 7")))
  
  Change limit 7 to limit 30 to get the average of the last 30 entries. I hope you can follow this to get the average of the last 60 as well as 90 entries.
  
  - Stephen Harris June 23, 2020 At 1:14 am
    
    PERFECT!!!
    
    Thanks for assist!!
    
Xavier June 19, 2020 At 1:30 pm

Hi there,

I have a dataset that runs from B378 to B458 and I want to be able to average the last n lot of values. When I used the formula you offered to “James” above but keep getting “#DIV/0” as my answer. All I did to the above formula was change the “B1:B” to “B378:B458”. Could you please show me the correct formula I should be using.

Thanks

- Prashanth June 19, 2020 At 6:30 pm
  
  Hi, Xavier,
  
  Also change row(A1:A) to row(A378:A458).
  
  If you still have issues, please feel free to make a sample file (sheet) and share it with your comment/reply.
  
  - Xavier June 21, 2020 At 6:43 pm
    
    Thanks for the reply. I have ten columns of separate data starting at B378:B458 and going across to K378:K458. I have the formula under each column changed accordingly. It works for 6 out of 10 columns but the other 4 have a #DIV!0 output. Is there any reason this might happen.
    
    Thanks for any help you could offer me.
    
    - Prashanth June 22, 2020 At 2:42 pm
      
      Hi, Xavier,
      
      Do those columns format as ‘plain text’ (Format menu > Number)?
      
      If not, it’s high time to consider sharing a copy (sample data only) of your sheet contains the problem.
      
Mike April 19, 2020 At 9:00 pm

What if I want to include blanks and 0s?

- Prashanth April 21, 2020 At 1:06 pm
  
  Hi, Mike,
  
  To include blanks and 0s in last n average, use the below formula.
  
  =ArrayFormula(average(indirect("B"&MATCH(2,1/(B1:B<>""),1)+1-4&":B"&MATCH(2,1/(B1:B<>""),1))))
  
  This is for column B. For column C, change all the capital letters B in the formula to C.
  
  Change the number 4 to 10 for the mean of the last 10 numbers.
  
John March 3, 2020 At 4:31 am

Hi. I have an issue but not sure if this is the correct place to post it. I have two columns in Google Sheets. One for Names and one for Results. Example:

Name | Results
John | 1
Bill | 0
Susan | 1
John | 0

There are hundreds of rows like this. I’m looking for a way to find the average of the last number of results for a name.

For example, I want to know the average of the last 10 results for John. Do you know a formula for how to do this? Thanks for your help.

- Prashanth March 3, 2020 At 6:17 pm
  
  Hi, John,
  
  Yes! I do have a formula. Please check this new tutorial – Formula to Conditionally Filter Last N Rows in Google Sheets.
  
James January 9, 2020 At 6:17 am

Can you help me? I only need one column to be added. How do I use this formula with only one column (not 2)?

Something like this?

=ArrayFormula(iferror(average(query(B1:B,"Select Col2 where Col2>0 order by Col1 Desc limit 4"))))

- Prashanth January 9, 2020 At 9:56 am
  
  To calculate the MEAN of last ‘n’ values (here n=4) leaving 0s and blanks, you can use this new formula.
  
  =average(IFERROR(query(sort(B1:B,row(A1:A),0),"Select * where Col1>0 limit 4")))
  
  This formula only requires the column that contains the values to average.
  
Susan Strange November 6, 2019 At 1:36 am

I need help! I have tried to use this and I can’t make it work.

I need to calculate the last 10 days average in column D and the date is in column A. I also need to exclude the blanks but include a zero.

- Prashanth November 7, 2019 At 10:02 am
  
  Hi, Susan,
  
  Formula:
  
  =average(query({A2:A,D2:D},"Select Col2 where Col2>=0 order by Col1 Desc limit 10"))
  
  Excluded the title row (here row#1) in the selection.
  
  If this doesn’t work, please share your Dataset (Sample only).
  
  Best,