K-Nearest Neighbor Imputation

Hi, What I am looking to do is find the 10 (or something set by a variable) nearest neighbors to every empty cell within a range, where the neighbors are in the same column or row (dictated by another variable), and then average those 10 nearest neighbors and insert the value into the original cell.


Thanks for your help!

Can you upload example workbook with your inputs and desired output?

Try run this macro:

• It will promt for number of neighbours
• It will promt by rows or columns
• Desire result is with blue font

1

Originally Posted by zbor

Try run this macro:

• It will promt for number of neighbours
• It will promt by rows or columns
• Desire result is with blue font

Thanks for your help! This was great. There were a couple things that could be fixed, which I would appreciate help because I don't fully understand your code:
a) when calculating the average of the 10 nearest neighbors, I would like it to ignore blank cells and cells without integers.
b) I think it's restricted to a range of A1:Z100? Because when I run it on a large dataset (22000 rows, A:GH columns) it does not work. It would be great if it could work on all rows and columns.
c) It seems to me that when calculating the nearest cell for an odd number of neighbors it will take the lowest value. For example, if dNum=1, it will take the closest neighbor with the lowest value. Is it possible to make it select either one of the nearest neighbors at random?

Thanks again!

a) For 3 neighbours my code will look:

Please Login or Register  to view this content.

For blank1 it will return 3,75 (2 + blank2 + 5 = 7 and average 3.5). It won't look 2 + 5 + 2 (far away third value) and result 3.
I'm affraid this is over my skills to give you second solution.

b) Just set range to

Please Login or Register  to view this content.

c) yes. It define range from the left, so for one value it will take left (or upper number). I will need more time to make it random. Hopefully tomorrow.
Or if someone will take care of a) problem he can also solve c)

Ok thanks for your help. If you figure out a solution to a) I'm happy to hear