sine wave trendline

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.

--
Amedee Van Gasse

This is a task that Solver is good at.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Amedee Van Gasse" <[email protected]> wrote in message
news:[email protected]...
>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.
>
> --
> Amedee Van Gasse

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

Hi. If your data on the x-axes is evenly spaced out, Excel has a Fourier
Transform function under Data | Analysis. However, it's a Radix-2 algorithm
only.
My opinion is that Solver can not do a LSQ very well, especially with more
than just a few data points. I've never had much success with a LSQ
fitting. The main problem is that by definition, one is squaring the error,
so the "error" never goes negative. This confuses Solver. With multiple
values, Solver gets confused, and will quickly give up.

--
Dana DeLouis
Win XP & Office 2003


"Amedee Van Gasse" <[email protected]> wrote in message
news:[email protected]...
>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.
>
> --
> Amedee Van Gasse

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...

--
Amedee Van Gasse

Dana DeLouis shared this with us in microsoft.public.excel.misc:

> Hi. If your data on the x-axes is evenly spaced out, Excel has a
> Fourier Transform function under Data | Analysis. However, it's a
> Radix-2 algorithm only. My opinion is that Solver can not do a LSQ
> very well, especially with more than just a few data points. I've
> never had much success with a LSQ fitting. The main problem is that
> by definition, one is squaring the error, so the "error" never goes
> negative. This confuses Solver. With multiple values, Solver gets
> confused, and will quickly give up.

Dana,

Unfortunately my data is not exactly evenly spaced out. I could
extrapolate additional data points that are evenly spaced out, using a
linear or other trendline. However this would add more work, complexity
and error.
And Radix-2, that would imply that I need exactly 2^x data points,
right? That's not the case.
Also, I don't have "just a few" data points, but hundreds or even
thousands. These are measurements made approximately every 5 minutes
over several weeks. I can clearly see a dayly cycle, so the period will
be exactly 24 hours. Phase isn't very interesting but amplitude is.

But overall I get the impression that Excel isn't exactly the best
software to do this kind of analysis. Should I seek other software, and
if yes, what?

--
Amedee Van Gasse

What is your evidence that Solver gets confused by functions that can't
go negative (since that would impact all kinds of minimizations)? I
have always assumed that the issue was that the defaults are set way too
loosly.

I have not looked hard for alternate settings that would work in one
pass, but if delta is the quantity that I am trying to minimize, I can
usually improve the initial solution with a second pass to minimize
c*delta, where c is suitably large (say 10^5).

Jerry

Dana DeLouis wrote:

> Hi. If your data on the x-axes is evenly spaced out, Excel has a Fourier
> Transform function under Data | Analysis. However, it's a Radix-2 algorithm
> only.
> My opinion is that Solver can not do a LSQ very well, especially with more
> than just a few data points. I've never had much success with a LSQ
> fitting. The main problem is that by definition, one is squaring the error,
> so the "error" never goes negative. This confuses Solver. With multiple
> values, Solver gets confused, and will quickly give up.

Hi. I'm not sure what a good suggestion would be. As far as the Fourier
idea goes, yes, you would be limited to 2^12, or 4096 data points.
I'm not sure of this idea, so I'll just throw it out. Since your data has a
period of 1 day, how about breaking the data up into daily groups. Take the
average of the daily highs and lows. Half way between the high and low
would be your offset, (or dc component), The value of average high-offset
would be your amplitude, Perhaps take the average of the daily starting
values to use as your phase. A pivot table may be able to organize your
data for you.
Anyway, not the best solution, but maybe a workaround. HTH. :>)

--
Dana DeLouis
Win XP & Office 2003


"Amedee Van Gasse" <[email protected]> wrote in message
news:[email protected]...
> Dana DeLouis shared this with us in microsoft.public.excel.misc:
>
>> Hi. If your data on the x-axes is evenly spaced out, Excel has a
>> Fourier Transform function under Data | Analysis. However, it's a
>> Radix-2 algorithm only. My opinion is that Solver can not do a LSQ
>> very well, especially with more than just a few data points. I've
>> never had much success with a LSQ fitting. The main problem is that
>> by definition, one is squaring the error, so the "error" never goes
>> negative. This confuses Solver. With multiple values, Solver gets
>> confused, and will quickly give up.
>
> Dana,
>
> Unfortunately my data is not exactly evenly spaced out. I could
> extrapolate additional data points that are evenly spaced out, using a
> linear or other trendline. However this would add more work, complexity
> and error.
> And Radix-2, that would imply that I need exactly 2^x data points,
> right? That's not the case.
> Also, I don't have "just a few" data points, but hundreds or even
> thousands. These are measurements made approximately every 5 minutes
> over several weeks. I can clearly see a dayly cycle, so the period will
> be exactly 24 hours. Phase isn't very interesting but amplitude is.
>
> But overall I get the impression that Excel isn't exactly the best
> software to do this kind of analysis. Should I seek other software, and
> if yes, what?
>
> --
> Amedee Van Gasse

Hi. I most likely am wrong, but I've never had much success with a LSQ
fitting of data using Solver. My experience is that Solver gives up very
quickly if it senses any type of confusion. However, others may have had
success with it. I have been looking for a good workaround though.
Here's the issue as I've seen it. Say Solver is trying to minimize the LSQ
on 1 data point in this simple example...

(x - 7)^2

Say its first guess is 13, with a returned value of 6^2, or 36.

It's next guess is 8.9, with a returned value of 3.61.

Solver senses it's getting closer by moving in a decreasing direction.

It next tries 5, but gets a retuned value of 4. This is a reversal of
direction, so it thinks that was the wrong direction, and its next guess
will be somewhere between 8.9 and 13.

Of course, its next guess is also in the wrong direction. Solver doesn't
know which way to go now, and gives up!

With many data points also doing course reversals, I've found that it's just
too hard for Solver. Solver is incapable of continuing its search for the
correct local minimum.

In general, that's why one can not use functions like IF, Max, etc within a
Solver model.

=IF(A1<7,3,4)

Solver tries 10 in A1 and gets a return value of 4. Try's a value of 12,
and also gets a return value of 4. The equation that it uses for its next
guess doesn't make sense, so it gives up. However, Solver is capable of
tracking this decision with a Boolean constraint because that algorithm is
built in.
Anyway, the above is just my opinion of course. :>)
--
Dana DeLouis
Win XP & Office 2003


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:42D3AEE4.9090505@no_e-mail.com...
> What is your evidence that Solver gets confused by functions that can't go
> negative (since that would impact all kinds of minimizations)? I have
> always assumed that the issue was that the defaults are set way too
> loosly.
>
> I have not looked hard for alternate settings that would work in one pass,
> but if delta is the quantity that I am trying to minimize, I can usually
> improve the initial solution with a second pass to minimize c*delta, where
> c is suitably large (say 10^5).
>
> Jerry
>
> Dana DeLouis wrote:
>
>> Hi. If your data on the x-axes is evenly spaced out, Excel has a Fourier
>> Transform function under Data | Analysis. However, it's a Radix-2
>> algorithm only.
>> My opinion is that Solver can not do a LSQ very well, especially with
>> more than just a few data points. I've never had much success with a LSQ
>> fitting. The main problem is that by definition, one is squaring the
>> error, so the "error" never goes negative. This confuses Solver. With
>> multiple values, Solver gets confused, and will quickly give up.
>

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

Dana DeLouis shared this with us in microsoft.public.excel.misc:

> Hi. I'm not sure what a good suggestion would be. As far as the
> Fourier idea goes, yes, you would be limited to 2^12, or 4096 data
> points. I'm not sure of this idea, so I'll just throw it out. Since
> your data has a period of 1 day, how about breaking the data up into
> daily groups. Take the average of the daily highs and lows. Half
> way between the high and low would be your offset, (or dc component),
> The value of average high-offset would be your amplitude, Perhaps
> take the average of the daily starting values to use as your phase.
> A pivot table may be able to organize your data for you. Anyway, not
> the best solution, but maybe a workaround. HTH. :>)

That sounds like a lot of hand-hacking and manual work. I was hoping
for a "simple" solution where I could just enter the data points (or in
this case: import them from a text file or some kind of sql-ish data
source) and have instant results.
The idea was to have 2 sets of data in my chart: one with the original
data, unconnected, with a lot of "white noise", and another with the
calculated sine wave, points connected with a smooth line.


And now that I examine the raw data more closely, I get the impression
that not only there is a daily period, but also a weekly period - but
with a much smaller amplitude. Uh-oh... I think I'll have to dig up
some math books...

--
Amedee Van Gasse