How to derive a function from an irregular table of values?

[alext180]

It's easy to get a function from a table that follows a clear pattern, but what if the values for y (or x) don't follow a pattern? For example;

x = 1 , y = 13
x = 2 , y = 9
x = 3 , y = 3
x = 4 , y = 8
x = 5 , y = 1
x = 6 , y = 5
x = 7 , y = 12

If you look at these points on a graph, they don't make any normal line.

 

[paisiello2]

You should be able to fit a 6th degree polynomial that goes through all those points. You can do this using the LINEST function in Excel.

 

[alext180]

I put the values into excel and did =LINEST(A2:A8,B2:B8,,FALSE) and it gave me -0.464285714 as the slope and 9.142857 as the y-intercept but that can't be right, so I must have done something wrong... I'm not great with excel.

 

[paisiello2]

Yes, you want a 6th degree polynomial not a one degree polynomial:

ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g

You will probably need to research to figure out how to get Excel to do this for you.

 

[MarneMath]

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

Excel can do up to 16th power. After that you're on your own :)

 

[jbriggs444]

It's easy to get a function from a table that follows a clear pattern, but what if the values for y (or x) don't follow a pattern?

If the points do not have a pattern then a function that you determine by fitting it to match those seven points will not have much pattern either. Getting Excel to give you a degree six polynomial that passes through seven points will not give you a reasonable prediction for the eighth...

...unless you have reason to believe that the underlying data is not random and actually came from a sixth degree polynomial in the first place.

 

[alext180]

If the points do not have a pattern then a function that you determine by fitting it to match those seven points will not have much pattern either. Getting Excel to give you a degree six polynomial that passes through seven points will not give you a reasonable prediction for the eighth...

...unless you have reason to believe that the underlying data is not random and actually came from a sixth degree polynomial in the first place.

You're right, but I think what I'm looking for is an equation that will equal the values of y when you plug in the corresponding values of x. Plugging in 1 will give you 13, 2 will give you 9, and so on :P.

 

[MarneMath]

So what you're looking for is this right?
$$
-\frac{53}{240}x^6+\frac{1277}{240}x^5-\frac{2419}{48}x^4+\frac{3801}{16}x^3-\frac{34733}{60}x^2+\frac{40477}{60}x-275
$$
Have you tried Lagrange Interpolation?
http://mathworld.wolfram.com/LagrangeInterpolatingPolynomial.html

 

[alext180]

So what you're looking for is this right?
$$
-\frac{53}{240}x^6+\frac{1277}{240}x^5-\frac{2419}{48}x^4+\frac{3801}{16}x^3-\frac{34733}{60}x^2+\frac{40477}{60}x-275
$$
Have you tried Lagrange Interpolation?
http://mathworld.wolfram.com/LagrangeInterpolatingPolynomial.html

Wow! That's exactly what I was looking for... Although I don't quite understand Lagrange Interpolation. Thanks!

 

[jbriggs444]

Wow! That's exactly what I was looking for... Although I don't quite understand Lagrange Interpolation. Thanks!

One way to think about it is that you have seven known values -- your data points. Now find seven different functions that have values at each of those data points. You need to pick functions that are linearly independent. That is, no one function can turn out to match any sum of multiples of the other functions.

Call the functions f₁, f₂, f₃, f₄, f₅, f₆ and f₇.

You want to put together a combination k₁f₁ + k₂f₂ + k₃f₃ + k₄f₄ + k₅f₅ + k₆f₆ + k₇f₇ such that the combination has the right values at each of your seven data points. You can find the right combination by solving a set of seven simultaneous equations in seven unknowns. Say your data points are (x₁,y₁) through (x₇,y₇)

Then the equations are:

y₁ = k₁f₁(x₁) + k₂f₂(x₁) + ... + k₇f₇(x₁)
y₂ = k₁f₁(x₂) + k₂f₂(x₂) + ... + k₇f₇(x₂)
[...]
y₇ = k₁f₁(x₇) + k₂f₂(x₇) + ... + k₇f₇(x₇)

You know all the x's and y's. Those are your data points. You know the values for each of the functions at each of the x's. That's just a matter of evaluating each function at each point. The k values are the unknowns. Solve for for k₁ through k₇

Lagrange Interpolation is what you get if you take f₁ through f₇ to be:

f₁(x) = 1
f₂(x) = x
f₃(x) = x²
...
f_n(x) = x^n-1

[Edit, fixed error on the final exponent above]

Obviously this will work for any number of data points, not just seven.

 

[paisiello2]

That is what the excel function is doing except it does a least squares approach for the case where the number of data points exceeds the degree of the polynomial by one or more.