Linear interpolation based on hardcoded data pairs?

I'd like a linear interpolation function that calculates the linear interpolation based on a data set hardcoded within the function itself - rather than data present in a worksheet table.

For instance given the following:

Please Login or Register  to view this content.

The function call =interpolate(1.5) would give a result of 6.5, =interpolate(3.25) would give 2.375.

How would I perform this?

Hi,

You have to deduct the linear formula

Example

y = x^2 + x + 1

Then you write the function

Please Login or Register  to view this content.

Afterwards you can call the function.

Steffen Thomsen

When I switched from Quattro Pro to Excel, this was one of the things I missed the most -- a built in linear interpolation function.

I think that the hardest part of linear interpolation is extracting the interval containing the unknown. I would probably use the MATCH function. In another part of the spreadsheet

Please Login or Register  to view this content.

which will return the location i in the table of the interval. Then

Please Login or Register  to view this content.

to build a new table containing x1,y1 and x2, y2. Then, you can use either TREND or the point slope formula or whatever to locate new_y

How would this work if the data were hardcoded? In your example you would need to pass five arguments, no?

Originally Posted by Steffen Thomsen

Hi,

You have to deduct the linear formula

Example

y = x^2 + x + 1

Sorry, but I am not following? Is this interpolation or something else?

Originally Posted by MrShorty

to build a new table containing x1,y1 and x2, y2.

Thank you, but I am looking for something where the raw data is hardcoded into the function and not passed from a table. I'm not sure what the best way to hardcode the data would be - the only way I'm familiar with is Arrays, but perhaps someone can mention something else. But I'd be thinking something like for instance:

Please Login or Register  to view this content.

Question: If I try to use Double as the array type I get a #VALUE! error, instead I have to use variant - why is this?

Here's the interpolation function I've written - feedback and improvements are welcome

Please Login or Register  to view this content.

why is this

Because that's how you declare that sort of array! I know that sounds circular but it's just a rule for an array declared in that way. You could make it marginally shorter by removing the loop:

Please Login or Register  to view this content.