# Slope of a line given multiple points

I have data with a starting (y) value that sequentially increments/decrements as (x) time measured in days passes. I found this link for creating a linear regression of the data http://www.easycalculation.com/statistics/regression.php

I would like to automate the slope calculation in excel. Does anyone have an idea of how to do it? I see the math formula at the bottom of the page

$$\frac{N\sum XY- \sum X\sum Y}{N\sum X^2-(\sum X)^2}$$

but i don't know how to translate it to an excel formula. The problem is mainly the $\sum XY$ and $\sum X^2$. The others are easy with the count, sum and pow function. My x coordinates and y coordinates are in rows such that C1 is x1 and D1 is x2.

-
it is more common to have y be the dependent and x the independent (e.g., time) variable. –  Abe May 31 '12 at 1:44
@Abe corrected thank you –  caseyr547 May 31 '12 at 2:05

To implement the equation in Excel:

1. make a new column labeled "XY" in E
2. enter the number of rows in cell "F2" (this will be N)
3. label column F "X^2"
4. enter =C2^2 into F2 to caclulate X^2, highlight F2:FN and hit ctrl+D to fill this equation down
5. enter the equation =(G2*sum(E:E) - sum(C:C)*sum(D:D))/(G2*sum(F:F) - sum(C:C)^2) into an empty cell. This will be your R^2
6. enter the equation =slope(D:D, C:C) into another empty cell, this should match your calculation.

Finished result is shown in an example google doc that can be downloaded in Excel format here.

-
thanks give me a moment to implement it and i will ask a question –  caseyr547 May 31 '12 at 2:18
your example shows x1 at a1 and x2 at a2 my x1 is at c1 and my x2 is at d2 –  caseyr547 May 31 '12 at 2:26
i guess i have to create n new columns for it to work –  caseyr547 May 31 '12 at 2:26
@caseyr547 Sorry, that was the linked to the 'published' version; I have updated the link to the raw spreadsheet, or you could download an excel version here –  David May 31 '12 at 2:33

A solution with R and the example data posted by @David and instructions on accessing data from google spreadsheets from the Revolutions blog

require(RCurl)

x <- mydata$X y <- mydata$Y
n <- nrow(mydata)
xy <- x*y
m <- (n*sum(xy)-sum(x)*sum(y)) / (n*sum(x^2)-sum(x)^2)
m

Or, you could use R's built-in function

lm(y~x)
-
thanks but my data isnt in that format my x1 is at c1 and my x2 is at d1 the xs and ys are on separate rows rather than separate colomns. –  caseyr547 May 31 '12 at 2:31
then you can use t(mydata) to transpose the matrix, or do this in excel using paste -> special -> transpose –  Abe May 31 '12 at 2:50