Wasim Akram
While working in Microsoft Excel sometimes we need to calculate moving range in excel. It is also known as the moving average. Today in this article, I am sharing with you how to calculate moving range in excel with some easy tricks. Stay tuned!
How to Calculate Moving Range in Excel: 4 Quick Methods
In the following, I have shared 4 simple and quick methods to calculate moving range in excel.
Suppose we have a dataset of some Product List and their Total Sales in quantity. Now we are going to calculate the moving range using formulas.
1. Apply AVERAGE Function to Calculate Moving Range in Excel
If you are looking for a simple solution to calculate the moving range in excel, then I would suggest trying the AVERAGE function. It’s very easy and simple to determine the moving range.
Steps:
- Starting with, choose a cell to apply the formula. Here I have selected cell (D7) as we are taking 3 intervals to get out the moving range value.
- Write down the formula-
=AVERAGE(C5:C7)Where,
- The AVERAGE function returns the average value inside a given string. Here we have provided the cells (C5:C7), thus the average value for the cells stands “67”.
- Gently press Enter to get the output.
- Now, drag down the “fill handle” to fill all the cells with the moving range.
- Finally, we have successfully calculated the moving range indicating the average sales quantity from the product list.
2. Use Data Analysis Tool to Calculate Moving Range
Another quick method to determine the moving range is using the data analysis tool. With proper utilization of the data analysis tools, you not only get to calculate the moving range but also you can draw a chart to visualize the trend for the sales. Follow the steps below-
Steps:
- Above all, press the “Data Analysis” option from the “Data” option.
- A new dialog box will appear named “Data Analysis”.
- From the new window choose “Moving Average” and press OK to continue.
- Now in the new dialog box choose your “Input Range” which is cells (C5:C13).
- Then, choose an interval of your choice. As we are determining the average value with 3 values. Thus we put “3” in the “Interval” section.
- Hence, select the “Output Range” from your dataset where you want to view your calculated output.
- After that, check mark the “Chart Output” if you want to get the chart in your worksheet.
- Simply, press OK.
- In summary, we have the moving range values in our selected output cells with a chart too.
Read More: How to Calculate Range for Grouped Data in Excel
3. Calculate Moving Range for the Last N-th Values in Excel
Sometimes you might feel the need to calculate the moving range for the last N-th values. In that case, you have to combine the COUNT, OFFSET, and AVERAGE functions to get your precious output. Follow the steps below-
Steps:
- In the same fashion, choose a cell (D13) to apply the formula.
- Write the following formula down-
=AVERAGE(OFFSET(C5,COUNT(C5:C100)-3,0,3,1))
- COUNT(C5:C100)→ In this part the COUNT function is counting how many values are available in Column C and providing an output-”9” as we have a total of 9 values in the column.
- OFFSET(C5,COUNT(C5:C100)-3,0,3,1)→ The OFFSET function takes the cell reference C5 and then select the range by taking the starting and ending point from the argument to calculate.
- Here, “-3” indicates 3 rows up, “0” order to stay in the same column, “3” means consisting of total 3 rows, and “1” indicates a total of 1 column.
- AVERAGE(OFFSET(C5,COUNT(C5:C100)-3,0,3,1))→ In the end, the AVERAGE function returns the average value from the selected cells in the range.
4. Calculate Moving Range for Insufficient Data in Excel
While working you won’t get proper data to calculate. For insufficient data, the average value will return “#N/A” for not getting the data to calculate. Here in this method, I am explaining to you how to calculate the moving range with the AVERAGE, ROW, and IF functions when you have insufficient data.
Steps:
- First, choose a cell (D5) and write the following formula down-
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C5:C7))Where,
- The IF function is generating values with the help of the ROW and AVERAGE functions indicating if the value for this argument “ROW()-ROW($C$5)+1” is less than “3” then show “#N/A” and if not then display the average value.
- Simply press Enter and drag the “fill handle” down to fill all the cells.
- Finally, we have the moving range output in our hands using a simple formula.
Things to Remember
- If you haven’t enabled the “Data Analysis” option, you won’t be able to enjoy the fantastic feature. In order to enable it just go to File > Options > Add-ins. From there choose “Analysis Toolpak” and click OK. That’s it.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover all the methods to calculate moving range in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.
