Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining a Simple Moving Average.
Written by Allen Wyatt (last updated December 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Jeff needs to create a formula that will return a moving average for a range of cells. He adds data to the worksheet daily and he always want to have an average of the last ten days' information. This always corresponds to the last ten cells in a column.
There are a couple of easy ways you can approach this problem. The solution you choose depends on what you ultimately want to see in the way of an average. For instance, if you want to see how the average changes over time, the best approach is to add an additional column to your worksheet. If the data is in column A (starting in row 2), then you can enter the following formula in cell B11:
=IF(A11>"",AVERAGE(A2:A11),"")
Copy the formula down the column, and you will always have the average of the last ten days shown. As you add new data to column A, the updated moving average appears at the bottom of column B. The advantage is that you can see how the average changes from day to day.
Note that it is important in this approach to place the formula in cell B11, which means that the first 9 cells (B2:B10) will not have a formula in them. This is to be expected, as this is a ten-day moving average. There cannot be any average for the first 9 days, as you don't have a full 10 days to create your first average.
If you don't want to add another column for each day's moving average, you can use a different formula to determine the current moving average. Assuming there are no blanks in column A and that there are more than ten pieces of data in the column, you could use the following formula:
=AVERAGE(OFFSET(A1,COUNTA(A:A)-1,0,-10,1))
The OFFSET function defines the range to average. It looks at the number of cells in column A and selects the last 10 as the desired range.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8347) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Determining a Simple Moving Average.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel allows you to use functions and formulas to analyze your data. One way you can analyze your data is to use the ...
Discover MoreFiguring out how to average data that is in a contiguous range of cells is easy. When the data is spread over a group of ...
Discover MoreGrabbing an average of a range of cells is easy using Excel functions. If you want that average to ignore hidden cells ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-12-24 10:41:19
J. Woolley
According to Microsoft, the syntax for OFFSET is
OFFSET(reference,rows,cols,[height],[width])
where height and width must be positive values (or omitted).
The Tip's formula works even though height is negative:
=AVERAGE(OFFSET(A1,COUNTA(A:A)-1,0,-10,1))
But perhaps it should be changed as follows:
=AVERAGE(OFFSET(A1,COUNTA(A:A)-10,0,10,1))
2022-12-24 10:25:57
J. Woolley
@Mike B
=IF(A11>"",...) appears to be a typo. It should be
=IF(A11<>"",...)
2022-12-24 10:07:41
Mike B
I don't understand what the "IF" condition (A11>"") is supposed to do. For me, it always returns "FALSE". I find that I need to change it to "ISNUMBER(A11)" or "ISBLANK(A11)" to get it to work.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments