22
$\begingroup$

What are the equations to calculate a linear trendline over a set of points?


EDIT: "In excel it is done automatically but how to manually calculate a linear trendline over a set of points" was originally the question. At first I asked this question because I was simply doing it with excel all the time and couldn't figure out how it computed the result. People tend to focus on the excel part instead of the actual question so I just removed this mention.

3 Answers 3

26

I've been looking for this formula in this website so I've made it Q&A style :) Hope this helps.

Slope;

$$\alpha = {n\sum(xy) - \sum x \sum y \over n\sum x^2 - (\sum x)^2}$$

Offset:

$$\beta = {\sum y - \alpha \sum x \over n}$$

Trendline formula:

$$y = \alpha x + \beta $$

source : https://classroom.synonym.com/calculate-trendline-2709.html

  • 2
    Source? ${}{}{}$2012-09-28
  • 0
    Then I don't see how Excel is connected to what you want. My textbook and many others give you the formulas for the least squares solution to the slope and intercept of a simple linear regression line.2012-09-28
  • 0
    @MichaelChernick i don't see the point arguing with you. You are on a knowledge sharing forum. If you don't seek knowledge but just how to use tools, good for you.2012-09-29
  • 0
    @TecHunter I wasn't arguing. I don't understand what you are doing and how it is really connected to Excel. My probing questions were just efforts to get you to tell me what you need from the regression line and why Excel entered into your question. Seems like I want a simple answer not an argument. All i am trying to do on the "knowledge sharing forum" is to understand your question in case I have some knowledge to share that can help to answer your question.2012-09-29
  • 0
    like you said, i was so used to excel that I couldn't remember how to calculate a regression line. Usually i just enter data then let it do the job. but this time I needed the formula for my program. Excel was just my reference, like you said you can show the equation but not how you come to this equation2012-09-30
  • 0
    @TecHunter : Is there a reason why you put the "equals" signs _outside_ of MathJax? (And also put purposeless curly braces around \alpha and \beta?). Putting "=" outside of MathJax results in lack of font size matching and proper spacing. ${}\qquad{}$2015-11-19
1

For Excel, let $r$ be Pearson's r, $s_x$ the standard deviation and $\bar{X}_x$ the mean of all the numbers on the x-axis, $s_y$ the standard deviation and $\bar{X}_y$ the mean of all the numbers on the y-axis.

Then the slope will be $a = r \frac{s_y}{s_x}$ and y-intercept $b = \bar{X}_y - a \bar{X}_x$, for linear trendline $y = ax + b$.

  • 0
    Thanks for your answer, I would like to understand it but my level of mathematics seems too low. could you detail more? `Pearson's r`? `standard deviation`? thanks!2018-12-04
-1

When I have used the data analysis package for simple linear regression I always get a scatterplot of the data and have an option to add a trendline and also an option for the equation and R square to be printed on the graph. these days this can easily be done with the chart wizard.

  • 0
    yeah but sometimes you need the equation. I recently had to use this equation to manually draw a trendline2012-09-28
  • 0
    Excel compute the equation by least squares and automatically draws the trendline and nothing stops you from asking for the equation to be written out on the plot. If you have Excel why wouldn't you do it this way? It gives you everything you want.2012-09-28
  • 4
    Because I need it for a javascript program where I don't have excel and my points are not static data. You have a calculator that does a lot of things yet sometimes you need to know how to do this by yourself no? anyway if you don't need this maybe someone else like me would need it2012-09-28