how do I interpolate values from one set to another set of values?

[soundengineer]

basic example

cell a1 = 20
cell a2= 19
cell a3 = 18
cell a4 = 17

cell B1 = 150
cell b2 = 127
cell b3 = 110
cell b4 = 100


I have a second set of cells with values in A being 24, 22, 20, 18
and I need to find teh values for the B cells based of of the original values

I tried TREND, but looking up the value in the cell for 20 reports a different value than the already known value of 150.....
and I need it to interpolate values for unknown cells but still report the exact values if the value already exists...
maybe only interpolate from the cell below and above the value I am requesting?

[MrShorty]

Linear interpolation is something we discuss quite frequently. If you use this site's search engine, you should be able to find several discussions, including some VBA UDF's specifically designed for linear interpolation.

If you had access to it, I know Quattro Pro had a built in linear interpolation function that would make short work of a problem like this.

To make your own solution in Excel, there are several steps:

1) a lookup function to locate the interval containing new_x (I tend to use the MATCH() function).
2) The INDEX() function to return the four values (2 x values and 2 y values) that bracket new x.
3) Linear interpolation (I sometimes use the TREND() function) to return the correct y value for the new x value.

[soundengineer]

that really doesnt explain anything to me...
I cant really use VBA as I need it to work on multiple platforms and multiple softwares that all can use excel formulas, but not all of them can handle VBA


here is the current worksheet I am playing with to try to get this to work correctly

Table Interpolation test.xlsx

[MrShorty]

I couldn't make sense of your table -- Do you need a bilinear interpolation or something more elaborate?

When you say that my explanation explained nothing, specifically what are you struggling to understand? Are you familiar with Excel's lookup functions? Are you familiar with the TREND() function?

I took the 4 data points in post 1 and did a quick linear interpolation using those points. Does my use of these formulas make sense?

[soundengineer]

if you look at my sheet.....
the top table has values in the first column....

the 2nd table has similar values in the first column, but some are slightly different
where table 1 has all whole numbers, table B has some non-whole numbers

I need the 2nd table to automatically figure out those half values or partial values
Example..
in A52 there is a value of 7.5
in the top table there is no 7.5,... there is an 8, and there is a 7
so I need it to find the value above and below 7.5 and do a linear interpolation between those values for all columns
so, find value in cell b52 by looking up at the first table and finding the value above 7.5 and below 7.5 and do some math to find what 7.5 would be for that particular column based on linear interpolation.

and then I need it to be a formula I can paste into all cells, even if I have to just slightly change it per row or column to make it work

[MrShorty]

I apologize for misunderstanding before. It looks like you want basic linear interpolation.

As I noted, Quattro Pro would have made short work of this with its built in linear interpolation function. It was one of my big disappointments when I converted to Excel -- the lack of a linear interpolation function.

One of my first observations is that some of your new x values are outside of the range of your tabulated known x (max known x is 20. max new x is 24). Have you considered how you want to handle these cases? (In my example, I have just let the functions return errors and have those errors propagate through).

Which parts of my previous example using MATCH() and INDEX() did you not understand? For a non-VBA solution, that is how I do linear interpolation, and I'm afraid if you did not understand my previous example, this one will likewise be unintelligible (since I'm using the same basic approach). I also would note that I tend to avoid multiple levels of nesting, so if you need this all nested into a single cell formula, we will probably need to bring in someone else who is more skilled at nesting functions like this.