# foormusique.biz: How to Calculate Moving Average in Excel 4 Examples

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 foormusique.biz dengan judul foormusique.biz: How to Calculate Moving Average in Excel 4 Examples yang telah tayang di foormusique.biz 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
pop-up window, click*Excel Options***Add-ins**and select**Excel Add-ins**from thebox and then press*Manage***GO…**

**Mark**the**Analysis ToolPak**asand click*Add-ins***OK**.

- Now go to tab
**Data -> Data Analysis**.

- Select
**Moving Average -> OK.**

- In the
pop-up box,*Moving Average*- 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**.

- Provide data in the

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

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.