SUBNA - Missing Values Interpolation

Returns an array of cells of a time series after substituting all missing values with the mean/median.


SUBNA(X, Order, Method, Value)
is the univariate time series data (a 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 an identifier for the method used to generate values for any missing data(1=mean (default), 2=median and others). For the full list of methods, visit the NumXL help file.
Value Method
1 Mean (default)
2 Median
3 Constant
4 Forward Flat
5 Backward Flat
6 Linear
7 Cubic Spline
8 Weighted Moving Average
9 Exponential Smoothing
10 Brownian Bridge
is the data argument related to the selected treatment method (if applicable). For instance, if method is constant, then value would be the actual value.


  1. The time series data is homogeneous or equally spaced.
  2. The function operates only on intermediate missing values. Missing values on both sides are left unchanged.
  3. The function maintains the original time-order of the data set.
  4. The function returns an array equal to the size of the input data. To view the whole output, select an appropriate space in the worksheet and press Ctrl+Shift+Enter.


Files Examples

Related Links



Article is closed for comments.

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