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)

X is the univariate time series data (a one dimensional array of cells (e.g. rows or columns)).

Order 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)

Method 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

Value 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 funtion 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


Have more questions? Submit a request