How can I compare two trendlines of two separate graphs?

Caveat -- I can't readily view your file, and I always have trouble converting a spreadsheet that large, so I can't be certain that I see what you have done so far. It would appear that you have successfully generated the trendlines in the charts, and are looking at doing additional work with those trendlines, is that right?

The first step in manipulating trendlines is to get the coefficients into the spreadsheet with enough precision that they are useful. You can expand the number format to show all the digits, then hand copy them into the spreadsheet if you want. If you are going to do this kind of analysis throughout your career, I would suggest you learn how to use the LINEST() function to generate the coefficients directly in the spreadsheet and save the effort of copying from the chart to the spreadsheet.

What kind of question are you asking? You haven't told us in math/statistical terms how you want to compare the two functions. If you tell us how you want to make the comparison/correlation, we should be able to help you figure out how to put that into Excel.

If your question is less about Excel and more about how to mathematically/statistically/scientifically compare the two curves, I'm not sure if any of us here will have the statistical/scientific background to suggest the best way to make this comparison. I'm sure we could suggest different ideas (ratio of the trendlines, or difference between the trendlines, for example), but those may or may not be correct for your application.

I'd like to compare how the slopes of tangents at each point vary over time i guess

But don't you need to have a linear trendline for LINEST?

The Linear in LINEST() refers to a linear function as you would learn in linear algebra (usually an upper division college math course). All polynomials are linear functions in this sense as are many other function types. The help file for LINEST() in the latest versions of Excel has a polynomial example, as do many of our threads here.

How do i get all the different slopes for each point on the trendline described by a 6th degree polynomial?

This sounds more like a calculus question than an Excel question. Excel does not inherently know how to take the derivative of a polynomial, but it can be taught how to do fairly easily (if you know how to take derivatives already). I'm sure there are tutorials on the internet for taking derivatives of polynomials if you search for them.

plugging it into wolframalpha to get the derivative, but it doesn't even look the same there and I don't know why

It looks ok to me, though I admit I didn't go through it thoroughly. It just looks like Wolfram prefers to express the coefficients as fractions rather than decimals.

In many cases, I believe that if one understands the math behind a problem, it will be easy to program a spreadsheet (or other programmin language) to do the work. If the math is a mystery, the programming will also be a mystery. Assuming you've had calculus/pre calculus at some point, I would suggest you start by reviewing how to differentiate polynomials so you understand how that works, then it will be easier to put into a spreadsheet.

bluntly plugging it into mathematica gives the same answers:
this slope should be negative
http://www.wolframalpha.com/input/?i...00+at+x%3D2004
this one should be positive
http://www.wolframalpha.com/input/?i...00+at+x%3D2010

but they're both negative
which leads me to believe it's not the math, but excel ... could it be it generated the wrong equation?

i've checked it and it looks good to me, but i still don't get the right results ... i'm not sure what im missing :\

That can be frustrating. Debugging this sort of thing can be frustrating. Sometimes it is as simple as a lost negative sign or a parenthesis out of place or who knows what little thing goes wrong. Sometimes, no matter how sure we are that are doing THE EXACT SAME THING, some little detail is eluding us.

Sometimes when I am debugging something like this, I break it up into as small of steps as I possibly can. Hopefully, then I can pinpoint the step or the term in the equation where it's going wrong.

ok so here are the steps:

1. Add 6th degree polynomial trendline
2. copy the given equation
3. type "derivate (paste equation here)" into mathematica
4. copy the new equation
5. paste that into excel and change all the x's with the first value in the column that is the x-axis
6. drag down to apply the same to the rest of x values
result: slope sign never changes...

i have no idea where this could possibly go wrong ...

Why do you think one of those slopes should be positive and one should be negative?

which leads me to believe it's not the math, but excel ... could it be it generated the wrong equation?

One observation, your x^6 term has one significant figure (6e-5). Is it really exactly 6.00000000000000e-5, or is that rounded to one sig fig -- meaning it could really be anything between 5.5e-5 and 6.49999999e-5? For whatever reason, when a chart displays the equation, it doesn't usually choose to display all sig figs, which can create all kinds of problems if you assume the coefficient is exactly 6e-5 when it really isn't (this could apply to the other coefficients as well. This is a common mistake we see around here (and a reason I prefer LINEST() over the chart trendlines). Check your equation and make sure all numbers are displayed to their full 15 digit precision.

the sign should change because i'm looking at it and it's a sinusoidal graph _ _
wtf.jpg
I'm not sure the significant figures would really make that much of a difference, as long as it's still a 6th degree polynomial with alternating signs

if copying won't work, then how can i just pull out full coefficients with the LINEST?

There are several different ways to set it up. From Excel's help file http://office.microsoft.com/en-us/ex...005209155.aspx (just before example 1)

In addition to using LOGEST to calculate statistics for other regression types, you can use LINEST to calculate a range of other regression types by entering functions of the x and y variables as the x and y series for LINEST. For example, the following formula:

=LINEST(yvalues, xvalues^COLUMN($A:$C))

works when you have a single column of y-values and a single column of x-values to calculate the cubic (polynomial of order 3) approximation of the form:

y = m1*x + m2*x^2 + m3*x^3 + b

Another observation -- what is the chart using for "x" in the regression? It looks like you are using (trying to use/assuming Excel is using) the year number as x. According the your picture, an x of 1994 should output something near 100, but an x of 1994 doesn't yield anything close to 100. It is another common error (especially when using dates) to not know what the chart trendline is using for x. This might be because I can't really see your original spreadsheet you posted, but it looks to me like another part of the problem is that x is not adequately defined, so that we (or at least I) cannot determine what the chart trendline equation is using for x.

hm, well it says it's using =data!$H:$H which is just the H column of years:
2011.99726
2011.994521
2011.991781
2011.989041
2011.986301
2011.983562
etc

>.> ... what else would it be using? %\
how can i use LINEST to process a 6th degree polynomial equation?

Originally Posted by shg

Here's something to ponder.

that looks really nice X3 ... but i'm not entirely sure what I'm looking at @.@ I'm kind of a newb when it comes to excel >.<
I'm a physics undergrad and I just started this project last week. I've been told to find sunspot and solar flux data and "correlate" them %\
Still not entirely sure what I'm even supposed to be doing here 8| ... But i laid the two data sets over each other and the trendlines lined up pretty nicely, so I figured maybe I could somehow find the degree of the "niceness" by relating the slopes of the tangent lines to each trendline at each point ...
blarg ...
thanks for bearing with me, guys XD ...
hopefully i can get some kind of half decent results by monday +.+ ...

Originally Posted by shg

You're wasting your time applying a polynomial fit to periodic phenomena, and then trying to do analysis based on the polynomial. Polynomials are not periodic.

how do you mean?

I wish I knew how to use fourier series +.+ ... but i'll look into it, thanks!