Fri, 8 Feb 2008 10:50:01 -0800 from Solis
I need to obtain a sin/cos trendline from a series of data I have. The data
shows obvious cyclical/wave properties to which a sin/cos trend would fit
perfectly. Is there a patch for this or some way to do it on Excel?
This isn't something I have tried myself, but you can always use
solver. Curve fitting is nothing more that tweaking the prediction
equation so that the deviations from measured data are as small as
possible.
Suppose your x's are in B1:B100 and your y's in C1:C100. You are
trying to fit the equation
y = a + b*sin(c*x+d)
for some constants a, b, c, d. Let's assume they will be developed
in A1:A4.
In D1, put the formula
=A$1 + A$2*sin(A$3*B1+A$4)
This is the value predicted by the equation. Note the $ after all the
A's, but not in B1. This ensures that the formula will change as
needed when dragged to fill cells.
In E1, put the formula
=(D1-C1)^2
This is the square of the "residual". The residual is the amount,
plus or minus, by which the prediction is off from your measured data
points.
Drag D1 and E1 to fill rows 2-100.
The goal of curve fitting is to minimize the sum of the squares of
the residuals, so put this formula into A6:
=sum(E1:E100)
Now run solver, with A1:A4 as adjustable cells and the target being
to minimize A6.
--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
