Interpolation on excel

Question

Hi to all

I've a source data like this:

       35    40
-15    15.0  15.1
-10    17.2  17.4
-5     19.7  19.8

and I need to find the value in (-16, 37). I've tried with linear interpolation but I can solve only with (x,y) couple of values. Could you help me?

thanks a lot, Andrea

Answer 1

Linear interpolation will find entries inside the limits given. ie -5 >= x >= -15. To find value for -16 you will need to extrapolate.

One way is fitting fitting a surface to the points e.g solving Ax = b) or saying it is the same as (-15, 37) - so depends on your needs and choosing the correct method which is not really what this site is about.

If you know the method we can tell you how to calculate it but you should show what you have tried by showing your code.

Answer 2

You could make a reasonable estimate by treating this is three independent linear problems:

1. At a constant x-value (say, 35), use a linear fit to the 1D data to find the z-value at your target y-value (-16), using the three y-values and three z-values. Call this Z₀.
2. As 1. but now for the other x-value (40). Call this Z₁.
3. Now do a fit in the other direciton... at y=-16, use (35, Z₀), and (40, Z₁) to find the Z-value at the target x-value.

This is an approximation and not the best solution, but I doubt Excel does the multivariate analysis that you would need to solve the problem correctly -- where you would fit a surface to these points. I don't know Excel well though, and could be doubting it out of ignorance.

Answer 3

Andrea, it looks as if the people trying to help (including me) are still having difficulty with your data.

To do anything with a matrix in excel, you need to use array formulas. When you've finished typing in the formula, don't press Enter. Press Ctrl + Shift + Enter instead. If you've done it right, you'll see the formula you entered surrounded by {}'s in the formula bar.

If you think your data is linear, and you want to try the approach identified by @tom10, try

=TREND(<known y's>,<known x's>,<new x's>)

Which will return an array of new y's for each new x provided.

You also might try matrix formulas. If you can express your problem as a system of linear equations, excel has a few matrix formulas. There's a good walkthrough here. These are only going to help you if your matrices are square.

Answer 4

Hi guys,

thanks to all, your suggests has been very helpful for me to understand better the problem.

In the beginning I've putted the problem in a wrong way because I've take cue from the graphical representation of the data, a matrix. But, after some "mumble" :) I've understand that the correct way to represent the problem was:

f(x1,x2) = y

       35    40
-15    15.0  15.1
-10    17.2  17.4
-5     19.7  19.8

so, i.e. f(35, -15) = 15.0

To obtain value in x1 = 37 and x2 = -13 I can use bilinear interpolation. The graphical representation of the problem in

http://en.wikipedia.org/wiki/File%3ABilinear%5Finterpolation.png

was my flash of inspiration.

Thanks to all!

Andrea

Answer 5

Andrea, if you want to use piecewise bilinear interpolation in 2D in Excel, can I recommend the VBA function here. That's worked well for me in the past.