|
MOVAVG Function
MOVAVG can calculate 6 different types of moving averages for a series:
Simple moving averages (SMA)
Central simple moving averages (CSMA)
Cumulative moving averages (CUSMA)
Weighted moving averages (WMA)
Exponential moving averages (EMA)
Double exponential moving averages (Brown’s method) (DEMA)
A set of moving averages is calculated from the input data and returned as an array.
If the input data has more than one row and more than one column (multiple series of data), a set of moving averages is calculated for each series.
Options are provided to control the number of terms in the moving averages and the starting method for the exponential moving averages.
MOVAVG is a non-volatile multi-threaded array function.
MOVAVG Syntax
MOVAVG (Range_Array, N, MA_Method [, Down] [,StartExp])
Range_Array
The values to be used for the moving average calculations. Can be a Range or an Array, a Column or a Row or a range of rows and columns.
If any non-numeric values are found in Range-Array MOVAVG will return #Value.
N
For Simple, Central simple and Weighted moving averages defines the number of items to be used in the moving average.
For Cumulative moving averages N is ignored.
For Exponential moving averages N, controls the smoothing constant (Alpha) and hence the amount of smoothing used in the calculation.
Alpha is calculated as 2/(N+1) so if N=9 then Alpha =0.2
To calculate N for a given Alpha use N=2/Alpha-1 so for Alpha=0.1, N= 19
MA_Method
Controls the calculation method used for the moving averages.
1 = Simple Moving Average.
Returns the average of each set of N consecutive values. The value is returned at the position of the last value in each set: so if N=4 zero is returned in position 1,2 and 3 and the first moving average is in position 4 followed by the 4-term moving average for each successive set.
The 4-term moving average of 1,2,3,4 is (1+2+3+4)/4 = 2.5 in position 4.
2 = Central Simple Moving Average.
For some types data it is more appropriate to return the moving average in the central position of the set. For example, if N=5 the first moving average will be returned in position 3.
For even values of N the moving average is calculated as the average of the 2 central terms and is returned at the position of the first central term. For example, if N=4 then two 4-term moving averages will be calculated centered on both position 2 and 3, and the average of these two movong averages will be placed at position 2.
3 = Cumulative Moving Average.
This is calculated as the cumulative sum of the values so far divided by the number of values so far. The value of N is meaningless for a CUSMA and is ignored.
4 = Weighted Moving Average.
An ascending sequence of weights is applied to the values in each set of N values to give more emphasis to the more recent values. The weights used are an ascending series of integers, divided by the sum of the integers.
For an N of 4 the weights are 1,2,3,4 with each weight divided by SUM(1,2,3,4)=10
For values 5,6,7,8 the WMA of this 4-term group is calculated as
(5*1+6*2+7*3+8*4)/ (1+2+3+4) =7
5 = Exponential Moving Average.
Each term in an EMA is calculated as
EMA(t)= Alpha* x(t) + (1-Alpha)* EMA(t-1)
Where:
t denotes the position in the data series
x (t) is the value in the data series at that position.
EMA(t) is the calculated exponentially smoothed value at position t
Alpha is the smoothing constant determined from N. It must be between 0 and 1.
The higher the value of Alpha the more weight is given to recent values (and the lower the smoothing).
MOVAVG calculates Alpha from N using Alpha=2/(N+1),
so a higher value of N gives a greater degree of smoothing.
There are 2 empirical factors in the EMA calculation that need to be chosen by the user.
What to use as the starting value. This is controlled by the value of StartExp.
What value to use for Alpha. This is controlled by the value of N.
Note: When using EMA the value of N does not determine the number of terms to average, it only determines the vale of Alpha. EMA returns a calculated EMA value for every term in the data.
6 = Double Exponential Moving Average (Brown’s method).
EMA tends to lag behind changes in the data and is therefore best suited to stationary data series (data has no overall trend).
DEMA adds a trend term by calculating both an EMA and an EMA of the EMA and then combining the two.
S1(t) = Alpha * x(t) + (1-Alpha) * S1(t-1)
S2(t) = Alpha * S1(t) + (1-Alpha) * S2(t-1)
DEMA(t) = 2* S1(t) – S2(t)
Trend(t) = (Alpha / (1-Alpha)) * (S1(t)-S2(t))
Where:
t denotes the position in the data series
x (t) is the value in the data series at that position.
S1 is the EMA of the data series x
S2 is the EMA of the S1 EMA
DEMA(t) is the calculated doubly exponentially smoothed value at position t
Alpha is the smoothing constant determined from N. It must be between 0 and 1.
Trend(t) is the trend term at position t
There are 2 empirical factors in the DEMA calculation that need to be chosen by the user.
What to use as the starting values for S1(0) and S2(0). This is controlled by the value of StartExp.
What value to use for Alpha. This is controlled by the value of N.
Note: When using DEMA the value of N does not determine the number of terms to average, it only determines the vale of Alpha. DEMA returns a calculated DEMA value for every term in the data.
Down (Optional. Default True)
This parameter is ignored when Range_Array is a single row or column.
When Range_Array contains multiple rows and columns Down determines the direction of the moving average calculation.
If Down is True then a separate series of moving averages will be calculated for each column in Range_Array.
If Down is False then a separate separate series of moving averages will be calculated for each row in Range_Array.
StartExp (Optional. Default 1)
This parameter is only used for EMA and DEMA calculations. It controls the way the starting values at position zero are calculated.
For large values of N and low values of the smoothing constant Alpha the initial values used for the exponential smoothing calculation can have a significant effect on the smoothed result.
StartExp=1
The first value in the data is used as the starting value:
EMA(1)=x(1)
S1(1)=x(1)
S2(1)=x(1)
StartExp=0
The first N values in the data are used to calculate the starting values.
StartExp = +ve
Where +ve is a positive integer. The first +ve values in the data are used to calculate the starting values.
The calculation when StartExp>=0 is:
EMA(1) = Average of the first N or StartExp values in the data
DEMA:
S1(1) = Average of the first N or StartExp values in the data
S2(1) = Average of the first N or StartExp S1 values
DEMA(1) = 2 * S1(1) – S2(1)
StartExp = -ve
When StartExp has a negative value the starting values at position zero for both DEMA and EMA are calculated by backcasting from the last values in the data, using the standard EMA and DEMA calculation methods.
MOVAVG Examples
These examples show the output and calculation methods for the 6 MOVAVG calculation methods.
Data is shown in grey.
Result of the moving average function is shown in pale green.
The calculation steps are shown in pale blue.
.png)
This example uses DEMA with N=18 and back-casting for the starting values:
=MOVAVG(C6#,18,6,,-1)
Data is in blue and the output from MOVAVG is in orange.
.png)
Higher values of N would result in a smoother curve but more lag.
|
