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