# 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

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.

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.

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.

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

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

Kipling? You mean Kriging?

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

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

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.

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.

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.

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

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

See the model in Solver.

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.

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

Got it working but the error is way too high.

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1