 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Smooth Curve
If I have 3 (or 4, or 5, ...) values (x1,y1; x2,y2; x3; y3; etc) in a 2xN array in Excel, how can I derive the formula (y=F(x)) for a simple smooth curve that would pass through all of those points - so that I can calculate the value of a new value of Y for a new value of X (not in the table, ie. interpolation). The simple curve would be similar to the curve traced by a "spline" held fixed at the points already made available in the 2xN array Hi Tony: Checkout the description for the FORECAST() function in Excel Help. It can both extrapolate as well as interpolate between two points.  SignatureGary's Student > If I have 3 (or 4, or 5, ...) values (x1,y1; x2,y2; x3; y3; etc) in a 2xN > array in Excel, how can I derive the formula (y=F(x)) for a simple smooth > curve that would pass through all of those points - so that I can calculate > the value of a new value of Y for a new value of X (not in the table, ie. > interpolation). The simple curve would be similar to the curve traced by a > "spline" held fixed at the points already made available in the 2xN array Try fitting a polynomial of degree 1 less than the number of points. eg suppose columns A and B contain: A B 1 2 2 5 3 8 4 9 5 10 6 ? Ideally you would use the trend function to find the missing value =TREND(B1:B5,A1:A5^{1,2,3,4},A6) but it doesn't seem to work on my version (2002 SP3), I think these functions were improved in 2003 version though. An alternative which does work is: =SUM(LINEST(B1:B5,A1:A5^{1,2,3,4})*A6^{4,3,2,1,0}) and gives the value 17 for the missing value above. If there are a large number of data points you can use TRANSPOSE(ROW(1:n-1)) in place of{1,2,..,n-1}. Lori, But this works, as expected =TREND(B1:B5,A1:A5^{1,2,3,4},A6^{1,2,3,4}) SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Try fitting a polynomial of degree 1 less than the number of points. eg > suppose columns A and B contain: [quoted text clipped - 20 lines] > If there are a large number of data points you can use > TRANSPOSE(ROW(1:n-1)) in place of{1,2,..,n-1}. Bernard - Thanks for the correction - thought I might be missing something > Lori, > But this works, as expected =TREND(B1:B5,A1:A5^{1,2,3,4},A6^{1,2,3,4}) [quoted text clipped - 27 lines] > > If there are a large number of data points you can use > > TRANSPOSE(ROW(1:n-1)) in place of{1,2,..,n-1}. Thanks for your help. Unfortunately, the suggestions provide for a "straight line" approximation using the least squares method - correct me if I'm wrong. I'm looking for a curve approximation to the points provided. The approach that I'm researching is shown in http://www.bergen.org/AAST/projects/3DSMaxTutorial/mathematics.html or, to be even more complicated, in http://www.mvps.org/directx/articles/catmull/ > If I have 3 (or 4, or 5, ...) values (x1,y1; x2,y2; x3; y3; etc) in a 2xN > array in Excel, how can I derive the formula (y=F(x)) for a simple smooth > curve that would pass through all of those points - so that I can calculate > the value of a new value of Y for a new value of X (not in the table, ie. > interpolation). The simple curve would be similar to the curve traced by a > "spline" held fixed at the points already made available in the 2xN array Tony - no, the method above fits a polynomial to the data of the form: y = a + bx + cx² + dx³ + ... and passes through all the data points. Try Bernard's formula on each of the points in the example. To fit a straight line to the data remove the exponentials: ^{1,2,3,..} from the formula. You can check this by plotting the points on an XY chart and add a trendline. If you right click the trendline there are options to adjust to a polynomial curve and to display the equation. > Thanks for your help. Unfortunately, the suggestions provide for a "straight > line" approximation using the least squares method - correct me if I'm wrong. [quoted text clipped - 11 lines] > > interpolation). The simple curve would be similar to the curve traced by a > > "spline" held fixed at the points already made available in the 2xN array OK - I agree that the TREND function provides for a polynomial fit, and my testing shows that this provides a suitable solution to my problem. Thanks to Lori, Bernard, and Gary's student > If I have 3 (or 4, or 5, ...) values (x1,y1; x2,y2; x3; y3; etc) in a 2xN > array in Excel, how can I derive the formula (y=F(x)) for a simple smooth > curve that would pass through all of those points - so that I can calculate > the value of a new value of Y for a new value of X (not in the table, ie. > interpolation). The simple curve would be similar to the curve traced by a > "spline" held fixed at the points already made available in the 2xN array
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.