Non-parametric K-NN regression in Excel

Scene 1:

Hello and welcome. In this video, we will demonstrate how to use the NumXL K-nearest neighbors of KNN function to estimate the values for observations with empty or missing values in a dataset.

First, in an adjacent column,

add the X values for the observations we wish to calculate Y-values for.

Next, select the top cell in the column adjacent to the X-values: Column E.

and type “N X K N N” in the formula bar, then press the “F X” button on the left.

The Excel function arguments window pops up.

In the Function Arguments dialog, each argument is displayed in a separate box, making it easier to input the values.

To view the complete reference page on this function online, locate and click the “help on this function” link at the left bottom of the window.

Here it is: The function arguments window launches your default internet browser and loads the NxKNN(.) function reference page.

For the X argument, select the range of cells in column A.

For the Y argument, select the range of cells in column B.

For the number of neighbors data points k, type six (6).

For the method argument, type zero (0) for the original unweighted implementation.

For the kernel function, which is irrelevant since we selected the original method, type 6 or leave it empty.

For the optimization switch argument, type one (1) or TRUE to compute an optimal kernel bandwidth value.

Select the cells with the desired X-value in column D for the target argument.

Finally, enter zero (0) or leave blank for the return type argument to return the fitted (aka., forecast) values.

Press the OK button to accept all input values and close the window.

Here you go, the full array of fitted values using a non-parametric k-nearest neighbors regression function.

Let’s now view the fitted values against the original data points on one graph!

Thanks for watching! For more info, check us out online, email us, or call us.