2011-09-24 08:05:22Excel help
Anne-Marie Blackburn
Anne-Marie Blackburn

Ok, this is probably a very stupid question but us bioscientists are not renowned for our statistical prowess: how do you calculate a trend in Excel? I'm reading through Dana's AR4 blog and I've no idea how to calculate the trend in temperatures.

I'd be very grateful if someone could point me in the right direction.

2011-09-24 08:17:32


Do you know how do draw a graph with Excel? You can click with your left mouse button on the graph and with your right click on "add trend" (something like that, I am using the german version). Now you can choose what kind of trend  (linear or something else) and you can also add the formula of your trend line.

Hopefully this helps!

2011-09-24 08:38:43comment
Robert Way


Another way is to use the following function:


so type = slope and then pick the Ys (temp values) and then press comma and click the Xs (year values).

My suggestion is that you look online to tell you how to download the data analysis toolbar... this has multiple built in functions including regression and it lets you get the statistical significance and so on

2011-09-24 09:00:30
Anne-Marie Blackburn
Anne-Marie Blackburn

Thanks Martin - I'd managed to get as far as the trend line but couldn't calculate the trend. I guess I should take the time to learn how to use Excel/Calc/R...

Thanks Robert - that did the trick: I got the same results as Dana :) I'll look into the data analysis toolbar - seems like a good thing to add. I'm actually using Calc right now but figured the function would be the same - and it was. Maybe I need to have a proper look at Excel.

2011-09-24 10:19:44
Dan Friedman

On the chart's trendline ´╗┐options, you should see checkboxes for show equation and show r^2.  You can also force a line to go through (0,0).

2011-09-24 13:19:01
Alex C


If you're using the newest version of Excel you want to double click on the trendline, and that should open up an options menu.  In the older versions, when you tell it you want a trendline it opened up the menu first, and then you got to select your preferences and make the line based on that.  I'm not a big fan of the new setup.

2011-09-28 23:08:51
Mark Richardson

Also look up the LINEST() feature in a how to somewhere.

It's my first look port of call normally. :)