LinkBack URL
About LinkBacks
I want to draw a trendline on my timeseries chart, but it seems to start off at the wrong position. Both with linear and logarithmic trendlines. Any ideas?
Capture.PNG
The trendlines on a logarithmic axis seems off
I want to draw a trendline on my timeseries chart, but it seems to start off at the wrong position. Both with linear and logarithmic trendlines. Any ideas?
Capture.PNG
Please attach your file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window and Submit your post.
Here's the corresponding excel file
And here's what I'm trying to achieve.
Attachment 546024
I can't see your second attachment. In the first, try an exponential trendline.
Entia non sunt multiplicanda sine necessitate
It invalidated it for some reason. Here it is again.Originally Posted by shg
Also, I tried all the integrated trend lines, nothing works out of the box. I even tried calculating the logarithmic regression parameters based directly off the data, still I'm not even close to the trendline in this image.
Post the second workbook as you did the first.
I don't have the solution yet but I have found that:
- If you select an exponential trendline, it will be plotted on this logarithmic scale as a straight line that goes from the first to last point.
- If you use a linear scale instead of a logarithmic scale, the trendline will be plotted from the first point to the last point. I don't know why it shows only a portion of this line on a logarithmic scale.
- I have not been able to find a curve type that is a better fit than the exponential curve.
I had to leave for a bit, I'll post it as soon as I return home in a few hours
Power function ...
Last edited by shg; 11-04-2017 at 06:38 PM.
Hi shg,
This is already great, thank you! Would you care explaining a bit how you arrived at this result?
Also, here is the most recent workbook, as promised.
linest(ln(y), ln(x)) computes the coefficients of the power function regression ln(y) = m * ln(x) + b, which is reconstructed as y = exp(b) * x ^ m. But linest used in that fashion for that data returns a value for m of about 138, and you can't raise a date value in the neighborhood on 40,000 to the 138th power in double-precision floating point -- it overflows.
So I calculated instead linest(ln(y), ln(x - offset)), and calculated the result as y = b * (x - offset) ^ m
Last edited by shg; 11-07-2017 at 10:38 AM.
Correction to chart axes ...
I added the other regression so you can compare.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks