Example_of_a_RegressionCorrelation

# Example_of_a_RegressionCorrelation - Using Excel to Find...

This preview shows pages 1–3. Sign up to view the full content.

Using Excel to Find Regression and Correlation: Method A - Table Suppose you are given the following data x y 1 1 2 1 3 3 4 2 A basic scatter diagram would be below (to draw a scatter diagram, highlight data, insert a chart and select scatte We want to find the linear regression equation and the R-squared value We will use the following table where the values are defined by the table headings x y x^2 xy y^2 1 1 1 1 1 2 1 4 2 1 3 3 9 9 9 4 2 16 8 4 Sum 10 7 30 20 15 These sums can now be used to calculate the values we need for the least-squares line. (page 135 in text) y = a + bx b = SSxy / SSx a = ybar - b * xbar number of data = 4 xbar = mean of x data = Sum x column / number of data = 10/4 = 2.5 xbar = 2.5 ybar = mean of y data = Sum y column / number of data = 7/4 = 1.75 ybar = 1.75 SSx = Sum of x^2 column - [ (Sum of x column)^2 / number of data ] = 30 - [10^2 / 4] = 30 - 100/4 = 30 - 25 = 5 0.5 1 1.5 2 2.5 3 3.5 4 4.5 0 0.5 1 1.5 2 2.5 3 3.5 Example x y

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
SSx = 5 SSxy = Sum xy column - [ (Sum of x column * Sum of y column) / number of data ] = 20 - [ 10 * 7 / 4] = 20 - 70/4 = SSxy = 2.5 b = SSxy / SSx = 2.5 / 5 = 0.5 b = 0.5 a = ybar - b * xbar = 1.75 - 0.5 * 2.5 = 1.75 - 1.25 = 0.5 a = 0.5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern