Table of Contents
To calculate an exponential moving average (EMA) in Excel, you can first calculate the simple moving average (SMA) of the prices for a given time period. Then subtract the SMA from the most recent price and multiply the difference by a weighting factor (usually 2/(1+periods)). Add this value to the SMA to get the EMA. Repeat this process for each period in the series to get a complete EMA.
In time series analysis, a moving average is simply the average value of a certain number of previous periods.
An exponential moving average is a type of moving average that gives more weight to recent observations, which means it’s able to capture recent trends more quickly.
The following step-by-step example shows how to calculate an exponential moving average in Excel
Step 1: Enter the Data
First, let’s enter the following dataset that shows the total sales made by a company during 10 consecutive sales periods:
Step 2: Calculate the Exponential Moving Average
Next, we’ll calculate the exponential moving average (EMA) using the following formula:
EMV = [Latest Value - Previous EMA] * (2/n+1) + Previous EMA
In the formula, n represents the number of periods to use to calculate the exponential moving average. This is the one number that you must specify.
For our example, we’ll calculate a 3-day EMA.
First, we’ll enter the EMA value in cell B2 to be equal to the value in cell A2:
Next, we’ll use the following formula to calculate the first value for the 3-day EMA:
=(A3-B2)*(2/($E$1+1))+B2
The following screenshot shows how to use this formula in practice:
Column B now shows the 3-day exponential moving average of sales.
To calculate an exponential moving average using a different number of periods, simply change the value in cell E1.
For example, we could calculate the 4-day exponential moving average of sales instead by simply changing the value in cell E1 to 4:
Column B now shows the 4-day exponential moving average of sales.