Board logo

Excell Trend Lines
liam.mccaffrey - 28/4/09 at 09:41 PM

I have an 18 point data set that I need to obtain a cos expression trend line for in the form of
f(x)= a + b cos(x + c)
where a, b, c are variables

There must be software that can attempt this can excell do? I can't play until the morning.

I have got pretty close with an expression of the form
f(x)= (a + cos(x + c))/b

we have to test its fit validity using an R^2 test, unfortunately my equation just fails on this criteria.

Any ideas chaps

DanP - 28/4/09 at 10:01 PM

I don't think excel can do this kind of fitting, but I don't think you actually need a trend line to what you need to do.

I think 'a' should just be the amount that the sine/cos wave is shifted up or down from oscillating around zero, 'b' should be the amplitude of the sine/cos wave (ie the amount it moved up and down from 'a', and 'c' relates to the phase of the wave ie where it reaches it's peak in relation to 0 on the x axis.

It sounds like you already have an equation that is pretty close, you should just be able to rejog the numbers you already have to get it in the right form

HTH!

DanP - 28/4/09 at 10:07 PM

Incidentally excel does have a useful feature called 'solver' (it's in the analysis toolpack that you enable using the 'add-ins' menu option)

It allows you to specify a cell (like your r^2 value) that it should try to minimise/maximize etc and it can play with the inputs to an equation until it finds an answer that meets your criteria.

I don't think you'll need it here but it can be quite useful.

rost - 29/4/09 at 06:37 AM

quote:

f(x)= a + b cos(x + c)

explanation:
a: offsets the wave to a higher or lower position.
b: increases the amplitude of the wave (cos(x) is max 1, so your amplitude = b*1).
c: offsets your wave to the left or right. (keep in mind your b has an effect on c)

I didn't get into statistics enough to curve fit a sine wave though.

I did find a specific piece of software for curve fitting: http://www.unipress.waw.pl/fityk/

Haven't tried it myself yet, but its worth taking a look at!

[Edited on 29/4/09 by rost]

liam.mccaffrey - 29/4/09 at 06:45 AM

thanks fellas, that eaxactly how i fitted my wave. by altering the phase angle and amplitutde.

been looking at it again this morning and we are not sure its possible within our constraints of r^2. We will keep trying

Many Thanks for the advice guys

iank - 29/4/09 at 07:44 AM

If you're doing that kind of thing regularly then you want something like matlab. Quite a learning curve but the right tool for the job if you use it a lot.

http://www.mathworks.com/

There is an open source project (kind of project that should work well as university bods will be interested)

http://www.gnu.org/software/octave/

liam.mccaffrey - 29/4/09 at 08:00 AM

i reckon i have a copy of matlab floating round from wehn i did the math degree