Horizontal interpolation for >1 empty cell

[mb1z]

Hi

I'm a bit of an excel noob but getting better as time goes on.

I have a large spreadsheet and need to fill missing values with the average of the nearest neighbouring cell that has a value.

For example, some blank cells will have a neighbouring data point on each side, but some might have 2 or 3+ blank cells in a row before touching a cell with a datapoint. I want to fill all these cells just with a calculation that is also dependent on the number of cells blank, so it would need to change with the amount of blank cells.

eg
[A] [(A+B)/2] [B]
[A] [A + (B-A)(1/3)] [A + ((B-A)(2/3)] [B]


Thanks, any help is appreciated. I'll continue googling in the mean time.

Edit: It appears a stepwise fill command in excel can do this but only when I select the row manually. Can I apply this to the whole sheet?

Edit: It further appears that I need to do a linear interpolation for all missing data points, which can automatically find the closest horizontal data points and apply linear interpolation to the blanks in between, without modifying the existing data points. The biggest challenge seems to be that the guides on the internet use a strict reference point whereas I need a conditional reference which searches for the nearest data point from any given empty cell.

[mehmetcik]

I have created the attached user defined function to interploate in columns

you might enjoy seeing if you can modify it to work on rows.

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.