NxCMA - Centered Moving-average Filter

Computes and returns an array of the centered (two-sided) moving average.


NxCMA(X, Order, TypeM, Weights, EndPoints)
is the univariate time series data (one-dimensional array of cells (e.g., rows or columns)).
is the time order in the data series (i.e., the first data point's corresponding date (earliest date=1 (default), latest date=0)).
Order Description
1 ascending (the first data point corresponds to the earliest date) (default)
0 descending (the first data point corresponds to the latest date)
is the integer enumeration for the filter type: (0 = Simple moving-average (default), 1 = Binomial, 2 = Henderson, 3 = Spencer, 4 = user-defined weights)
Type Description
0 Simple two-sided equal-weighted moving average (default)
1 Binomial-weighted moving average
2 Henderson's weighted moving average
3 Spencer's weighted moving average
4 User-defined weighted moving average
is the number of terms (aka. weights) in the moving window. M must be odd-number.
is an array of the multiplying factors (i.e., weights) of the user-defined moving/rolling window. Only required when Type=4 (user-defined weighted MA).
is a logical value for applying asymmetric weights at the end of the series. If missing or omitted, Endpoints = False, and no treatment for endpoints are performed.


  1. The time series is homogeneous or equally spaced.
  2. The time series may include missing values (e.g. #N/A) at either end.
  3. The NxCMA returns an array-type value back to Excel. The user must use CTRL+ALT+ENTER to show returned values.
  4. The centered or two-sided moving averages are used to smooth a time series and be able to estimate or see the trend, one-sided moving averages can be used as a simple forecasting method.
  5. The number of terms (i.e., M) in NxCMA must be odd.
  6. A moving average leaves any linear trend in the original data untouched, but with Spencer's or Henderson's filters, it is possible to leave higher-order polynomials (e.g., quadratic) untouched as well.
  7. The smoothed time series using the centered moving average is expressed as follow: $$ \hat{x}_t = \sum_{j=-\frac{M}{2}}^\frac{M}{2} w_j\times x_{t-j}$$ Where:
    • $\hat{x}_t$ is the smoothed data set at time t
    • $M$ is the number of terms in the moving average: $M$ must be an odd positive number.
    • $w_j$ is the j-th weight or the multiplying factor.
    • $ -\frac{M}{2} \leq j \leq \frac{M}{2} $
  8. For the Binomial-weighted moving average, the weights are calculated as follow: $$ w_j=\binom{M}{\frac{M}{2}+j} \times 2^{-M} $$ Where:
    • $M$ must be greater or equal to 3.
  9. For the Henderson moving average, the moving average weights are calculated as follows: $$w_j = 315\times\frac{(k-1)^2 - j^2)(k^2-j^2)((k+1)^2-j^2)((3k^2-16)-11j^2)}{8k(k^2-1)(4k^2-1)(4k^2-9)(4k^2-25)}$$ where:
    • $ k=\frac{M+3}{2} $
    • $m$ is the number of terms in moving average: $m$ is odd integer greater or equal to 3 ($m \geq 3$).
    • $w_j$ is the j-th weight or the multiplying factor.
  10. For Spencer type of filters, the number of terms can be either 15 (quarterly data) or 21 (monthly data).
  11. For 15-terms spencer moving average, the weights are: $$w_j = \frac{a_j}{320}$$ Where: $$a = [ -3, -6,-5, 3, 21,46,67,74,67,46,21,3,-5,-6,-3 ] $$
  12. For 21-terms spencer moving average, the weights are: $$w_j = \frac{a_j}{1750} $$ Where: $$a=[-5, -15, -25,-25,-10, 30, 90,165, 235, 285, 300, 285, 235, 165, 90, 90, 30, -10, -25, -25, -15, -5 ] $$
  13. If the keepNA argument is set to True(1), cells with missing value are preserved and are sampled equally with other observations and represented by #N/A in the returned array.
  14. The NxCMA function is available starting with version 1.66 PARSON.


Files Examples



Article is closed for comments.

Was this article helpful?
0 out of 0 found this helpful