sine wave trendline

[Amedee Van Gasse]

If you have read this message already in a more general Excel group:
sorry. It's only recently that I stumbled upon this group, and I'm at a
dead end in the general group. I hope the charting experts here can
help me.


I have a few thousand data points: measurements made approximately (but
not exactly) every 5 minutes over several weeks. When I plot these data
points in an XY-chart, I can clearly see a "noisy" sine wave with a
daily cycle (period). I'm not 100% sure, but there even appears to be a
weekly cycle.


I need 2 things:
* a smooth sine wave line in the chart, with the real data points
around it.
* the calculated amplitude of the data, if possible with the error
margin.


I see two ways of achieving this:

1) The graphical way: let Excel fit a sine wave trendline to the chart,
and get the amplitude, period and fase parameters from the sine wave
formula.
I already know this is not possible: Excel only has linear,
exponential, logaritmic and polynomic trendlines, no periodic
trendlines.
OR
2) The mathematical way: calculate the parameters for the sine wave
formula based on the data points, and add a second series of data
points to the chart, the points connected with a smooth line.
I already know 1 of the 3 variables: a period of 24 hours. Phase isn't
paryiculary interesting but amplitude is.


How would I do that? I'd prefer something with formulas and not with a
lot of hand work. Suggestions I already got:
* Fourier Analysis from the ATP --> not usable because the number of
points isn't 2^x
* Guesstimate initial values for A, P and F; add a column with a sinus
with these guesses, calculate the error, and let the solver add-in look
for the minimal error. --> doesn't work either, the solver seems to
have problems with this AND I need to manually guesstimate initial
values.
* Break up the data in daily highs and lows and have a pivot table
organise the data. --> this is a lot of manual work, not suitable for
something that has to be done more than once.
* Program something in VBA to do "Monte Carlo" analysis. --> This is a
lot of work and I would only do this as a last resort.


Any suggestions and examples are welcome.

 

[bj]

have you tried
generating a sine wave with approximate amplitude and period using your X
data.
squaring the difference between the real data and the calculated data, and
totaling
using solver to then minimize the total of he sum of squares. by changing
your period and amplitude.
You may have to play with the settings of Solver and your initial period and
amplitude numbers to keep it from diverging before it converges.

 

[Jon Peltier]

I thought the response in the general group was detailed and clear.
Perhaps you should revisit it, and follow all of the steps closely. If
you encounter problems, respond to the original thread or to the person
responding to your query, with a specific question about the suggested
procedure.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tushar Mehta]

If you know the period and if phase is not important, then not only
will Solver work but you can get the desired results with just the
LINEST function.

Since you are only interested in the amplitude, you have a function of
the kind: y=A0+A1*SIN(x). Somewhere you also stated that you believe
there is a linear trend in x. In that case, you would have

y=A0+A1*SIN(x)+A2*x

Note that this function is completely amenable to analysis with LINEST
because it is linear in the unknown variables A0, A1, and A2. And,
yes, I tested the results with both LINEST and Solver.

Suppose you have the x values in column A, the SIN(x) values in column
B, and the y values in column C. Then select a 5 rows by 3 columns
range and *array* enter the formula =LINEST(C1:Cn,A1:Bn,TRUE, TRUE),
where n is the last row with data.

You can check the results with Solver. Designate three cells, say F1,
G1, and H1 as the three unknowns, A0, A1, and A2.

In D1 enter the formula =$F$1 + $G$1 * B1 + $H$1 * A1. In E1 calculate
=(D1-C1)^2. Copy D1:E1 all the way to rows 2:n. In F3 enter =SUM
(E1:En). Ask Solver to minimize F3 by changing F1:H1. You will get
the same results as LINEST.

For my tests, I generated 540 random data points using three different
methods. 3*SIN(x)+(RAND()/5-0.1), 2+3*SIN(x)+(RAND()/5-0.1), and
2+3*SIN(x)+ x/10+(RAND()/5-0.1) I also tried with the random variable
RAND()/2-0.25

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

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

 

[Amedee Van Gasse]

Tushar Mehta shared this with us in microsoft.public.excel.charting:

If you know the period and if phase is not important, then not only
will Solver work but you can get the desired results with just the
LINEST function.

That would be nice!

Since you are only interested in the amplitude, you have a function
of the kind: y=A0+A1*SIN(x).

OK, that sounds correct.

Somewhere you also stated that you believe there is a linear trend in
x. In that case, you would have

y=A0+A1*SIN(x)+A2*x

No, you misread that. I'll just ignore the +A2*x part.

Note that this function is completely amenable to analysis with
LINEST because it is linear in the unknown variables A0, A1, and A2.
And, yes, I tested the results with both LINEST and Solver.

Suppose you have the x values in column A, the SIN(x) values in
column B, and the y values in column C. Then select a 5 rows by 3
columns range and array enter the formula =LINEST(C1:Cn,A1:Bn,TRUE,
TRUE), where n is the last row with data.

You can check the results with Solver. Designate three cells, say
F1, G1, and H1 as the three unknowns, A0, A1, and A2.

In D1 enter the formula =$F$1 + $G$1 * B1 + $H$1 * A1. In E1
calculate =(D1-C1)^2. Copy D1:E1 all the way to rows 2:n. In F3
enter =SUM (E1:En). Ask Solver to minimize F3 by changing F1:H1.
You will get the same results as LINEST.

For my tests, I generated 540 random data points using three
different methods. 3*SIN(x)+(RAND()/5-0.1),
2+3*SIN(x)+(RAND()/5-0.1), and 2+3*SIN(x)+ x/10+(RAND()/5-0.1) I
also tried with the random variable RAND()/2-0.25

Yes!!!
I can work with this. Thank you, thank you, thank you!!!

 

[Tushar Mehta]

You are welcome. Persistence pays off, eh?

--
Regards,

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

{snip}

 

[Martin Brown]

have you tried
generating a sine wave with approximate amplitude and period using your X
data.
squaring the difference between the real data and the calculated data, and
totaling
using solver to then minimize the total of he sum of squares. by changing
your period and amplitude.
You may have to play with the settings of Solver and your initial period and
amplitude numbers to keep it from diverging before it converges.

You can certainly do that, although for a dataset with a known periodic
repetition it may be better to fold the data over the known period of 24
hours. Lomb periodogram and similar methods implement this approach. The
full method can cope with gaps in the data too.

It is a lot easier if you know you can fold the data on a fixed period
like 24 hours. This also has the advantage that the periodic waveform
need not be a sine wave - its a technique popular with variable star
observers too. You may even find spreadsheets around to do it.

Hope this helps,
Martin Brown

 

[Amedee Van Gasse]

Martin Brown shared this with us in microsoft.public.excel.charting:

You can certainly do that, although for a dataset with a known
periodic repetition it may be better to fold the data over the known
period of 24 hours. Lomb periodogram and similar methods implement
this approach. The full method can cope with gaps in the data too.

It is a lot easier if you know you can fold the data on a fixed
period like 24 hours. This also has the advantage that the periodic
waveform need not be a sine wave - its a technique popular with
variable star observers too. You may even find spreadsheets around to
do it.

Hope this helps,
Martin Brown

Martin,

I googled up Lomb periodogram and my first reaction is:

<keanu>
Whoa!
</keanu>


My second reaction: I think I have to go back to uni and follow a
course about all those interesting numbercrunching statistical analysis
methods. I wished I had paid more attention when fast Fourier
transformation was explained...