fitting sine curves

[don]

i have 3 and 7 year running average data that i wish to
fit a sine curve to - can this be done in excel and if so -
how?

 

[Tushar Mehta]

If you want to fit y=a*sin(x)+b, it is a lot simpler than trying to fit
y=a*sin(b*x)+c. I think the latter can be done, but am far from sure.

Suppose your x values in radians(*) are in column A and your y values
are in B, both starting in row 2 (row 1 is a header).

Then, in C2, enter the formula =SIN(A2). Copy C2 down column C to
cover all the rows with data in A:B. Now, use the LINEST function with
B as the 'Known Ys' and C as the 'Known Xs'.

(*) If the values are in degrees, convert to radians with the formula
x/180*Pi. So, in C2, use =SIN(A2/180*PI())

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bernard Liengme]

Hello Don,
You could have Solver do it for you.
Let the x-values be in A1:A10 and the y-values be in B1:B10
1) In D1, E1 and F1 enter the text A, B and C; in D2,E2,F1 enter the values
1
2) Select D1:F2 and use Insert|Name create to name D2:F2 as A, B and C_
respectively (Of course any cell can be used)
3) In C1 enter =A*SIN(B*A1)+C_; copy down to C10
4) In G2 (for example) compute the sum of the deviations-squared of the
actual and the predicted values with =SUMXMY2(B1:B10,C1:C10)
5) Have Solver minimize G2 by varying D2:F2
Now you have a least squares fit.