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, H)
is the input data sample (one- or two-dimensional array of cells).
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 the method is constant, then the value would be the actual value.
is the smoothing parameter (aka. bandwidth). If missing or omitted, a default value of one (1) is used.


  1. The time series in the data set are homogeneous or equally spaced.
  2. Each column in the input data set corresponds to a separate time series variable.
  3. Each row in the input data set corresponds to an observation.
  4. The function operates only on intermediate missing values. Missing values on both sides are left unchanged.
  5. The function maintains the original time order of the data set.
  6. 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