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.

--
Amedee Van Gasse

Hi Amedee,

It sounds like you have a challenging data analysis task ahead of you, but don't worry, there are a few ways to approach this problem in Excel. Here's one possible solution:

1. First, create a scatter plot of your data points. Select the data range, go to the Insert tab, and choose Scatter from the Chart group.
2. Next, add a new column to your data table that calculates the time of day for each data point. For example, if your data starts at 12:00 AM and is collected every 5 minutes, the first time value would be 0, the second would be 0.0833 (5/60), and so on. You can use the following formula to calculate the time value for each row:
   Formula:

   =MOD(A2,1)*24 

   , assuming your time values are in column A.
3. Now, create a new column that calculates the sine of the time value, using the following formula:
   Formula:

   =SIN(B2*2*PI()/24) 

   , assuming your time values are in column B and the period is 24 hours.
4. Add a new scatter plot series to your chart, using the time and sine columns as the X and Y values. This will create a smooth sine wave line on your chart.
5. To calculate the amplitude of the sine wave, you can use the MAX and MIN functions to find the highest and lowest points of the sine wave, and then subtract them to get the amplitude. For example, if your sine wave values are in column C, you can use the following formula:
   Formula:

   =MAX(C:C)-MIN(C:C) 

   .
6. To calculate the error margin of the amplitude, you can use the STDEV function to calculate the standard deviation of the sine wave values, and then multiply it by 2 to get the 95% confidence interval. For example, if your sine wave values are in column C, you can use the following formula:
   Formula:

   =STDEV(C:C)*2 

   .

Let me know if you have any questions or if there's anything else I can do to assist you.

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.

"Amedee Van Gasse" wrote:

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.

--
Amedee Van Gasse

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/
_______

Amedee Van Gasse wrote:

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.

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


In article ,
says...
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.

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

--
Amedee Van Gasse

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

In article ,
says...

{snip}

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

bj wrote:

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" wrote:


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.

--
Amedee Van Gasse

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

bj wrote:

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

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

--
Amedee Van Gasse