zenduck.me: How to Calculate Moving Average in Excel 4 Examples

September 9, 2023Agustus 11, 2023 falma.u99 average, calculate, examples, excel, moving, zenduckme

Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.

Berikut adalah artikel atau berita tentang Harian zenduck.me dengan judul zenduck.me: How to Calculate Moving Average in Excel 4 Examples yang telah tayang di zenduck.me terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di [email protected], Terimakasih.

Get FREE Advanced Excel Exercises with Solutions!

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 in Excel in 4 different examples.

Download Workbook

You can download the free practice Excel workbook from here.

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

Read More: How to Generate Moving Average in Excel Chart (4 Methods)

4 Examples on How to Calculate Moving Average in Excel

In this phase, you will learn how to calculate moving average with Excel tools, formulas etc.

With the dataset shown below, we will calculate the moving average of Sales at the interval of 3 with Excel’s Data Analysis Tool.

Steps:

Click on tab File -> Options

From the Excel Options pop-up window, click Add-ins and select Excel Add-ins from the Manage box and then press GO…

Mark the Analysis ToolPak as Add-ins and click OK.

Now go to tab Data -> Data Analysis.

Select Moving Average -> OK.

In the Moving Average pop-up box,
- Provide data in the Input Range box that you want to calculate the moving average by dragging through the column or row. In our case, it is $C$5:$C$15.
- Write the number of intervals in the Interval (We wanted 3 days of interval so we wrote number 3)
- In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. In our case, it is $D$5:$D$15.
- If you want to see the trendline of your data with a chart then Mark the Chart Output Otherwise, leave it.
- Click OK.

You will get the Moving Average of the data provided along with an Excel trendline showing both the original data and the moving average value with smoothed fluctuations.

2. Compute Moving Average with the Average Function in Excel

You can simply run an AVERAGE formula to calculate the Moving Average of data given with a specific interval. Excel can understand the pattern and apply the same pattern to the rest of the data.

As shown in the image below, pick the 3rd cell and simply write a simple AVERAGE formula to calculate the sales value with an interval of 3.

In Cell D7, write

And press Enter.

You will get the moving average of sales value for the 3 specific products of that cell and the above 2 cells.

Now drag the row down by Fill Handle to apply the same pattern to the rest of the cells.

Let’s check it out whether it is really giving us the moving average (same interval of 3 but newly added data) or not.

If we double-click on any other cell as shown in the picture below, we can see that the cell has a moving average indicating the average value of that cell and the above two cells.

Cell D11 holds the moving average of Cell C9, C10 and C11.

Related Content: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)

3. Calculate the Rolling Average with Formula in Excel

You can also utilize formulas to calculate the Moving Average in Excel.

3.1. Get Moving Average for the Last N-th Values in a Column with Formula

Suppose you want to know the average of sales of last 3 products of your column. To do this, you need the formula to calculate the moving average. And the Average function can do this along with the OFFSET and the COUNT functions.

The generic formula for this is,

=Average(OFFSET(first_cell, COUNT(entire_range)-N, 0, N, 1)

Here,

N = the number of the 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)-3,0,3,1))

Here,

C5 = Start point of the values

3 = Interval

It will give you the moving average of the last 3 values in a column.

See the picture above where we got the moving average, 700, of the last 3 values (Cell C13, C14 and C15) of Column C of our dataset.

To check whether the result is really correct or not, we also implemented a generic Average formula among cells C13 to C15 and still got the result 700.

Formula Breakdown

COUNT(C5:C100) -> 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)-3,0,3,1) -> The OFFSET function takes the cell reference C5 (1^st argument) as the starting point, and balance the value returned by the COUNT function by moving 3 rows up (-3 in the 2^nd argument). It returns the sum of values in a range consisting of 3 rows (3 in the 4^th 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)-3,0,3,1)) -> Finally, the AVERAGE function calculate the returned sum values to extract the moving average.

3.2. Get Moving Average for the Last N-th Values in a Row with Formula

To get the moving average for the last 3 values in a row, the formula is,

=Average(OFFSET(first_cell, COUNT(range)-N, 0, N, 1)

As you can see, 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,COUNT(C5:M5)-3,0,3,1))

Here,

C5 = Start point of the range

M5 = Endpoint of the range

3 = Interval

It will give you the moving average of the last 3 values in a row.

4. Calculate the Moving Average for Insufficient Data in Excel

If you want to start the formulas from the first row of the range, there won’t be enough data to calculate a complete average because the range will extend above the first row.

The AVERAGE function automatically ignores text values and empty cells. So it will continue to calculate with fewer interval values. That’s why this formula works with Cell no. 3 as we declared the interval value 3.

To handle the insufficient data problem when calculating the moving average, you can use the following formula,

Where,

C5 = Start point of the range

C7 = Endpoint of the range

3 = Interval

ROW()-ROW($C$5)+1 -> Simply generates relative row number starting with 1

As C5 is in Row 5 so this indicates, in Row 5, the result is 1; in Row 6, the result is 2 and so on.

When the current row number is less than 3, the formula returns #N/A. Otherwise, the formula returns the moving average.

Now drag the row down by Fill Handle to apply the formula to the rest of the cells.

Conclusion

This article explained how to calculate the Moving Average in Excel with 4 examples. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.