Ask Your Question

how do i add a sine trend line on calc?

asked 2020-04-01 18:07:58 +0100

yodale gravatar image

updated 2020-07-27 11:15:14 +0100

Alex Kemp gravatar image

I use 6.0.3 on a win10 pc and i wanna add a sine/cosine trend line. But i cant find it anywhere, if there is such thing. Plesase help me!

edit retag flag offensive close merge delete


What is a sine/cosine trendline? Do you mean a sinus/cosine function fitting some existing data using a least squares fit?

Opaque gravatar imageOpaque ( 2020-04-01 18:18:11 +0100 )edit

yes exactly, english not my 1st language sorry. The thing is i miss some data thats why i want this to be a harmonic sine wave

yodale gravatar imageyodale ( 2020-04-01 21:13:59 +0100 )edit

Your real data (empiric?) won't worry about your wishes.
To analyze data based on the idea they might allow for an approximative description by harmonic functions is a common proceeding. However, it's very different from the approximations by trendlines based on internal usage of the LINEST() (and probably LOGEST()) functions with Charts.
I'm not well informed about the field. You may look for some terms containing the name 'Fourier' who was a French scientist and mathematician (around 1800) to whom the related mathematics is traced back.

See also my (very incomplete) answer.

Lupp gravatar imageLupp ( 2020-04-01 22:03:24 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-04-01 22:03:27 +0100

Lupp gravatar image

updated 2020-11-20 18:37:39 +0100

Functions desrcibable (approximately) with the help of harmonic functions mostly contain contributions by more than one harmonic function. This is similar as with polynomial approximations, but more convoluted.
Every single harmonic contributor is described by three parameters: Frequency, Amplitude, and Phase.
If you are confident your specific function is extrremely simple, it should allow for an approximation with one single harmonic.
In this simple case you can use the 'Solver' coming packed with LibreOffice, to find values for the three mentioned parameters meeting the needs.
(You might also test triples of FAP estimated by yourself and checked with the help of the chart. LeastSquare isn't a dogm, but a pragmatic concept, imo.)

See this example: ask236405harmonicApproximationInMostSimpleCase_1.ods).

===Edit 2020-11-20 about 17:25 UTC===
(Attached example now contains the correction mentioned in my comment of 2020-07-13 below.)

edit flag offensive delete link more


If you want to do a lot of this sort of analysis, and besides the excellent suggestion by @Lupp above, you might consider using a mathematical analysis package like R Project (free and open source) or Matlab (proprietary, although there is a "free" trial version available).

What you need to use in either of those packages is a "discrete" (for empirical data) Fast Fourier Transform (FFT or DFFT) to solve for the spectrum of the data that you have. These solutions, however, will give you much more information than you might need or want, that is, all the spectral components (fundamental and harmonics and sub-harmonics) of the "signal" contained in your data, their frequencies, amplitudes and phases. So, the R and Matlab packages might not be appropriate for your problem. I mention these possible solutions so that you are at least aware of them.

ve3oat gravatar imageve3oat ( 2020-04-02 00:30:00 +0100 )edit

Due to a different question I just came back to this topic rather accidentally.
On this occasion I had another look into the attached example, and found that the image of the solver dialog on the second sheet contaied a relevant error. I cannot remember the proable cause, but I can tell you that the field Target cell should contain the cell where the SUM of quadratic deviations is calculated. In the example this is cell $J$1 while the image wrongly shows $E$5. Sorry!
And congratualations to the clever users who found and fixed the error themselves.

Lupp gravatar imageLupp ( 2020-07-13 15:14:15 +0100 )edit

answered 2020-04-12 20:37:02 +0100

yodale gravatar image

I used Solver as you told me. It took me a while understanding how it works, but the result is so neat. I wanna thank you all for answering so fast, you helped me much. Thank you guys!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-01 18:07:58 +0100

Seen: 223 times

Last updated: Nov 20 '20