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!

**Table of Contents**hide

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

**Formula Breakdown:**

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

**Enter**button.

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