Find average of K nearest neighbours ignoring blanks

[tucanj]

Hi,

zbor was kind enough to help me find the average of K nearest neighbors in the same column to a cell (link: http://www.excelforum.com/excel-prog...mputation.html), however I need the macro to ignore blanks when doing this process. I would appreciate some help. You can see the example attached which was his latest work.

[tucanj]

I have written some code to find the 5 nearest neighbors on each side of the cell however I am running into problems when approaching the edges of the dataset and there are not 5 values on each side. My code is below (sorry for the sloppiness in advance). Any ideas how to deal with the blank cells within 5 cells of the edge? For example, if there are 4 cells above the blank cell, I would like it to take 6 cells from below the cell.

Please Login or Register  to view this content.