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.