Using MS Excel, how do I fit a sine curve to my data?

Question

I tried using a trend line, but that doesn't have an option to fit to trig functions. I'm trying to create a laboratory exercise for my (college) students to do, and I'd rather have them use the computer to fit the data than do it by hand.

Answer 1

OK. This can be extended to many functions... For your sine issue, I figured the general form of the equation that would be useful is:

y = A sin (x) + B cos (x) + C

(The sin , cos combo takes care of possible phase shift, the C median shift).

So build your spreadsheat with columns:

x , y, sin(x), cos(x)

Now if you do a regression with y and sin(x) and cos(x) you can find the coefficients.

Excel has a built in linear regression function called LINEST and can handle multiple variables (unlike the chart trend line).

The function is returns an array, so highlight a block of cells 5 rows by 3 columns, then click in the formula bar and enter:

=LINEST(Y range, X's range, ,TRUE)

The yrange is you y range. the xrange should encompass both columns sin(x) cos(x).

To enter the array formula hold down ctrl + Shift and hit Enter.

The 1st row of your results are: C B A

The rest are stats measures (such as r^2).