Question

*****I'm using excel spreadsheet so I'm stuck on how to find a least square straight line after linearizing the relationship. I do not have mini lab. I'm using excel.. The following are the average distances of the planets in the solar system from the sun.

Planet No Planet Distance (millions of miles)

1 Pluto 47.163

2 Venus 67.235

3 Earth 92.960

4 Mars 141.61

5 Asteroids 313.00

6 Jupiter 483.60

7 Saturn 886.70

8 Uranus 1783.0

9 Neptune 2794.0

10 Pluto 2794.0

Find a least squares straight line after linearizing the relationship.

Answer 1

x	y	(x-x̅)²	(y-ȳ)²	(x-x̅)(y-ȳ)
1	47.163	20.25	797741.57	4019.24
2	67.235	12.25	762289.29	3055.82
3	92.96	6.25	718030.49	2118.42
4	141.61	2.25	637948.53	1198.08
5	313	0.25	393538.91	313.66
6	483.6	0.25	208599.37	-228.36
7	886.7	2.25	2875.83	-80.44
8	1783	6.25	710098.12	2106.68
9	2794	12.25	3436104.33	6487.86
10	2794	20.25	3436104.33	8341.53

	ΣX	ΣY	Σ(x-x̅)²	Σ(y-ȳ)²	Σ(x-x̅)(y-ȳ)
total sum	55	9403.268	82.5	11103330.8	27332.48
mean	5.50	940.33	SSxx	SSyy	SSxy

sample size ,   n =   10          
here, x̅ = Σx / n=   5.50   ,     ȳ = Σy/n =   940.33  
                  
SSxx =    Σ(x-x̅)² =    82.5000          
SSxy=   Σ(x-x̅)(y-ȳ) =   27332.5          
                  
estimated slope , ß1 = SSxy/SSxx =   27332.5   /   82.500   =   331.3028
                  
intercept,   ß0 = y̅-ß1* x̄ =   -881.8385          
                  
so, regression line is   Ŷ =   -881.8385   +   331.3028   *x