<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=1005900&amp;fmt=gif">


Trending - how does it work?

Have you ever wondered how Excel calculates that nice trend line in your not so perfect data? I’m guessing not and that you take it for granted, and hand in reports without actually knowing the statistics and mathematics in the background. Below I detail how this works and give a worked example in Excel.

When gathering data we usually gather the data based on dependent and independent variables. X is usually the independent variable and y is usually the dependent variable. What we can do is take these x and y values and plot them against each other and generate a graph.

And generate something like this:Cgraph1

Now it’s clear we can see a pattern here. But how do we put a line over these values so that all points are considered equally? Well the answer is that we often use “least squares” fit.

The function of the best fit line to fit each datum:Cformula1

Thus we can consider the difference between the line and each datum:Cformula2

For real data  it could be either positive or negative. Because it may be negative we have to square it. Hence the “least squares” fit. When we sum all of these we can find a minimum for the line of best fit.  We then normalise each datum by its uncertainty σi.Cformula3


Taking partial derivatives:Cformula5

From this we can generate 2 simultaneous equations:Cformula6

These can be easily solved by some matrix manipulation:Cformula7


It follows that:Cformula9

So now we have covered that we are going to look at how it can be applied.

I am going to use the first graph as an example. In Excel I am going to tabulate up values and from this calculate all the values required. Ctable1

From above we can see that the line of least squares should be y=1.042424x+1.666667. Plotting this with the initial line gives the below:Cgraph2