The Moving Average is also known as the Rolling Average or Running Average in Excel. In this article, we will show you how to calculate the moving average for a dynamic range in Excel with examples.
What is Moving Average?
The Moving Average means the time period of the average is the same, but it keeps moving when new data is added.
For Instance, if anyone asks you to provide the moving average of sales value on day 3, you have to give the sales value of Days 1, 2, and 3. And if anyone asks you to provide the moving average of sales value on day 4, you have to give the sales value of days 2, 3, and 4. As new data is added, you must keep the time/ interval period (3 days) the same but use the newly added data to calculate the moving average.
A moving average smooths out any irregularities (peaks and valleys) from data to easily recognize trends. The larger the interval period is to calculate the moving average, the more fluctuations smoothing occurs, as more data points are included in each calculated average.
A very efficient way to calculate a moving average is with the OFFSET function in Excel. The OFFSET function can build a dynamic range which means the range will automatically update when data is modified.
The general form of the AVERAGE function and the OFFSET function together is:
=AVERAGE(OFFSET(A1, 0, 0, -n, 1))Here,
- A1 = reference
- n = the number of spans to include in each average
The above OFFSET returns a range that is passed into the AVERAGE function to extract the Moving Average of data.
Dynamic Moving Average in Excel: 3 Examples
In this phase, you will learn how to calculate the moving average for dynamic range with Excel’s OFFSET function along with the MATCH function and the COUNT function.
1. Calculate Moving Average for Specific Data in a Dynamic Range in Excel
Suppose you have a dataset of Date and Sales for each date given. You want to find the moving average for each date in a way that when you put the date in a search box and input the interval period, the moving average will be calculated. And every time you update the search value and/or interval period, the moving average will be automatically updated.
The steps on how you can do that are given below.
Steps:
- In a cell (e.g. Cell F7), write the following formula:
=AVERAGE(OFFSET(C4,MATCH(F5,B5:B15,0)+1,0,F6,1))Here,
- C4 = Starting point, Column header.
- F5 = Search value that we want to extract the moving average for (in our case, we put 12/6/2002 in that cell as an example).
- B5:B15 = Search range where we will search the match for our search value.
- F6 = The interval period that we want (for now, let’s have an interval of 3 for the sake of the example).
- Press Enter. You will get the moving average of the search value for the given interval.
Notice in the above picture, that Cell F7 is carrying the result of the average of 3 (Interval) Sales values after the Date 12/6/2002 (Search Value).
Now, look at the following gif. Every time you change the intervals, the result Cell F7 will be automatically updated according to the input value. For instance, if you pass 2 as an interval period then the result of the average of 2 (Interval) Sales values after the Date 12/6/2002 (Search Value) will be displayed, if you pass 4 as an interval period then the result of the average of 4 (Interval) Sales values after the Date 12/6/2002 (Search Value) will be displayed.
You can also change the Search Value to extract the data for any specific date that you want. The result Cell F7 will show you the correct moving average according to the date and the interval period that you will provide.
To understand more see the gif below.
Formula Breakdown
- MATCH(F5,B5:B15,0)
- Result: 5
- Description: The MATCH function returns the position of the search/ lookup value in the search range/ array. The syntax for the MATCH function is:
         =MATCH(lookup_value, array, [match_type])
Here,
-
-
- F5 = lookup_value
- B5:B15 = array
- 0 = [match_type], exact match
-
It counts the position of the search value 12/6/2002 in the range B5:B15 and finds position 5.
- OFFSET(C4,MATCH(F5,B5:B15,0)+1,0,F6,1) -> becomes:
- OFFSET(C4,5+1,0,F6,1)
- Result: 220, 450, 1000
- Description: The OFFSET function takes the cell reference C4 (1st argument) as the starting point.
- We don’t want to include the selected date so we added a +1 to our MATCH It instructs the formula to find the selected date and go down one extra cell.
- As we want to stay in the same column it was set to 0.
- Height, F6 depends on the condition. If we want to include the selected date then we must include the cell reference in the calculation.
- The width is set to 1.
- AVERAGE(OFFSET(C4,MATCH(F5,B5:B15,0)+1,0,F6,1)) -> becomes:
- AVERAGE(220, 450, 1000)
- Result: 556.6666667
- Description: The average result of 220, 450, 1000
Read More: How to Calculate 7-Day Moving Average in Excel
2. Get Rolling Average for the Last N-th Values in a Dynamic Column in Excel
Suppose you want to know the average sales of the last N amount of products in your column.
To do this, you need the formula to calculate the moving average. The Average function can do this along with the OFFSET and the COUNT functions.
The generic formula for this is,
Here,
- N = the number of values to include to calculate the average
So if we calculate the moving average for our dataset then the formula will be,
=AVERAGE(OFFSET(C5,COUNT(C5:C100)-F5,0,F5,1))Here,
- C5 = Start point of the range
- F5 = The interval period that we want (for now, let’s have an interval of 3 for the sake of the example).
It will give you the moving average of the last 3 values in a dynamic column.
Notice in the above picture, that Cell F6 is carrying the moving average, 700, of the last 3 (Interval) Sales values (Cell C13, C14, and C15) of Column C of our dataset.
Now, look at the following gif. Every time you change the intervals, the result Cell F6 will be automatically updated according to the input value. For instance, if you pass 2 as an interval period then the result of the average of the last 2 (Interval) Sales values of the column will be displayed, if you pass 4 as an interval period then the result of the average of the last 4 (Interval) Sales values of the column will be displayed.
Formula Breakdown
- COUNT(C5:C100)
- Result: 11
- Description: The COUNT function counts how many values are there in Column C. We started from Cell C5 because that is the starting point of the range to calculate.
- OFFSET(C5,COUNT(C5:C100)-F5,0,F5,1) -> becomes:
- OFFSET(C5,11-3,0,3,1)
- Result: 1050, 300, 750
- Description: The OFFSET function takes the cell reference C5 (1st argument) as the starting point, and balances the value returned by the COUNT function by moving 3 rows up (-3 in the 2nd argument). It returns the sum of values in a range consisting of 3 rows (3 in the 4th argument) and 1 column (1 in the last argument), which is the last 3 values that we want to calculate.
- AVERAGE(OFFSET(C5,COUNT(C5:C100)-F5,0,F5,1)) -> becomes:
- AVERAGE(1050, 300, 750)
- Result: 700
- Description: Finally, the AVERAGE function calculates the returned sum values to extract the moving average.
Read More: How to Calculate Exponential Moving Average in Excel
3. Extract Moving Average for the Last N-th Values in a Dynamic Row in Excel
You can also get the average of sales of the last N amount of products of your row in Excel.
The formula is almost the same as the formula with the column. Only this time, instead of including the entire range, you have to insert a fixed range.
=AVERAGE(OFFSET(C5,0,COUNT(C5:M5)-E7,0,E7,1))Here,
- C5 = Start point of the range
- M5 = Endpoint of the range
- E7 = The interval period that we want (for now, let’s have an interval of 3 for the sake of the example).
It will give you the moving average of the last 3 values in a dynamic row.
Notice in the above picture, that Cell E8 is carrying the result of the average of the last 3 (Interval) Sales values (K5, L5, M5) of the row.
Now, look at the following gif. Every time you change the intervals, the result Cell E8 will be automatically updated according to the input value. For instance, if you pass 2 as an interval period then the result of the average of the last 2 (Interval) Sales values of the row will be displayed, if you pass 4 as an interval period then the result of the average of the last 4 (Interval) Sales values of the row will be displayed.
Read More: How to Average Every Nth Row in Excel
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
This article explained how to calculate the moving average for dynamic range in Excel with 3 examples. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
Related Articles
- How to Determine Triple Exponential Moving Average in Excel
- How to Calculate Running Average in Excel
- Excel Formula to Find Displaced Moving Average
- How to Generate Moving Average in Excel Chart
- How to Calculate Centered Moving Average in Excel
<< Go Back to Moving Average | Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello Sanjida,
How do you compute a running maximum of values in a given column using dynamic arrays
as in the following example
values running maximum
113.4200 113.42000
109.5806 109.58065
104.5533 104.55333
103.2129 103.21290
102.0600 102.68667
102.5710 102.68667
102.6867 102.68667
102.4419 102.53333
102.5333 102.53333
101.6419 101.64194
101.1800 101.18000
Best regards!
gianluca
Hello Gianluca,
Hope you are doing well. Thank you for your query. Well, I can see two columns here and you need to get the running maximum of the values using a dynamic array. Therefore, you can use the MAX function to get the running maximum from the dataset. You may follow the below image to use the MAX function as an array function. If you use the total column in the formula as below then the formula will work like an array formula. You can add or change any value within the range and the running maximum value will change according to your dataset.
[wpsm_box type="green" float="none" textalign="center"]
=MAX($B:$B,$C:$C)[/wpsm_box]
Here, if add another column then the formula adds the values in the range and changes the output as below.