How to Calculate an Exponential Moving Average in Excel

This guide will explain how to calculate an exponential moving average in Excel.

Since it has several built-in functions and tools, Excel is an excellent tool to use when doing long and complex calculations. Furthermore, this will allow us to perform fewer mistakes, as Excel will give a warning when encountering an error in calculations. 

In this case, we will focus on calculating the exponential moving average in Excel. Unfortunately, there is no built-in function in Excel that will automatically calculate the exponential moving average. So we will utilize the exponential moving average formula and apply it in Excel.

So an exponential moving average is a moving average that gives emphasis and more weight to the most recent data values in a data set. And the exponential moving average reacts more significantly to recent changes in values than a simple moving average which applies equal weight to all values.

Furthermore, the exponential moving average is similar to the weighted moving average, which also assigns greater weight to more recent data values. However, we will use the previously calculated exponential moving average value as a basis for our calculation rather than the original values.

Let’s take a sample scenario wherein we need to calculate the exponential moving average in Excel.

Suppose we have a data set containing the monthly sales. And we want to determine the exponential moving average of the values within our data set. Since there is no built-in function you can utilize, you simply apply the exponential moving average formula to get the result.

Great! Now we can move on and dive into a real example of calculating the exponential moving average in Excel.

 

A Real Example of Calculating an Exponential Moving Average in Excel

Let’s say we have a data set containing two columns. So the first column contains the months. And the second column has the total sales made within that specific month. So our initial data set would look like this:

Initial dataset

To calculate the exponential moving average, we will be using the previously calculated exponential moving average value as the basis for our calculations. And this makes the exponential moving average more accurate than the other types of moving averages.

Thus, the exponential moving average is more cumulative since all previous data values will have some type of effect on the exponential moving average to be calculated. However, this effect lessens over time as we continue along the calculation.

Since there is no built-in function to calculate the exponential moving average in Excel, we will utilize the exponential moving average formula, which is X = (K*(C-P)) + P

In the formula, X represents the current exponential moving average value. Then, C refers to the current original data value, while K represents the smoothing constant. Lastly, P refers to the previous EMA. 

Furthermore, the first exponential moving average in the range to be calculated can simply be the corresponding original data value or be a simple moving average value.

Moreover, we need to calculate the smoothing contrast using the formula = 2/(1+n), wherein n refers to the number of periods for the exponential moving average.

To apply this in Excel, we will first calculate the smoothing constant. For example, we want to calculate our data set’s 3-month exponential moving average. So our n value in the smoothing constant formula would be 3. 

Next, we will use the original value of our first month to be our first exponential moving average value. In this case, the total sales value for the month of January will be our first exponential moving average. 

Afterward, we can now utilize the exponential moving average formula to calculate the exponential moving average value for the rest of the months. 

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and discuss the steps of how to calculate an exponential moving average in Excel.

 

How to Calculate an Exponential Moving Average in Excel

In this section, we will discuss the step-by-step process of how to calculate an exponential moving average in Excel. Furthermore, each step has detailed instructions and pictures for you to easily follow.

To apply this method in your work, simply follow the steps mentioned below,

1. Firstly, we need to calculate our smoothing constant using the formula = 2/(1+n). In this case, we want to calculate the 3-month exponential moving average, which would make our n value 3. Then, we will type in the formula “=2/(1+3)”. Lastly, we will press the Enter key to return the result.

Exponential Moving Average in Excel

 

2. Secondly, we will use the original first data value of our data set as our first exponential moving average. In this case, we will simply type in the same value for the total sales of January, which is “12”.

First EMA value

 

3. Next, we can now apply the exponential moving average formula for the rest of the rows in the data set. To do this, we will type in the formula “=(H2*(C3-D2))+D2)”. Lastly, we will press the Enter key to return the result.

Exponential Moving Average in Excel

 

4. Afterward, we will need to input the previous exponential moving average value in the current calculation. For instance, the exponential moving average value in February is “12.5”. Thus, we will input that value in our formula when calculating the exponential moving average for March.

In this case, we will type in the formula “=(H2*(C4-D3)+D3)”. Lastly, we will press the Enter key.

Exponential Moving Average in Excel

 

5. Afterward, we will repeat the process of using the previous exponential moving average value for the current exponential moving average calculation. 

Alternatively, we can also drag down the Fill Handle tool to apply the formula to the rest of the rows. 

However, we would need to make our smoothing constant an absolute reference. So our formula would have to be “=($H$2*(C4-D3)+D3)”.

Exponential Moving Average in Excel

 

6. And tada! We have successfully calculated the exponential moving average in Excel.

Final result

 

And that’s pretty much it! We have successfully explained how to calculate an exponential moving average in Excel. Now you can apply this method to your work whenever you need to determine the exponential moving average of your data set.

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like