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.