Amedee Van Gasse wrote:
Bill Martin -- (Remove NOSPAM from address) shared this with us in
microsoft.public.excel.misc:


Amedee Van Gasse wrote:

I have an XY-chart with data points that are very near a sine wave.
I would like to fit a sine wave trendline to the chart, and also get
the amplitude and the period from the formula of the sine wave.

Or perhaps I should work the other way around? First determine the
parameters for the sine wave function and then create a second
series of data points to be plotted on the chart?

Any suggestions and (simple!) examples are welcome.


-----------------

Expanding a little on Bernard's response, I would proceed as follows.

1) have a column of your data points you're trying to fit.

2) Add another column which is a calculated sine wave using
amplitude, period (and phase?) values taken from three cells. Plug a
random guess at values into the cells initially.

3) Add an additional column that calculates an error function between
your data and the sine wave you've created. Conventionally this
might be a LSQ calculation. At the bottom of that column that is one
cell with the LSQ overall error for the fit.

4) Use Solver to minimize this LSQ value by manipulating the 3 cells
holding your unknown parameters -- amplitude, period and phase.

If there is also some DC offset and or slope to the data that is easy
to also incorporate into the system. That exercise is left to you...

Good luck...

Bill


Bill,

Thank you for your reply. I think I know where to find it now. I'm
going to try it.
However, after reading Dana DeLouis, I am still a bit worried...

--------------------

If Solver works, you're home free. If it fails to work, then it's fairly easy
to write a macro that crudely plugs random deltas into the parameters you've
already set up and checks the LSQ result -- saving the results if it's better
than the previous best. A crude form of Monte Carlo analysis. I've never had
that fail me for "simple" problems though it sometimes takes awhile.

Bill