# Bilinear interpolation of scattered data points

1. ## Bilinear interpolation of scattered data points

Hello guys,
I have a problem interpolating non equidistant points in Excel. Basically I have x,y,z Data for a lot of points in a grid.
What I want to be able to do is to input not mapped x an y values and interpolate the corresponding z value.
To make more clear what I mean I have attached a corresponding Excel sheet as an example. In reality what I have are about 100 x/y/z points in a close meshed grid.

I don't even know if this is possible within Excel, but maybe some of you have an idea.

Thanks
Sebastian  Register To Reply

2. ## Re: Bilinear interpolation of scattered data points

The are a variety of methods described at http://en.wikipedia.org/wiki/Multiva...ttered_data.29. All, as a practical matter, would require VBA, and none would be trivial to implement.  Register To Reply

3. ## Re: Bilinear interpolation of scattered data points

I know. The function I would need is the simplest of the methods. Just a bilinear Interpolation.
But I know it's not trivial. I thought maybe someone here has written a function like this before and could give me some advice.  Register To Reply

4. ## Re: Bilinear interpolation of scattered data points

Bilinear interpolation is intrinsically a calculation on a regular grid.

A bi-cubic polynomial fit (using Solver) doesn't work very well: ``Please Login or Register  to view this content.``
You could try a harmonic fit (sines & cosines). What's the underlying data?

There are surely add-ins that do this.  Register To Reply

5. ## Re: Bilinear interpolation of scattered data points

I am trying an to use add in using Kipling interpolation right now. The underlying data is an engine Map consisting of rpm and BMEP data on the x and y axis. The z axis is for example the fuel flow.
This is what I am using.
http://zible.free.fr/?page_id=1493  Register To Reply

6. ## Re: Bilinear interpolation of scattered data points

Kipling? You mean Kriging?  Register To Reply

7. ## Re: Bilinear interpolation of scattered data points

Yes sorry. I am doing ten things at the same time right now.. Kriging.  Register To Reply

8. ## Re: Bilinear interpolation of scattered data points

Hey shy,
how did you do the bi-cubic polynomial fit with solver?
I would like to try that on my data as well but don't know how to do it.
Thanks.

And if someone already has code for an inverse distance weigthing interpolation method in VBA that would be great, too.
I am trying to put a tool together but it will be more or less difficult.  Register To Reply

9. ## Re: Bilinear interpolation of scattered data points

I did. The formula in D8 and copied down is

=MMULT(MMULT(CHOOSE({1,2,3,4}, 1, x, x^2, x^3), \$A\$1:\$D\$4), CHOOSE({1;2;3;4}, 1, y, y^2, y^3))

... where x refers to the value in col A and y to the value in col B.

See attached.

I think the Kriging approach would be MUCH better for this problem though.  Register To Reply

10. ## Re: Bilinear interpolation of scattered data points

Yes,
Kriging should be a lot better.
But the disadvantage of Kriging is that I have to create a Variogram for each Map of Data Point for which I want to interpolate data.
Because I need to do it for a lot of data this would take a lot of time.
Therefore I have been looking into the Inverse Distance Weighting algorithm.
The problem is that my programming skills are a little rusty so I am looking for an add in or VBA code that will do the IDW interpolation for me.  Register To Reply

11. ## Re: Bilinear interpolation of scattered data points

shg I also don't really understand how you came up with the matrix at the top of your file for the bicubic interpolation.

-A--- --B-- --C--- --D---
1 1.024 1.011 0.959 -0.321
2 0.820 0.645 -0.235 0.316
3 0.313 0.279 -1.748 0.087
4 -1.169 1.076 0.480 -0.058  Register To Reply

12. ## Re: Bilinear interpolation of scattered data points

See the model in Solver.  Register To Reply

13. ## Re: Bilinear interpolation of scattered data points

Now I have been trying to do the bicubic interpolation.
But with my data the Error field shows a VALUE Error, which I can't figure out how to get to work.  Register To Reply

14. ## Re: Bilinear interpolation of scattered data points

Got it working but the error is way too high.  Register To Reply

15. ## Re: Bilinear interpolation of scattered data points

Not surprising. As I said, some kind of local interpolation is surely better.

Kriging seems more oriented to geospatial applications. For your application, perhaps interpolation based on contour lines is more appropriate. I'm interested in the solution, but really have nothing to suggest in terms of implementation.  Register To Reply

16. ## Re: Bilinear interpolation of scattered data points

At this point in my research Akima spline interpolation would be very useful and would fit my problem very well. Unfortunately I Don't think a VBA implementation will be easy so I might have to use MatLab or comparable software.  Register To Reply