Interpolation on excel

Question

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

You could try to fit a plane to your data:

z=A*x+B*y+C

Then solve for A,B,C using least-squares and the 6 points (-15,35,15.0),... Excel may be able to find the optimal values for you.

Edit

Here I inserted your data (the green cells), and used the solver tool to build the optimal linear model z=Ax+By+C. (x is 35,40 and y is -15,-10,-5). A,B,C are the blue cells on the left. Then the pink cells are Ax+By+C, and the red cells are (date-model)^2. The error is the sum of all these 6 red cells.

Then use the Solver tool (needs to be activated from Excel options). Define the error cell as target (minimize) and A,B,C as variable cells. It will find the best A,B,C values.

These values are then applied to your request (x=37 and y=-16), giving the result 14.5.

Answer 2

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.