How to calculate sxx in excel

Regression Terminology

x y (x-xavg) 2

(y-yavg) 2 (x-xavg)*(y-yavg) x

2 y

2 x*y ŷ = b 1 x + b 0 error

(y - ŷ)

error 2

(y - ŷ) 2

2 3 16.00 3.06 7.00 4.00 9.00 6 2.90 0.10 0.01

6 4 9.34 0.56 0.00 36.00 16.00 24 4.75 -0.75 0.56

7 6 9.00 1.56 1.25 49.00 36.00 42 5.21 0.79 0.62

9 6 81.00 1.56 3.75 81.00 36.00 54 6.13 -0.13 0.02

column total 24 19 115.34 6.75 12.00 170.00 97.00 126 19.00 0.00 1.21

column avg (sum/n) 6.00 4.75 28.83 1.69 3.00

column stdev (sample) 2.94 1.50

column stdev (pop) 2.55 1.30 0.78

variance (pop) 6.50 1.69

n = number of data pairs 4 0.61

3.00

r = cov(x,y) / ( σ x * σ y ) = 0.91

r 2 = 0.82

0.46

1.98

covariance (using Excel Covariance function)

Column 1 Column 2

Column 1 6.50 Column 2 3.00 1.69

correlation (using Excel Correlation function)

Column 1 Column 2

Column 1 1

Column 2 0.91 1

r 2

0.82

y = 0.4615x + 1.9808 R² = 0.8205

0

1

2

3

4

5

6

7

0 1 2 3 4 5 6 7 8 9 10

y

x

y vs. x

𝒃𝟏 = 𝒙𝒚 − 𝒏 ∙ 𝒙 ∙ 𝒚

𝒙𝟐 − 𝒏 ∙ 𝒙𝟐 =

𝒃𝟎 = 𝒚 − 𝒃 𝒙 =

𝝈 = 𝒔 = 𝑺𝒕𝒅 𝑬𝒓𝒓𝒐𝒓 𝒐𝒇 𝑬𝒔𝒕𝒊𝒎𝒂𝒕𝒆 = 𝒆𝒓𝒓𝒐𝒓𝟐

𝒏 − 𝟐 =

𝑺𝑺𝑬

𝒏 − 𝟐 =

𝝈𝟐 = 𝒔𝟐 = 𝑴𝑺𝑬 = 𝑴𝒆𝒂𝒏 𝑺𝒒𝒖𝒂𝒓𝒆 𝑬𝒓𝒓𝒐𝒓 =

𝑺𝑺𝑬 = 𝑺𝒖𝒎 𝒐𝒇 𝑺𝒒𝒖𝒂𝒓𝒆 𝑬𝒓𝒓𝒐𝒓

𝒄𝒐𝒗(𝒙,𝒚) = [ 𝒙 − 𝒙 𝒚 − 𝒚 ]

𝒏 =

SYY= SXY=

Process 1. Pick slope-intercept equations of choice 2. Setup table/columns 3. Calculate equation terms 4. Use equations to determine slope, intercept, other stats.

SXX=

This is a continuation of the Regression Exercise assignment. Use Excel to perform a regression analysis that is similar to the one shown below; however, use the data points from the Regression Exercise assignment. DO NOT use the same numbers that are the X and Y values in this example or you will not get credit.

Submit a well-formatted Excel analysis (both the xlsx file and a printout) to BBLearn before the specified due date/time. You may print directly to a pdf file, scan the printout, or take a clear photo of it.