Register to reply 
How to derive a function from an irregular table of values? 
Share this thread: 
#1
Mar1214, 10:22 PM

P: 5

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. 


#2
Mar1214, 10:53 PM

P: 558

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.



#3
Mar1214, 11:21 PM

P: 5

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 yintercept but that can't be right, so I must have done something wrong... I'm not great with excel.



#4
Mar1214, 11:57 PM

P: 558

How to derive a function from an irregular table of values?
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. 


#5
Mar1314, 03:43 AM

P: 439

http://spreadsheetpage.com/index.php...line_formulas/
Excel can do up to 16th power. After that you're on your own :) 


#6
Mar1314, 05:55 AM

P: 907

...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. 


#7
Mar1414, 06:44 AM

P: 5




#8
Mar1414, 07:05 AM

P: 439

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}x275 $$ Have you tried Lagrange Interpolation? http://mathworld.wolfram.com/Lagrang...olynomial.html 


#9
Mar1414, 10:45 PM

P: 5




#10
Mar1514, 06:49 AM

P: 907

Call the functions f_{1}, f_{2}, f_{3}, f_{4}, f_{5}, f_{6} and f_{7}. You want to put together a combination k_{1}f_{1} + k_{2}f_{2} + k_{3}f_{3} + k_{4}f_{4} + k_{5}f_{5} + k_{6}f_{6} + k_{7}f_{7} 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_{1},y_{1}) through (x_{7},y_{7}) Then the equations are: y_{1} = k_{1}f_{1}(x_{1}) + k_{2}f_{2}(x_{1}) + ... + k_{7}f_{7}(x_{1}) y_{2} = k_{1}f_{1}(x_{2}) + k_{2}f_{2}(x_{2}) + ... + k_{7}f_{7}(x_{2}) [...] y_{7} = k_{1}f_{1}(x_{7}) + k_{2}f_{2}(x_{7}) + ... + k_{7}f_{7}(x_{7}) 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_{1} through k_{7} Lagrange Interpolation is what you get if you take f_{1} through f_{7} to be: f_{1}(x) = 1 f_{2}(x) = x f_{3}(x) = x^{2} ... f_{n}(x) = x^{n1} [Edit, fixed error on the final exponent above] Obviously this will work for any number of data points, not just seven. 


#11
Mar1514, 02:30 PM

P: 558

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.



Register to reply 
Related Discussions  
Is it possible to find values not presented in a table?  General Math  1  
Normal distribution table values  Precalculus Mathematics Homework  2  
Trying to derive this but has multiple absolute values  Calculus & Beyond Homework  1  
Fourier representation of aperiodic irregular function  Advanced Physics Homework  1  
Chain rule with table of values  Calculus & Beyond Homework  16 