# Non-parametric K-NN regression in Excel

Check out this video to learn how to estimate values for datasets with missing values using the NumXL non-parametric K-nearest neighbors (K-NN) function in Microsoft Excel. We'll use the um-modified K-NN method in the demonstration.

Video script

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.

Scene 2:

Scene 3:

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

Scene 4:

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

Scene 5:

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

Scene 6:

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.

Scene 7:

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

Scene 8:

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

Scene 9:

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

Scene 10:

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

Scene 11:

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

Scene 12:

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

Scene 13:

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

Scene 14:

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

Scene 15:

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.

Scene 16:

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

Scene 17:

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

Scene 18:

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