In: Statistics and Probability
State | PPS | T/S Ratio | Avg. Salary | %Takers | Avg. Tot. Score |
"Alabama" | 4.405 | 17.2 | 31.144 | 10 | 1035 |
"Alaska" | 8.963 | 17.6 | 47.951 | 48 | 939 |
"Arizona" | 4.778 | 19.3 | 32.175 | 28 | 950 |
"Arkansas" | 4.459 | 17.1 | 28.934 | 7 | 1010 |
"California" | 4.992 | 24 | 41.078 | 46 | 908 |
"Colorado" | 5.443 | 18.4 | 34.571 | 31 | 985 |
"Connecticut" | 8.817 | 14.4 | 50.045 | 82 | 914 |
"Delaware" | 7.03 | 16.6 | 39.076 | 70 | 902 |
"Florida" | 5.718 | 19.1 | 32.588 | 50 | 895 |
"Georgia" | 5.193 | 16.3 | 32.291 | 66 | 859 |
"Hawaii" | 6.078 | 17.9 | 38.518 | 58 | 895 |
"Idaho" | 4.21 | 19.1 | 29.783 | 16 | 984 |
"Illinois" | 6.136 | 17.3 | 39.431 | 14 | 1054 |
"Indiana" | 5.826 | 17.5 | 36.785 | 59 | 887 |
"Iowa" | 5.483 | 15.8 | 31.511 | 7 | 1105 |
"Kansas" | 5.817 | 15.1 | 34.652 | 11 | 1066 |
"Kentucky" | 5.217 | 17 | 32.257 | 12 | 1005 |
"Louisiana" | 4.761 | 16.8 | 26.461 | 10 | 1027 |
"Maine" | 6.428 | 13.8 | 31.972 | 70 | 902 |
"Maryland" | 7.245 | 17 | 40.661 | 65 | 915 |
"Massachusetts" | 7.287 | 14.8 | 40.795 | 81 | 913 |
"Michigan" | 6.994 | 20.1 | 41.895 | 13 | 1038 |
"Minnesota" | 6 | 17.5 | 35.948 | 10 | 1091 |
"Mississippi" | 4.08 | 17.5 | 26.818 | 5 | 1041 |
"Missouri" | 5.383 | 15.5 | 31.189 | 10 | 1051 |
"Montana" | 5.692 | 16.3 | 28.785 | 23 | 1014 |
"Nebraska" | 5.935 | 14.5 | 30.922 | 10 | 1056 |
"Nevada" | 5.16 | 18.7 | 34.836 | 31 | 922 |
"New Hampshire" | 5.859 | 15.6 | 34.72 | 70 | 941 |
"New Jersey" | 9.774 | 13.8 | 46.087 | 72 | 904 |
"New Mexico" | 4.586 | 17.2 | 28.493 | 12 | 1021 |
"New York" | 9.623 | 15.2 | 47.612 | 76 | 898 |
"North Carolina" | 5.077 | 16.2 | 30.793 | 62 | 871 |
"North Dakota" | 4.775 | 15.3 | 26.327 | 7 | 1113 |
"Ohio" | 6.162 | 16.6 | 36.802 | 25 | 981 |
"Oklahoma" | 4.845 | 15.5 | 28.172 | 11 | 1032 |
"Oregon" | 6.436 | 19.9 | 38.555 | 53 | 953 |
"Pennsylvania" | 7.109 | 17.1 | 44.51 | 72 | 885 |
"Rhode Island" | 7.469 | 14.7 | 40.729 | 70 | 894 |
"South Carolina" | 4.797 | 16.4 | 30.279 | 60 | 849 |
South Dakota" | 4.775 | 14.4 | 25.994 | 6 | 1074 |
"Tennessee" | 4.388 | 18.6 | 32.477 | 14 | 1045 |
"Texas" | 5.222 | 15.7 | 31.223 | 50 | 899 |
"Utah" | 3.656 | 24.3 | 29.082 | 6 | 1082 |
"Vermont" | 6.75 | 13.8 | 35.406 | 70 | 907 |
"Virginia" | 5.327 | 14.6 | 33.987 | 66 | 902 |
"Washington" | 5.906 | 20.2 | 36.151 | 50 | 943 |
"West Virginia" | 6.107 | 14.8 | 31.944 | 19 | 937 |
"Wisconsin" | 6.93 | 15.9 | 37.746 | 10 | 1079 |
"Wyoming" | 6.16 | 14.9 | 31.285 | 12 | 1006 |
We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of Sales here and x_values have Advertising values.
Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.
The equation comes out to be -
avg tot score = 1053.703 + 5.616*PPS -3.37*T/S Ratio + 1.382*Avg Salary - 2.90 * %Taker s
The slopes for the regression line will be 5.616, -3.37, 1.38 and -2.90 for PPS, T/S Ration, Avg Salary and %Taker s respectively.
Interpretation of slopes
When PPS is increased by a unit, the avg total will increase by 5.616 units.
When T/S Ratio is increased by a unit, the avg total will decrease by 3.37 units.
When Avg Salary is increased by a unit, the avg total will increase by 1.382 units.
When %Taker s is increased by a unit, the avg total will decrease by 2.9 units.
The coefficient of determination(R**2) comes out to be 0.824190568, it means 82.4% of the avg total score variable can be explained by all the independent variables.