The Original Data Sheet in the attached spreadsheet shows x, y coordinates and the corresponding true z values.

The Interpolation Sheet shows a different set of x, y coordinates (some overlapping), and the interpolated values imported from two different algorithms - bilinear and bicubic interpolation. There is also a column called z_truth.

My goal is to determine whether bilinear or bicubic interpolation is better.

It may be that for this initial data set that one sometimes does better than the other and vice versa.

To get a sense of how the interpolated data matched against the original data, I started copying rows from the Original Data Sheet to the Interpolation Sheet placing x, y coordinates from one near the other. If the coordinates match exactly, then there is only one copy. Otherwise, there are two copies where the original x, y coordinate falls between two x,y coordinates in interpolated sheet.

Currently, I am just going one row at a time from the Original Data Sheet and copying it to one or two locations in the Interpolation Sheet. This is taking too much time. I was wondering whether this copying could be automated.

If you could also help me with some metric to identify whether bilinear or bicubic interpolation is closer to the true values, I would appreciate that.

(I am now aware that my choice of original true value coordinates may not be so great; but this is a (good?) first start.

Thanks,

Paul

Compare_2d_Interpolations.xlsx
The principle for the transfer is to find original x and y values where the absolute distance to interpolation x and y values, is less or equal to 0.02.

The result is in columns K to O

The values you have found are moved to columns V to Z, so you can compare.

The limit 0.02 is set at the line "Const Limit As Single = 0.02".

That match what you have found.

You can change the value, and run again, it takes only a second.

The differences to z_truth are quite large for those where x an y don't match , so a compare to bilinear and bicubic might be meaningless.

If z_truth is the correct value (where does that come from), the interpolations can be compared to that, by dividing with z_truth to see the relative match for each. Columns Q and R.

In row 1 is the average.

I would say the best is the one nearest to 1 (bicubic).

The same is done in columns S and T for the transferred values, but only where x and y does not match original x and y.

Try changing limit to 0.01, to only use values where x and y are closer to each other.

Then bilinear is best.

Open in new window

Compare_2d_Interpolations.xlsm