In: Statistics and Probability
Calories (C) |
Sugar (S) |
Fat (F) |
Carbohydrates (R) |
100 |
12 |
0.5 |
25 |
130 |
11 |
1.5 |
29 |
100 |
1 |
2 |
20 |
130 |
15 |
2 |
31 |
130 |
13 |
1.5 |
29 |
120 |
3 |
0.5 |
26 |
100 |
2 |
0 |
24 |
120 |
10 |
0 |
29 |
150 |
16 |
1.5 |
31 |
110 |
4 |
0 |
25 |
110 |
12 |
1 |
25 |
150 |
15 |
0 |
36 |
160 |
15 |
1.5 |
35 |
150 |
12 |
2 |
29 |
150 |
15 |
1.5 |
29 |
110 |
6 |
1 |
23 |
190 |
19 |
1.5 |
45 |
100 |
3 |
0 |
23 |
120 |
4 |
0.5 |
23 |
120 |
11 |
1.5 |
28 |
130 |
5 |
0.5 |
29 |
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 Calories here and x_values have sugar values in the first case and carbohydrates in the second.
Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.
The equations come out to be -
Sugar = -12.84 + 0.18*Calories
carbohydrates = 1.07 + 0.21*Calories
When calories = 120
Sugar = -12.84 + 0.18*120 = -12.84 + 21.6 = 8.76
carbohydrates = 1.07 + 0.21*120 = 1.07 + 25.2 = 26.27
The multiple regression equation comes out to be -
Calories = 12.92908 -0.26828*Sugar + 7.57*Fat + 3.89*carbohydrates
When fat = 0.5, sugar = 7, carbohydrates = 31
Calories = 12.92908 -0.26828*7+ 7.57*0.5+ 3.89*31
Calories = 12.92908 - 1.87796 + 3.785 + 120.59
Calories = 135.42