# A tibble: 5 × 2
StoreID MeanCH
<dbl> <dbl>
1 1 1.80
2 2 1.84
3 3 1.93
4 4 1.95
5 7 1.84
Answers to Exercises
Every exercise answer in the book, collected in one place. Try each problem on your own before you look.
1 Descriptive Stats I
Exercise 1, question 1
The “Title” variable represents the names of books. Therefore, this is a categorical variable.
“Price” represents the cost of each book in a numeric format, making it a numerical variable.
“Year Published” indicates the publication year of each book. It is numerical.
If “Rating” represents a numerical score based on a continuous scale (e.g., average user ratings on a platform like Goodreads), it is numerical because arithmetic operations like averaging or comparing differences are meaningful. If “Rating” represents predefined categories (e.g., “Excellent,” “Good,” “Fair,” “Poor”) or is interpreted as ranks without meaningful differences between values, it would be categorical.
Exercise 1, question 2
The measurement scale is nominal for “Title” since these are labels used to identify each book and do not have a numerical meaning or order.
If “Rating” represents a score (e.g., 4.2, 4.0) given to each book, it is numerical and could be considered interval data because the scale represents a meaningful difference, but it may not have an absolute zero or meaningful ratios (e.g., a book rated 4.0 is not “twice as good” as one rated 2.0).
“Price” is a measurable quantity with a meaningful zero (e.g., a book priced at $0 means it is free), making it ratio data.
“Year” is interval data because the zero point is arbitrary (year 0 does not represent the absence of time) and differences between years are meaningful (e.g., 1897 - 1818 = 79 years).
Exercise 2, question 1
The variable Year is measured on the interval scale because the observations can be ranked, categorized and measured when using this kind of scale. However, there is no true zero point so we cannot calculate meaningful ratios between years.
Exercise 2, question 2
The variable Quarter is measured on the ordinal scale, even though it contains numbers.
Exercise 2, question 3
The variable Deliveries is measured on the ratio scale. It is the strongest level of measurement because it allows us to categorize and rank the data as well as find meaningful differences between observations. Also, with a true zero point, we can interpret the ratios between observations.
Exercise 3
- Customer ID: Categorical, Nominal. These are arbitrary labels used only for identification. There is no order or numerical meaning; you can only determine if two IDs are the same or different.
- Satisfaction Level: Categorical, Ordinal. The ratings have a clear order (higher numbers indicate greater satisfaction), but the intervals between levels are not necessarily equal (the difference between 4 and 5 may not be perceived the same as between 1 and 2). Ratios are not meaningful.
- Visit Duration: Numerical, Ratio. Time in minutes has a true zero (0 minutes means no time spent), equal intervals, and meaningful ratios (e.g., 60 minutes is twice as long as 30 minutes). All arithmetic operations are valid.
- Beverage Type: Categorical, Nominal. These are unordered categories/labels for different drinks. You can group or count them, but there is no natural order or numerical relationship between categories like “Latte” and “Tea”.
2 Descriptive Stats II
Exercise 1, question 1
The most common diagnosis is Meningioma, a slow-growing tumor that forms from the membranous layers surrounding the brain and spinal cord. The diagnosis represents about \(48.28\)% of the sample.
| Diagnosis | Frequency | Relative |
|---|---|---|
| Meningioma | 42 | 48.28% |
| LG glioma | 9 | 10.34% |
| HG glioma | 22 | 25.29% |
| Other | 14 | 16.09% |
In Excel, build a PivotTable: drag the diagnosis field to the Rows area and again to the Values area (Excel counts each category). To add the relative frequency, drag diagnosis to the Values area a second time and set Value Field Settings → Show Values As → % of Column Total. Alternatively, use =COUNTIF(range, "Meningioma") for each category and divide by the total =COUNTA(range).
Exercise 1, question 2
The majority of diagnoses are Meningioma. Low grade glioma is the least common. High grade glioma and Other have about the same frequency.
With the PivotTable selected, go to Insert → Charts → Clustered Column to create the bar chart.
Exercise 1, question 3
\(33\) people did not survive Meningioma, the highest number of non-survivals. The survival rate of Meningioma is only \(21.43\)%.
| Status | Meningioma | LG glioma | HG glioma | Other |
|---|---|---|---|---|
| 0 | 33 | 5 | 5 | 9 |
| 1 | 9 | 4 | 17 | 5 |
In Excel, build a PivotTable with diagnosis in the Columns area, status in the Rows area, and status in the Values area as a count. To obtain the survival rates, set Show Values As → % of Column Total; the survival rate for Meningioma is \(9/42 = 21.43\)%.
Exercise 2, question 1
The highest frequency is in the \(80 < x \le 90\) bin. \(8\) temperatures were between \(50 < x \le 60\) degrees.
| Interval | Frequency |
|---|---|
| \((50,60]\) | 8 |
| \((60,70]\) | 25 |
| \((70,80]\) | 52 |
| \((80,90]\) | 54 |
| \((90,100]\) | 14 |
In Excel, add a helper column with an =IFS() formula to assign each temperature to its class, then build a PivotTable to count the observations in each bin, exactly as shown in the chapter.
Exercise 2, question 2
The temperature was \(5.23\)% of the time between \(50\) and \(60\); the temperature was \(70\) or less \(33\) times; the temperature was above \(70\), \(78.43\)% of the time.
| Interval | Frequency | Relative | Cumulative | Rel. Cumulative |
|---|---|---|---|---|
| \((50,60]\) | 8 | 5.23% | 8 | 5.23% |
| \((60,70]\) | 25 | 16.34% | 33 | 21.57% |
| \((70,80]\) | 52 | 33.99% | 85 | 55.56% |
| \((80,90]\) | 54 | 35.29% | 139 | 90.85% |
| \((90,100]\) | 14 | 9.15% | 153 | 100.00% |
In the PivotTable, drag the bin field to the Values area a second time and use Show Values As → % of Column Total for the relative frequency, and Show Values As → Running Total In for the cumulative and relative cumulative distributions.
Exercise 2, question 3
The distribution is not perfectly symmetric. It is skewed slightly to the left.
With the PivotTable selected, insert a Clustered Column chart and set the Gap Width to \(0\)% to turn it into a histogram.
Exercise 3, question 1
From \(1901\) through \(2000\), both portfolios have behaved very similarly. Returns are between \(-3\)% and \(3\)%, there is no trend, and positive (negative) returns for X seem to match with positive (negative) returns of Y.
In Excel, add a Year column running from \(1901\) to \(2000\). Select the Year, X, and Y columns and go to Insert → Charts → Line. Excel plots one line per portfolio and adds a legend automatically. Use Chart Design → Change Colors (or right-click a series → Format Data Series) to give X and Y distinct colors.
Exercise 4, question 1
| Department | Frequency |
|---|---|
| Customer Service | 6 |
| Finance | 8 |
| HR | 3 |
| IT | 2 |
| Marketing | 7 |
| Operations | 6 |
| R&D | 3 |
| Sales | 5 |
In Excel, build a PivotTable with dept in the Rows area and again in the Values area as a count, or use =COUNTIF(range, "Finance") for each department.
Exercise 4, question 2
| Department | Relative |
|---|---|
| Customer Service | 15.0% |
| Finance | 20.0% |
| HR | 7.5% |
| IT | 5.0% |
| Marketing | 17.5% |
| Operations | 15.0% |
| R&D | 7.5% |
| Sales | 12.5% |
In the PivotTable, drag dept to the Values area a second time and set Show Values As → % of Column Total. Alternatively, divide each =COUNTIF() result by the total =COUNTA(range).
Exercise 4, question 3
The highest frequency is in the Finance department with 8 employees. The relative frequency is 20%.
With the PivotTable selected, go to Insert → Charts → Clustered Column to create the bar chart.
Exercise 5, question 1
The highest frequency is in the [50,60) bin with 13 students.
| Bin | Frequency |
|---|---|
| \([50,60)\) | 13 |
| \([60,70)\) | 11 |
| \([70,80)\) | 9 |
| \([80,90)\) | 9 |
| \([90,100)\) | 7 |
In Excel, add a helper column with an =IFS() formula to assign each score to its class, build a PivotTable to count the scores in each bin, then insert a Clustered Column chart and set the Gap Width to \(0\)% to turn it into a histogram.
3 Descriptive Statistics III
Exercise 1, question 1
To find the mean we will use the following formula \(( \frac{1}{n} \sum_{i=i}^{n} x_{i})\). The summation of the values is \(51\) and the number of observations is \(5\). The mean is \(51/5=10.2\).
The median is found by locating the middle value when data is sorted in ascending order. \(x_{sorted}=[8,9,10,12,12]\). The median in this example is \(10\).
The mode is the value with the highest frequency. In this example the mode is \(12\) since it is repeated twice and all other numbers appear only once.
To verify in Excel, enter the data in cells A1:A5 and use:
=AVERAGE(8,9,10,12,12)→ \(10.2\)=MEDIAN(8,9,10,12,12)→ \(10\)=MODE(8,9,10,12,12)→ \(12\)
Exercise 1, question 2
The mean is \(-2.67\), the median is \(-3.5\), the mode is \(-4\). To verify in Excel, enter the data in cells A1:A6 and use:
=AVERAGE(-4,0,-6,1,-3,-4)→ \(-2.67\)=MEDIAN(-4,0,-6,1,-3,-4)→ \(-3.5\)=MODE(-4,0,-6,1,-3,-4)→ \(-4\)
Exercise 1, question 3
The mean is \(18.33\), the median is \(20\), the data is bimodal with both \(15\) and \(20\) being modes.
To verify in Excel, enter the data in cells A1:A9 and use:
=AVERAGE(20,15,25,20,10,15,25,20,15)→ \(18.33\)=MEDIAN(20,15,25,20,10,15,25,20,15)→ \(20\)=MODE.MULT(20,15,25,20,10,15,25,20,15)→ \(15\) and \(20\) (note: useMODE.MULTinstead ofMODEto capture all modes)
Exercise 2, question 1
The mean price for Country Hill is \(1.87\). The mean price for Minute Maid is \(2.09\).
To verify in Excel, assuming PriceCH is in column D and PriceMM is in column E, use:
=AVERAGE(D2:D1071)→ \(1.87\)=AVERAGE(E2:E1071)→ \(2.09\)
Exercise 2, question 2
The mean price at store 1 for Country Hill is \(1.80\). The juice is cheaper at store 1.The mean price at each store is:
To verify in Excel, use AVERAGEIF to filter by store. Assuming StoreID is in column C and PriceCH is in column D:
=AVERAGEIF(C2:C1071, 1, D2:D1071)→ store 1 mean=AVERAGEIF(C2:C1071, 2, D2:D1071)→ store 2 mean=AVERAGEIF(C2:C1071, 3, D2:D1071)→ store 3 mean
Exercise 2, question 3
Purchasers of Country Hill at store 1 paid a median price of \(1.76\) for Country Hill juice. This once again was the lowest price.The median price for Country Hill purchasers at each store is given by:
# A tibble: 5 × 2
StoreID MedianCH
<dbl> <dbl>
1 1 1.76
2 2 1.86
3 3 1.99
4 4 1.99
5 7 1.86
To verify in Excel, use MEDIAN with IF as an array formula. Assuming Purchase is in column A, StoreID in column C, and PriceCH in column D. Enter with Ctrl+Shift+Enter:
=MEDIAN(IF((C2:C1071=1)*(A2:A1071="CH"), D2:D1071))→ store 1 median
Repeat changing the store number for each store.
Exercise 3, question 1
The average price of sale is found by using the weighted average formula. \(\frac{\sum w_{i}x_{i}}{\sum w_{i}}\) The weights (\(w_{i}\)) are given by the number of shares bought and the values (\(x_{i}\)) are the prices. The weighted average is \(246.802\).
To verify in Excel use:
=SUMPRODUCT({250.34,234.59,270.45},{80,120,50}) / SUM(80,120,50)→ \(246.802\)
Exercise 3, question 2
The average if equal shares were bought would be \(251.7933\).
=AVERAGE(250.34,234.59,270.45)→ \(251.793\)
Exercise 4, question 1
The inflation rate is the percentage change in the CPI. The inflation rate for each period is shown in the table below:
| 1-2 | 2-3 | 3-4 | 4-5 |
|---|---|---|---|
| 30% | 23.08% | 12.5% | 16.67% |
Exercise 4, question 2
*The average growth rate is \(20.38%\). Use the geometric mean formula with compounding.
\[\bar x_{g}=(1.3 \times 1.2308 \times 1.125 \times 1.1667)^{1/4}-1\] \[\bar x_{g}=0.2038\]
You can use the command GEOMEAN(1.3,1.2308,1.125,1.1667)-1 in Excel to verify the result.
Exercise 5
At the end of 4 years it is predicted that you would have \(1621.17\) dollars. Each year you would have gained \(12.84\)% on average.
To verify in Excel use:
=AVERAGE(0.173, 0.196, 0.068, 0.082)→ \(0.12975\) arithmetic mean=GEOMEAN(1.173, 1.196, 1.068, 1.082)-1→ \(0.1173\) geometric mean=1000*(1+0.1173)^4→ \(1621.17\)
Exercise 6, question 1
The mean and the median salary of the hitters is 535 and 425 respectively.Due to right-skew from superstar salaries, the median better represents the typical player.
To verify in Excel, assuming salaries are in column S, use:
=AVERAGE(S2:S323)→ \(535\)=MEDIAN(S2:S323)→ \(425\)
Exercise 6, question 2
For players with ≥1 home run: mean higher (around 600+), median still lower than mean; skewness persists, median again more representative.
To verify in Excel, assuming salaries are in column S and home runs in column C:
=AVERAGEIF(C2:C323, ">=1", S2:S323)→ mean salary=MEDIAN(IF(C2:C323>=1, S2:S323))→ median salary
4 Descriptive Stats IV
Exercise 1, question 1
The mean is \(60\), the Range is \(94\), the MAD is \(28\), the variance is \(1186\) and the variance is \(34.44\).
Here is the table of deviations:
| \(x_{i}\) | \(x_{i}-\bar{x}\) | \((x_{i}-\bar{x})^2\) | \(|x_{i}-\bar{x}|\) |
|---|---|---|---|
| 70 | 10 | 100 | 10 |
| 68 | 8 | 64 | 8 |
| 4 | -56 | 3136 | 56 |
| 98 | 38 | 1444 | 38 |
The variance averages out the squared deviations \((x_{i}-\bar{x})^2\), the MAD averages out the absolute deviations \(|x_{i}-\bar{x}|\), and the standard deviation is the square root of the variance.
Exercise 1, question 2
The mean is \(-2\), Range is \(7\), the MAD is \(2.33\), the variance is \(7.6\) and the standard deviation is \(2.76\).
Here is the table of deviations from the mean:
| \(x_{i}\) | \(x_{i}-\bar{x}\) | \((x_{i}-\bar{x})^2\) | \(|x_{i}-\bar{x}|\) |
|---|---|---|---|
| -4 | -2 | 4 | 2 |
| 0 | 2 | 4 | 2 |
| -6 | -4 | 16 | 4 |
| 1 | 3 | 9 | 3 |
| -3 | -1 | 1 | 1 |
| 0 | 2 | 4 | 2 |
Exercise 1, question 3
The mean is \(6\), Range is \(8\), the MAD is \(2\), the variance is \(8\) and the standard deviation is \(2.83\).
Here is the table of deviations from the mean:
| \(x_{i}\) | \(x_{i}-\bar{x}\) | \((x_{i}-\bar{x})^2\) | \(|x_{i}-\bar{x}|\) |
|---|---|---|---|
| 2 | -4 | 16 | 4 |
| 8 | 2 | 4 | 2 |
| 4 | -2 | 4 | 2 |
| 10 | 4 | 16 | 4 |
| 6 | 0 | 0 | 0 |
| 6 | 0 | 0 | 0 |
The range is \(Range = 10 - 2 = 8\).
The MAD is \(mad = (4+2+2+4+0+0)/6 = 12/6 = 2\).
The sample variance is \(s^2 = (16+4+4+16+0+0)/(6-1) = 40/5 = 8\).
The standard deviation is \(s = \sqrt{8} = 2.83\).
Exercise 2, question 1
For the sample taken, GBTC has the less variation. The standard deviation of GBTC is \(9.43\), which is less than \(16.57\) for VTI or \(50.38\) for TSLA.
Exercise 2, question 2
The answer is the same, since the MAD for GBTC is \(8.46\) which is lower than \(14.27\) for VTI or \(41.67\) for TSLA.
Exercise 2, question 3
By considering the magnitudes of the stock prices, it seems like VTI is the less volatile stock. VTI has a CV of \(0.08\) which is lower than \(0.44\) for GBTC or \(0.18\) for TSLA. In fact, by CV Bitcoin seems to be the most risky asset.
Exercise 3, question 1
The best performing stock on average is stock X. It has an average return of \(-0.078\)% vs. \(0.097\)% for stock Y. The safest stock is stock X as well, since the standard deviation is \(1.062\) percentage points vs. \(1.14\) percentage points for stock Y.
Exercise 3, question 2
*The Sharpe Ratio measures the excess return per unit of risk taken. Stock X has the better Sharpe Ratio \(-0.106\) vs. \(-0.115\) for stock Y. Stock X is recommended since it provides a higher excess return per unit of risk taken.
Exercise 4
Start by computing the sample mean:
\[\bar{x} = \frac{2.1 + (-0.5) + 3.0 + 1.2 + (-1.8) + 0.7 + 2.5 + (-0.3)}{8} = \frac{6.9}{8} = 0.8625\]
Here is the table of deviations from the mean:
| \(x_{i}\) | \(x_{i}-\bar{x}\) | \((x_{i}-\bar{x})^2\) | \(|x_{i}-\bar{x}|\) |
|---|---|---|---|
| 2.1 | 1.2375 | 1.5314 | 1.2375 |
| -0.5 | -1.3625 | 1.8564 | 1.3625 |
| 3.0 | 2.1375 | 4.5689 | 2.1375 |
| 1.2 | 0.3375 | 0.1139 | 0.3375 |
| -1.8 | -2.6625 | 7.0889 | 2.6625 |
| 0.7 | -0.1625 | 0.0264 | 0.1625 |
| 2.5 | 1.6375 | 2.6814 | 1.6375 |
| -0.3 | -1.1625 | 1.3514 | 1.1625 |
The sample variance is:
\[s^2 = \frac{\sum(x_i - \bar{x})^2}{n-1} = \frac{19.2188}{7} = 2.7455\]
The sample standard deviation is:
\[s = \sqrt{2.7455} = 1.657\]
The Mean Absolute Deviation is:
\[mad = \frac{\sum|x_i - \bar{x}|}{n} = \frac{10.7}{8} = 1.3375\]
The Coefficient of Variation is:
\[CV = \frac{s}{\bar{x}} \times 100 = \frac{1.657}{0.8625} \times 100 = 192.1\%\]
The CV is about \(192\%\), which is large: the standard deviation is almost twice the mean. This indicates the returns are highly variable relative to their average: the mean is small compared with typical month-to-month fluctuations. In practice, a CV this large signals that the project’s returns are unreliable; an investor cannot count on earning close to the average return in any given month.
Exercise 5, question 1
The sample mean is:
\[\bar{x} = \frac{120 + (-85) + 200 + (-150) + 95 + (-60) + 310 + (-220) + 175 + (-45)}{10} = \frac{340}{10} = 34\]
The bettor is profitable on average, earning \(\$34\) per week.
Here is the table of deviations from the mean:
| \(x_{i}\) | \(x_{i}-\bar{x}\) | \((x_{i}-\bar{x})^2\) | \(|x_{i}-\bar{x}|\) |
|---|---|---|---|
| 120 | 86 | 7,396 | 86 |
| -85 | -119 | 14,161 | 119 |
| 200 | 166 | 27,556 | 166 |
| -150 | -184 | 33,856 | 184 |
| 95 | 61 | 3,721 | 61 |
| -60 | -94 | 8,836 | 94 |
| 310 | 276 | 76,176 | 276 |
| -220 | -254 | 64,516 | 254 |
| 175 | 141 | 19,881 | 141 |
| -45 | -79 | 6,241 | 79 |
The sample variance is:
\[s^2 = \frac{262{,}340}{9} = 29{,}149\]
The sample standard deviation is:
\[s = \sqrt{29{,}149} = \$170.73\]
The MAD is:
\[mad = \frac{86+119+166+184+61+94+276+254+141+79}{10} = \frac{1{,}460}{10} = \$146\]
The bettor is profitable on average but faces large week-to-week swings. A typical week deviates from the mean by about \(\$146\) to \(\$171\) depending on the measure used.
Exercise 5, question 2
\[CV = \frac{170.73}{34} \times 100 = 502\%\]
A CV of \(502\%\) is extremely large (i.e., the standard deviation is five) times the mean. This indicates that the average weekly profit of \(\$34\) is tiny relative to the typical week-to-week swings of \(\$171\). The bettor cannot reliably count on earning near the mean in any given week. A high CV signals that the strategy is highly risky relative to its average reward.
Exercise 5, question 3
With a risk-free rate of \(\$0\):
\[SR = \frac{\bar{x} - R_f}{s} = \frac{34 - 0}{170.73} = 0.199\]
A Sharpe ratio of \(0.199\) is low. As a point of reference, a Sharpe ratio above \(1\) is generally considered acceptable in finance. The bettor earns only \(\$0.20\) of profit per dollar of risk taken, suggesting that while the strategy is marginally profitable, the risk taken to achieve that profit is not well compensated. A more disciplined betting strategy or better odds selection would be needed to improve the risk-adjusted return.
5 Descriptive Stats V
Exercise 1, question 1
The minimum is \(0\), the first quartile is \(2\), second quartile is \(4\), third quartile is \(8\), and maximum is \(30\). \(30\) is an outlier since it is beyond \(Q_{3}+1.5 \times IQR\).
Quartiles are calculated with the location formula \(L_p = 1 + k(n-1)\). The sorted data is \(\{0,1,3,4,6,10,30\}\) with \(n = 7\):
- \(L_{25} = 1 + 0.25(6) = 2.5 \Rightarrow Q_1 = 1 + 0.5(3-1) = 2\)
- \(L_{50} = 1 + 0.50(6) = 4 \Rightarrow Median = 4\)
- \(L_{75} = 1 + 0.75(6) = 5.5 \Rightarrow Q_3 = 6 + 0.5(10-6) = 8\)
In Excel we get the five point summary with =QUARTILE.INC():
=QUARTILE.INC(A:A, 0) /* Minimum = 0 */
=QUARTILE.INC(A:A, 1) /* Q1 = 2 */
=QUARTILE.INC(A:A, 2) /* Median = 4 */
=QUARTILE.INC(A:A, 3) /* Q3 = 8 */
=QUARTILE.INC(A:A, 4) /* Maximum = 30 */
The interquartile range is needed to determine if there are any outliers. The \(IQR = Q_3 - Q_1 = 6\). Since \(Q_3 + 1.5 \times IQR = 8 + 1.5(6) = 17\), everything beyond \(17\) is an outlier, so \(30\) is an outlier.
=QUARTILE.INC(A:A, 3) - QUARTILE.INC(A:A, 1) /* IQR = 6 */
=QUARTILE.INC(A:A, 3) + 1.5 * 6 /* UL = 17 */
Exercise 1, question 2
If we use the \(z\)-score instead we find that \(30\) is not an outlier since the \(z\)-score is \(Z_{30}=2.15\). This observation is only \(2.15\) standard deviations away from the mean.
In Excel we make a quick calculation of the \(z\)-score to confirm our result. The \(z\)-score is \(Z_{i}=\frac{x_{i}-\bar{x}}{s}\):
=STANDARDIZE(30, AVERAGE(A:A), STDEV.S(A:A)) /* = 2.15 */
Since \(2.15 < 3\), the value \(30\) is not flagged as an outlier by the \(z\)-score method, even though the IQR method flagged it. The two methods can disagree.
Exercise 1, question 3
Chebyshev’s theorem states that \(1-\frac{1}{z^{2}}\) of the data lies between \(z\) standard deviations from the mean.
Substituting the \(z\)-score found in 2. we get that at least \(78.34\%\) of the data lies within that many standard deviations of the mean. In Excel:
=1 - 1/(2.15^2) /* ≈ 0.7834, i.e. 78.34% */
Exercise 2, question 1
\[\bar{x} = \frac{2+2+2+6+13}{5} = \frac{25}{5} = 5\]
\[s = \sqrt{\frac{9+9+9+1+64}{4}} = \sqrt{23} = 4.796\]
In Excel:
=AVERAGE(A2:A6) /* mean = 5 */
=STDEV.S(A2:A6) /* s = 4.796 */
Exercise 2, question 2
| \(x_i\) | \(x_i - \bar{x}\) | \(\frac{x_i-\bar{x}}{s}\) | \(\left(\frac{x_i-\bar{x}}{s}\right)^3\) | \(\left(\frac{x_i-\bar{x}}{s}\right)^4\) |
|---|---|---|---|---|
| 2 | \(-3\) | \(-0.625\) | \(-0.244\) | \(0.153\) |
| 2 | \(-3\) | \(-0.625\) | \(-0.244\) | \(0.153\) |
| 2 | \(-3\) | \(-0.625\) | \(-0.244\) | \(0.153\) |
| 6 | \(1\) | \(0.208\) | \(0.009\) | \(0.002\) |
| 13 | \(8\) | \(1.667\) | \(4.630\) | \(7.716\) |
In Excel, put the standardized value in a helper column and copy it down, then square and cube it:
=(A2 - AVERAGE($A$2:$A$6)) / STDEV.S($A$2:$A$6) /* z */
=B2^3 /* z^3 */
=B2^4 /* z^4 */
Exercise 2, question 3
\[Sk = \frac{5}{(4)(3)}\sum\left(\frac{x_i-\bar{x}}{s}\right)^3 = \frac{5}{12}(-0.244-0.244-0.244+0.009+4.630)\] \[= \frac{5}{12}(3.907) = 1.628\]
6 Regression I
Exercise 1, question 1
The covariance is \(14.9\) and the correlation is \(0.96\). The results indicate that there is a strong direct relationship between the two variables.
Start by finding the mean of each variable: \(\bar{x} = 19.8\) and \(\bar{y} = 16.6\). Then build the deviations table:
| \(x_i\) | \(y_i\) | \(x_i-\bar{x}\) | \(y_i-\bar{y}\) | Product |
|---|---|---|---|---|
| 20 | 17 | \(0.2\) | \(0.4\) | \(0.08\) |
| 21 | 19 | \(1.2\) | \(2.4\) | \(2.88\) |
| 15 | 12 | \(-4.8\) | \(-4.6\) | \(22.08\) |
| 18 | 13 | \(-1.8\) | \(-3.6\) | \(6.48\) |
| 25 | 22 | \(5.2\) | \(5.4\) | \(28.08\) |
\[s_{xy} = \frac{0.08+2.88+22.08+6.48+28.08}{4} = \frac{59.6}{4} = 14.9\]
\[r = \frac{14.9}{sd(x) \times sd(y)} = \frac{14.9}{3.49 \times 4.34} = 0.96\]
To verify in Excel, enter x in column A and y in column B and use:
=COVARIANCE.S(A2:A6, B2:B6)→ \(14.9\)=CORREL(A2:A6, B2:B6)→ \(0.96\)
Exercise 1, question 2
The covariance is \(0.85\) and the correlation is \(0.148\). The results indicate that there is a very weak direct relationship between the two variables. They might be unrelated.
To verify in Excel, enter w in column A and z in column B and use:
=COVARIANCE.S(A2:A6, B2:B6)→ \(0.85\)=CORREL(A2:A6, B2:B6)→ \(0.148\)
Exercise 2, question 1
The correlation coefficient is \(-0.78\). This is indicative of a moderately strong inverse relationship between mpg and hp. As horsepower increases, fuel efficiency tends to decrease.
In Excel, assuming mpg is in column A and hp is in column B:
=CORREL(A2:A33, B2:B33)→ \(-0.78\)
Exercise 2, question 2
The scatter diagram is downward sloping and most points are close to the trend line. It is what was expected from a correlation coefficient of \(-0.78\).
In Excel, select the hp and mpg columns and go to Insert → Charts → Scatter. To add the trend line, right-click any point and choose Add Trendline → Linear.
Exercise 2, question 3
The coefficient of determination is \(0.60\). This value is not very close to one. This is expected since miles per gallon can also vary because of the car’s weight and fuel efficiency. It makes sense that hp only explains \(60\%\) of the total variation.
In Excel, square the correlation coefficient:
=CORREL(A2:A33, B2:B33)^2→ \(0.60\)
Exercise 3, question 1
It seems like there is a direct relationship between both variables. The more debt a graduate takes on, the higher the earnings tend to be.
In Excel, select the MD_EARN_WNE_P10 and GRAD_DEBT_MDN columns and go to Insert → Charts → Scatter. Add a linear trend line by right-clicking a point and choosing Add Trendline → Linear.
Exercise 3, question 2
The correlation coefficient shows a moderate direct relationship between earnings and debt, \(r = 0.46\). The coefficient of determination indicates that only \(21\%\) of the variation in earnings can be explained by debt.
In Excel, assuming GRAD_DEBT_MDN is in column A and MD_EARN_WNE_P10 is in column B:
=CORREL(A2:A1000, B2:B1000)→ \(0.46\)=CORREL(A2:A1000, B2:B1000)^2→ \(0.21\)
Note: use =CORREL() rather than =COVARIANCE.S() when there are missing values, as CORREL handles them more gracefully in Excel.
Exercise 4, question 1
There is a direct relationship between bedrooms and price. As the number of bedrooms increases, sale prices tend to rise. This is intuitive since larger homes generally command higher market values.
In Excel, select the bedrooms and Price columns and go to Insert → Charts → Scatter. Add a linear trend line by right-clicking a point and choosing Add Trendline → Linear.
Exercise 4, question 2
The correlation coefficient is \(r = 0.92\), indicating a strong direct relationship between bedrooms and sale price. Larger homes tend to sell for higher prices, though many other factors (location, condition, amenities) also influence price, which prevents a perfect correlation.
In Excel, assuming bedrooms is in column A and Price is in column B:
=CORREL(A2:A348, B2:B348)→ \(0.92\)
Exercise 4, question 3
The coefficient of determination is \(R^2 = 0.85\), so about \(85\%\) of the variation in home prices is explained by the number of bedrooms alone. The remaining variation is likely driven by factors such as location, number of bathrooms, lot size, school district quality, and the overall condition of the property.
In Excel, square the correlation coefficient:
=CORREL(A2:A348, B2:B348)^2→ \(0.85\)
7 Regression II
Exercise 1, question 1
The regression line is \(\hat{y}=-4.93+1.09x\). For each unit increase in \(x\), \(y\) increases on average by \(1.09\).
Start with the deviations from the mean (\(\bar{x}=19.8\), \(\bar{y}=16.6\)):
| \(x_i\) | \(y_i\) | \(x_i-\bar{x}\) | \(y_i-\bar{y}\) | Product | \((x_i-\bar{x})^2\) |
|---|---|---|---|---|---|
| 20 | 17 | \(0.2\) | \(0.4\) | \(0.08\) | \(0.04\) |
| 21 | 19 | \(1.2\) | \(2.4\) | \(2.88\) | \(1.44\) |
| 15 | 12 | \(-4.8\) | \(-4.6\) | \(22.08\) | \(23.04\) |
| 18 | 13 | \(-1.8\) | \(-3.6\) | \(6.48\) | \(3.24\) |
| 25 | 22 | \(5.2\) | \(5.4\) | \(28.08\) | \(27.04\) |
\[\hat{\beta}=\frac{\sum(x_i-\bar{x})(y_i-\bar{y})}{\sum(x_i-\bar{x})^2}=\frac{59.6}{54.8}=1.09\]
\[\hat{\alpha}=\bar{y}-\hat{\beta}\bar{x}=16.6-1.09(19.8)=-4.93\]
To verify in Excel, enter x in column A and y in column B. A single LINEST returns both coefficients, with the intercept last:
=LINEST(B2:B6, A2:A6, TRUE, TRUE)
=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),1,1)→ \(\hat{\beta}=1.09\)=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),1,2)→ \(\hat{\alpha}=-4.93\)
Exercise 1, question 2
SST is \(69.2\), SSR is \(64.82\) and SSE is \(4.38\) (note that \(SSR+SSE=SST\)). The \(R^2\) is \(\frac{SSR}{SST}=0.94\) and the Standard Error estimate is \(1.21\). Both indicate a great fit of the regression line to the data.
All four numbers come out of the same LINEST block, so spill it once with =LINEST(B2:B6, A2:A6, TRUE, TRUE) and read off:
=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),5,1)→ \(SSR = 64.82\)=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),5,2)→ \(SSE = 4.38\)=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),3,1)→ \(R^2 = 0.94\)=INDEX(LINEST(B2:B6,A2:A6,TRUE,TRUE),3,2)→ \(s_e = 1.21\)
Then \(SST = SSR + SSE = 69.2\), which =DEVSQ(B2:B6) confirms.
Exercise 1, question 3
If \(x=32\) then \(\hat{y}=-4.93+1.09(32)=29.87\). The regression is a good fit, so we can feel good about our prediction. However, we would be concerned about the small sample size of the data.
In Excel:
=TREND(B2:B6, A2:A6, 32)→ \(29.87\)
Exercise 2, question 1
An extra year of education increases the annual salary by about \(\$5,300\) (the slope). A person with no education would be expected to earn about \(\$17,258\) (the intercept).
In Excel, assuming Salary is in column B and Education is in column C:
=LINEST(B2:B101, C2:C101, TRUE, TRUE)
=INDEX(LINEST(B2:B101,C2:C101,TRUE,TRUE),1,1)→ \(5.30\)=INDEX(LINEST(B2:B101,C2:C101,TRUE,TRUE),1,2)→ \(17.26\)
Exercise 2, question 2
The \(R^2\) is \(0.67\) and the standard error is about \(21\). The line is a moderately good fit. A person with \(16\) years of education is predicted to earn about \(\$102,000\).
In Excel:
=INDEX(LINEST(B2:B101,C2:C101,TRUE,TRUE),3,1)→ \(R^2 = 0.67\)=INDEX(LINEST(B2:B101,C2:C101,TRUE,TRUE),3,2)→ \(s_e = 21\)=TREND(B2:B101, C2:C101, 16)→ \(102.08\)
Exercise 3, question 1
Approximately \(36\%\) of the sample owns a home (mean \(=0.3625\)).
In Excel, assuming OwnHome is in column A and Food is in column B, first delete any rows with missing values. Then add a helper column C with the dummy variable:
=IF(A2="Yes", 1, 0)
Copy it down for all rows and take the average:
=AVERAGE(C2:C81)→ \(0.3625\)
You can also skip the helper column entirely; the same proportion is =AVERAGE(IF(A2:A81="Yes",1,0)).
Exercise 3, question 2
The intercept (\(\approx 6473\)) is the average food expenditure of individuals without homes. The slope (\(\approx -3418\)) is the difference in food expenditure between individuals who own a home and those who do not: home owners spend about \(\$3,418\) less on food than non-owners.
In Excel, with Food in column B and the dummy in column C:
=LINEST(B2:B81, C2:C81, TRUE, TRUE)
=INDEX(LINEST(B2:B81,C2:C81,TRUE,TRUE),1,2)→ intercept \(= 6473\)=INDEX(LINEST(B2:B81,C2:C81,TRUE,TRUE),1,1)→ slope \(= -3418\)
Because a single dummy just splits the sample in two, the two predictions are the two group means:
=TREND(B2:B81, C2:C81, 0)→ \(6473\), the same as=AVERAGEIF(A2:A81,"No",B2:B81)=TREND(B2:B81, C2:C81, 1)→ \(3055\), the same as=AVERAGEIF(A2:A81,"Yes",B2:B81)
Exercise 3, question 3
The regression line is \(\widehat{dummy}=1.432-0.000204\,Food\). This is a linear probability model: it predicts the likelihood of owning a home from food expenditure. The slope tells us how that likelihood changes as food expenditure increases by one dollar. In general, the likelihood of owning a home decreases as food expenditure increases. The scatter plot shows that most home owners have food expenditures below \(\$6,000\), while non-owners are mostly above it.
In Excel, with the dummy in column C and Food in column B:
=INDEX(LINEST(C2:C81,B2:B81,TRUE,TRUE),1,2)→ intercept \(= 1.432\)=INDEX(LINEST(C2:C81,B2:B81,TRUE,TRUE),1,1)→ slope \(= -0.000204\)
For the scatter plot, select Food and the dummy variable, go to Insert → Charts → Scatter, and add a linear trend line by right-clicking a point and choosing Add Trendline → Linear.
Exercise 4, question 1
With an \(R^2=0.81\), the model fits the data very well.
In Excel, first isolate Japan: select the data, go to Data → Filter, and filter Country.Name to Japan. Copy the \(62\) Japan rows to a new sheet. Assuming Year is in column C and Population is in column D:
=INDEX(LINEST(D2:D63,C2:C63,TRUE,TRUE),3,1)→ \(R^2 = 0.81\)
Exercise 4, question 2
The prediction for \(2030\) is about \(140\) million people (\(140{,}268{,}585\)).
In Excel:
=TREND(D2:D63, C2:C63, 2030)→ \(140{,}268{,}585\)
Exercise 4, question 3
After looking at the scatter plot, it seems unlikely that the population in Japan will hit \(140\) million. In the most recent years the population has been decreasing, so the straight-line trend overstates the future population, so the prediction should be treated with caution.
In Excel, select the Year and Population columns for Japan, go to Insert → Charts → Scatter, and add a linear trend line by right-clicking a point and choosing Add Trendline → Linear.
8 Probability I
Exercise 1, question 1
- \(P(D)=1/5=0.2\) since all events are equally likely.
- \(P(B^c)=4/5=0.8\)
- \(P(A \cup C \cup E)=P(A + C + E)=3/5=0.6\).
Exercise 1, question 2
- Events \(A\) and \(B\) are not mutually exclusive since they share some of the same elements.
- The events are not exhaustive since the union of both doesn’t create the sample space.
Exercise 1, question 3
- The probability is \(68.8\)%.
- The events are mutually exclusive. If someone is classified as obese, the person is not classified again as overweight.
- The events are not exhaustive since there are people in the U.S. that have a normal weight.
- The probability that the person drawn has normal weight is \(31.2\)%.
Exercise 2, question 1
From the multiplication rule, \[P(A|B)*P(B)=P(A \cap B)\]
substituting values yields: \[P(A \cap B)=0.45*0.3=0.135\] From the addition rule, \[P(A \cup B)=P(A)+P(B)-P(A \cap B)\] substituting yields: \[P(A \cup B)=0.65+0.3-0.135=0.815\] From the multiplication rule once again, \[P(B|A)=\frac{P(A \cap B)}{P(A)}\] substituting values yields, \[P(B|A)=0.135/0.65=0.2076923\]
Exercise 2, question 2
From the complement rule we have that \(P(A^c)=0.6\) and \(P(B^c)=0.5\).
Using the multiplication rule, \[P(A^c|B^c)=\frac{P(A^c \cap B^c)}{P(B^c)}\] substituting yields: \[P(A^c|B^c)=0.24/0.5=0.48\] From the addition rule, \[P(A^c \cup B^c)=P(A^c)+P(B^c)-P(A^c \cap B^c)\] substituting yields: \[P(A^c \cup B^c)=0.6+0.5-0.24=0.86\] The event that has no elements of \(A\) or \(B\) is given by \(P(A^c \cap B^c)\). Therefore \(P(A \cup B)=1-0.24=0.76\) has all the elements of A and B.
Exercise 2, question 3
In short, the problem states \(P(A)=0.4\), \(P(B)=0.6\), and \(P(A|B)=0.5\). Where \(A\) and \(B\) are events of stocks rising in price. The question asks for: \[P(A \cup B)=P(A)+P(B)-P(A \cap B)\] Using the multiplication rule, \[P(A \cap B)=0.5*0.6=0.3\] Hence, \[P(A \cup B)=0.4+0.6-0.3=0.7\] The events are not mutually exclusive since \(P(A \cap B)=0.3 \neq 0\). The events are also not independent since \(P(A|B)=0.5 \neq 0.4=P(A)\).
Exercise 3, question 1
Rearranging the addition rule, \[P(A \cap B)=P(A)+P(B)-P(A \cup B)=0.5+0.4-0.7=0.2\] Using the conditional probability formula, \[P(A|B)=\frac{P(A \cap B)}{P(B)}=\frac{0.2}{0.4}=0.5 \qquad P(B|A)=\frac{P(A \cap B)}{P(A)}=\frac{0.2}{0.5}=0.4\] The events are not mutually exclusive since \(P(A \cap B)=0.2 \neq 0\). They are independent since \(P(A|B)=0.5=P(A)\) (equivalently, \(P(A)P(B)=0.5 \times 0.4=0.2=P(A \cap B)\)).
Exercise 3, question 2
Since the events are independent, \[P(A \cap B)=P(A)P(B)=0.3 \times 0.6=0.18\] From the addition rule, \[P(A \cup B)=0.3+0.6-0.18=0.72\] Independence also means that knowing \(B\) happened does not change the probability of \(A\), so \(P(A|B)=P(A)=0.3\). Finally, by De Morgan’s law, \(A^c \cap B^c=(A \cup B)^c\), so \[P(A^c \cap B^c)=1-P(A \cup B)=1-0.72=0.28\] As a check, the complements of independent events are also independent: \(P(A^c)P(B^c)=0.7 \times 0.4=0.28\).
Exercise 3, question 3
Mutually exclusive events cannot happen together, so \(P(A \cap B)=0\). The addition rule then simplifies to \[P(A \cup B)=P(A)+P(B)=0.25+0.45=0.70\] Since \(A\) and \(B\) never occur together, \(P(A|B)=0/0.45=0\). The probability that neither occurs is the complement of the union: \(1-0.70=0.30\).
The events are not independent. If \(B\) happens, the probability of \(A\) drops from \(0.25\) to \(0\), so knowing \(B\) changes the probability of \(A\). Mutually exclusive events (with positive probabilities) are always dependent.
Exercise 3, question 4
In short, \(P(M)=0.60\), \(P(P)=0.05\), and \(P(M \cap P)=0.02\). From the addition rule, \[P(M \cup P)=0.60+0.05-0.02=0.63\] The probability that a mobile visitor buys is \[P(P|M)=\frac{P(M \cap P)}{P(M)}=\frac{0.02}{0.60}=0.033\] Visitors who do not use a mobile phone make up \(P(M^c)=0.40\) of the traffic, and they account for the remaining \(0.05-0.02=0.03\) of purchases, so \[P(P|M^c)=\frac{0.03}{0.40}=0.075\] Purchasing is not independent of the device: \(P(P|M)=0.033 \neq 0.05=P(P)\). Desktop visitors are more than twice as likely to buy as mobile visitors.
Exercise 3, question 5
Since the failures are independent, \[P(\text{both fail})=0.02 \times 0.02=0.0004\] “At least one is working” is the complement of “both fail”: \[P(\text{at least one works})=1-0.0004=0.9996\] “Exactly one fails” can happen in two mutually exclusive ways: the first fails and the second works, or the first works and the second fails. Adding them, \[P(\text{exactly one fails})=(0.02)(0.98)+(0.98)(0.02)=0.0392\]
Exercise 4, question 1
Below is the joint probability table. The \(P(A)=0.26+0.34=0.6\), \(P(A \cap B)=0.26\), \(P(A|B)=0.26/0.4=0.65\), and \(P(B|A^c)=0.14/0.4=0.35\). Events \(A\) and \(B\) are not independent since \(P(A) \neq P(A|B)\). The events are not mutually exclusive since \(P(A \cap B)=0.26 \neq 0\).
| \(B\) | \(B^c\) | Total | |
| \(A\) | 0.26 | 0.34 | 0.6 |
| \(A^c\) | 0.14 | 0.26 | 0.4 |
| Total | 0.4 | 0.6 | 1 |
Exercise 5, question 1
The contingency table below shows the counts for each combination of Crash Type and Light Condition:
| Daylight | Not Daylight | Total | |
|---|---|---|---|
| Head-on | 166 | 108 | 274 |
| Not Head-On | 3258 | 1326 | 4584 |
| Total | 3424 | 1434 | 4858 |
In Excel, build a PivotTable: drag Crash Type to the Rows area, Light Condition to the Columns area, and either field to the Values area as a Count. To turn the counts into a joint probability table, use Value Field Settings → Show Values As → % of Grand Total. You can also count any single cell directly. Assuming Crash Type is in column B and Light Condition in column C:
=COUNTIFS(B:B, "Head-on", C:C, "Daylight")→ \(166\)
Exercise 5, question 2
a) The probability of a Head-On crash is:
\[P(\text{Head-on})=\frac{166+108}{4858}=\frac{274}{4858}=0.056\]
b) The probability that the crash is in daylight is:
\[P(\text{Daylight})=\frac{166+3258}{4858}=\frac{3424}{4858}=0.70\]
c) The probability that a crash is Head-On given daylight is:
\[P(\text{Head-on}\mid\text{Daylight})=\frac{P(\text{Head-on}\cap\text{Daylight})}{P(\text{Daylight})}=\frac{166}{3424}=0.048\]
In Excel these follow from the PivotTable counts. For example, part a) is =COUNTIF(B:B,"Head-on") / COUNTA(B2:B4859).
Exercise 5, question 3
The two variables are dependent since \(P(\text{Head-on}\mid\text{Daylight}) \neq P(\text{Head-on})\), that is \(0.048 \neq 0.056\).
Exercise 6, question 1
\(P(A^c)=1-P(A)=1-0.7=0.3\), \(P(A \cap B)=𝑃(𝐵|𝐴)𝑃(𝐴) = 0.55(0.70) = 0.385\), \(P(A^c \cap B)=𝑃(B|A^c)𝑃(A^c) = 0.10(0.30) = 0.03\), \(P(B)= 𝑃(A \cap B) + 𝑃(𝐴^c \cap 𝐵) = 0.385 + 0.03 = 0.415\), and \(P(A|B)= \frac{𝑃(A \cap B)}{P(B)}=0.385/0.415=0.9277\).
Exercise 6, question 2
Let the event of failing be \(F\), the event of not failing be \(NF\), the event of having a tutor be \(T\), and the event of not having a tutor be \(NT\). The probability of failing the course is \(0.25\). \((𝐹) = 𝑃(𝐹 \cap 𝑇) + 𝑃(𝐹 \cap 𝑇^c) = 𝑃(𝐹|𝑇)𝑃(𝑇) + 𝑃(𝐹|𝑇^c)𝑃(𝑇^c) = 0.10(0.50) + 0.40(0.50) = 0.05 + 0.20 = 0.25\) The probability of not having a tutor, given that she failed the course is \(0.2\). \(P(𝑇|𝐹) = \frac{𝑃(𝐹\cap𝑇)}{𝑃(𝐹\cap𝑇)+𝑃(𝐹 \cap𝑇^c)}= 0.05/0.25 = 0.20\).
Exercise 7, question 1
\(3!=3 \times 2 \times 1=6\), \(4!=4 \times 3 \times 2 \times 1=24\), \(C_6^8=28\), and \(P_6^8=20{,}160\).
In Excel:
=FACT(3)→ \(6\)=FACT(4)→ \(24\)=COMBIN(8, 6)→ \(28\)=PERMUT(8, 6)→ \(20{,}160\)
Exercise 7, question 2
If order doesn’t matter, there are \(252\) ways. If order matters, then there are \(30{,}240\) ways.
In Excel:
=COMBIN(10, 5)→ \(252\)=PERMUT(10, 5)→ \(30{,}240\)
Exercise 7, question 3
The order in which the two people are picked does not matter, so we count combinations. The number of possible pairs is \[C_2^6=\frac{6!}{2!\,4!}=15\] Every pair is equally likely, so we can use the classical method. The number of pairs made up of two analysts is \(C_2^4=6\), so \[P(\text{both analysts})=\frac{6}{15}=0.4\] “At least one intern” is the complement of “both analysts”: \[P(\text{at least one intern})=1-0.4=0.6\] We can check this by counting directly: there are \(C_1^4 \times C_1^2=8\) pairs with one analyst and one intern, and \(C_2^2=1\) pair with both interns, so \((8+1)/15=0.6\).
In Excel:
=COMBIN(6, 2)→ \(15\)=COMBIN(4, 2)/COMBIN(6, 2)→ \(0.4\)=1 - COMBIN(4, 2)/COMBIN(6, 2)→ \(0.6\)
9 Probability II
Exercise 1, question 1
The expected value is \(10.75\) and the standard deviation is \(5.31\). The probability of \(x<15\) is \(0.65\).
In Excel, enter the values x in column A and the probabilities P(X=x) in column B:
=SUMPRODUCT(A2:A5, B2:B5)→ \(10.75\) (expected value)=SQRT(SUMPRODUCT((A2:A5-10.75)^2, B2:B5))→ \(5.31\) (standard deviation)- \(P(x<15) = 0.35 + 0.3 = 0.65\)
Exercise 1, question 2
The expected value is \(-17.4\) and the standard deviation is \(6.86\). The probability of \(x \geq -9\) is \(0.25\).
In Excel, with y in column A and P(Y=y) in column B:
=SUMPRODUCT(A2:A5, B2:B5)→ \(-17.4\)=SQRT(SUMPRODUCT((A2:A5-(-17.4))^2, B2:B5))→ \(6.86\)- \(P(y \geq -9) = 0.15 + 0.1 = 0.25\)
Exercise 1, question 3
Both funds have the same expected return of \(6\). The safest return comes from fund 1 since the standard deviation is only \(11.14\) vs. \(31.05\) for fund 2.
In Excel, enter the probabilities in column A, Fund 1 in column B, and Fund 2 in column C:
=SUMPRODUCT(A2:A4, B2:B4)→ \(6\) (Fund 1 expected return)=SUMPRODUCT(A2:A4, C2:C4)→ \(6\) (Fund 2 expected return)=SQRT(SUMPRODUCT((B2:B4-6)^2, A2:A4))→ \(11.14\) (Fund 1 std. dev.)=SQRT(SUMPRODUCT((C2:C4-6)^2, A2:A4))→ \(31.05\) (Fund 2 std. dev.)
Since both funds have the same expected return, choose Fund 1: it achieves that return with much less risk.
Exercise 2, question 1
The expected return of the portfolio is \(10.4\) and the standard deviation is \(14.60\).
The portfolio weights are \(\alpha=2/5=0.4\) for X and \(\beta=3/5=0.6\) for Y. In Excel:
- Expected return \(\alpha R_1 + \beta R_2\):
=0.4*8 + 0.6*12→ \(10.4\) - Standard deviation \(\sqrt{\alpha^2 \sigma_1^2+\beta^2 \sigma_2^2+2\alpha\beta\rho\sigma_1\sigma_2}\):
=SQRT(0.4^2*12^2 + 0.6^2*20^2 + 2*0.4*0.6*0.4*12*20)→ \(14.60\)
Exercise 3, question 1
\(P(Z=1)=0.31\), and \(P(Z \geq 2)=0.57\). The expected value is \(np=1.75\) and the standard deviation is \(\sqrt{np(1-p)}=1.067\).
In Excel:
=BINOM.DIST(1, 5, 0.35, FALSE)→ \(0.31\)=1 - BINOM.DIST(1, 5, 0.35, TRUE)→ \(0.57\)
Exercise 3, question 2
\(P(W>160)=0.14\), and \(P(155 \leq W \leq 165 )=0.45\). The expected value is \(np=154\) and the standard deviation is \(\sqrt{np(1-p)}=5.95\).
In Excel:
=1 - BINOM.DIST(160, 200, 0.77, TRUE)→ \(0.14\)=BINOM.DIST(165, 200, 0.77, TRUE) - BINOM.DIST(154, 200, 0.77, TRUE)→ \(0.45\)
Exercise 3, question 3
The probabilities are the same. Each event has a probability of \(0.3456\). If the probability changes to \(0.5\) now the event of two women and two men is more likely.
In Excel:
=BINOM.DIST(3, 4, 0.6, FALSE)→ \(0.3456\) (three men)=BINOM.DIST(2, 4, 0.6, FALSE)→ \(0.3456\) (two men)=BINOM.DIST(3, 4, 0.5, FALSE)→ \(0.25\)=BINOM.DIST(2, 4, 0.5, FALSE)→ \(0.375\)
At \(p=0.5\), having two of each is the most likely outcome.
Exercise 4, question 1
The \(P(S=2)=0.25\) and \(P(S \geq 2)=0.44\). The expected value and the variance is \(1.5\).
In Excel:
=POISSON.DIST(2, 1.5, FALSE)→ \(0.25\)=1 - POISSON.DIST(1, 1.5, TRUE)→ \(0.44\)
Exercise 4, question 2
The \(P(T=14)=0.039\) and \(P(18 \leq T \leq 23)=0.49\).
In Excel:
=POISSON.DIST(14, 20, FALSE)→ \(0.039\)=POISSON.DIST(23, 20, TRUE) - POISSON.DIST(17, 20, TRUE)→ \(0.49\)
Exercise 4, question 3
The probability of administering more than \(8\) but less than \(12\) shots is \(0.3\).
If \(84\) shots are administered on average weekly, then \(12\) are administered daily. Using this daily mean in Excel:
=POISSON.DIST(11, 12, TRUE) - POISSON.DIST(8, 12, TRUE)→ \(0.30\)
Exercise 5, question 1
\(P(X=0)=0.58\), \(P(X=1)=0.37\), and \(P(X \leq 1)=0.94\).
In Excel (successes in population \(r=3\), population \(N=25\), sample \(n=4\)):
=HYPGEOM.DIST(0, 4, 3, 25, FALSE)→ \(0.58\)=HYPGEOM.DIST(1, 4, 3, 25, FALSE)→ \(0.37\)=HYPGEOM.DIST(1, 4, 3, 25, TRUE)→ \(0.94\)
Exercise 5, question 2
The probability is \(0.078\).
In Excel (successes in population \(r=25\), population \(N=100\), sample \(n=20\)):
=1 - HYPGEOM.DIST(7, 20, 25, 100, TRUE)→ \(0.078\)
Exercise 5, question 3
The probability of matching two white balls is \(5\%\). Winning the jackpot is extremely unlikely! A probability of \(0.00000000512\). It is more likely to be struck by lightning according to the CDC.
In Excel, treat the five drawn white balls as the successes in a population of \(59\), with the player’s five picks as the sample:
=HYPGEOM.DIST(2, 5, 5, 59, FALSE)→ \(0.05\) (match two white balls)
For the jackpot, multiply the probability of matching all five white balls by the probability of matching the single Powerball (1 of 39):
=HYPGEOM.DIST(5, 5, 5, 59, FALSE) * HYPGEOM.DIST(1, 1, 1, 39, FALSE)→ \(0.00000000512\)
10 Probability III
Exercise 1, question 1
The height of the density function \(f(x)=0.1667\), the mean is \(1\), standard deviation is \(1.73\), and \(P(X \leq -1)=0.1667\).
The uniform distribution has no Excel function, so each quantity is computed directly from its formula:
- Height \(f(x)=\frac{1}{b-a}\):
=1/(4-(-2))→ \(0.1667\) - Mean \(\mu=\frac{a+b}{2}\):
=(-2+4)/2→ \(1\) - Standard deviation \(\sigma=\sqrt{\frac{(b-a)^2}{12}}\):
=SQRT((4-(-2))^2/12)→ \(1.73\) - \(P(X \leq -1)=\frac{x-a}{b-a}\):
=(-1-(-2))/(4-(-2))→ \(0.1667\)
Exercise 1, question 2
The probability that you will arrive on time is \(0.875\). If the time of the errand is 30 minutes, then the probability goes down to \(0.75\).
There is a \(120\) minute interval in which the provider can arrive, so \(P(X>t)=1-\frac{t}{120}\). In Excel:
=1 - 15/120→ \(0.875\)=1 - 30/120→ \(0.75\)
Exercise 2, question 1
\(P(-0.67 \leq Z \leq -0.23)=0.158\), \(P(0 \leq Z \leq 1.96)=0.475\), \(P(-1.28 \leq Z \leq 0)=0.4\) and \(P(Z > 4.2) \approx 0\).
Use the =NORM.S.DIST() function for the standard normal distribution:
=NORM.S.DIST(-0.23, TRUE) - NORM.S.DIST(-0.67, TRUE)→ \(0.158\)=NORM.S.DIST(1.96, TRUE) - NORM.S.DIST(0, TRUE)→ \(0.475\)=NORM.S.DIST(0, TRUE) - NORM.S.DIST(-1.28, TRUE)→ \(0.4\)=1 - NORM.S.DIST(4.2, TRUE)→ \(\approx 0\)
Exercise 2, question 2
\(P(Y>7.6)=0.005386\), \(P(7.4 \leq Y \leq 10.6)=0.0071\), a \(y\) such that \(P(Y>y)=0.025\) is \(6.42\), and a \(y\) such that \(P(y \leq Y \leq 2.5)\) is \(-2.56\).
Use =NORM.DIST() for probabilities and =NORM.INV() for quantiles:
=1 - NORM.DIST(7.6, 2.5, 2, TRUE)→ \(0.005386\)=NORM.DIST(10.6, 2.5, 2, TRUE) - NORM.DIST(7.4, 2.5, 2, TRUE)→ \(0.0071\)- \(P(Y>y)=0.025\) means \(y\) is the \(97.5\)th percentile:
=NORM.INV(0.975, 2.5, 2)→ \(6.42\) - Since \(2.5\) is the mean, \(P(y \leq Y \leq 2.5)=0.4943\) leaves \(0.5-0.4943=0.0057\) in the left tail:
=NORM.INV(0.0057, 2.5, 2)→ \(-2.56\)
Exercise 2, question 3
The probability is \(10.56\)%. A game lasting no more than \(2.069\) hours would be in the bottom \(1\)%.
In Excel:
=NORM.DIST(2.5, 3, 0.4, TRUE)→ \(0.1056\)=NORM.INV(0.01, 3, 0.4)→ \(2.069\)
Exercise 3, question 1
The standard deviation is equal to the mean \(0.1\). \(P(0.10 \leq S \leq 0.2)=0.2325\)
The rate is \(\lambda = 1/\mu = 1/0.1 = 10\). In Excel:
=EXPON.DIST(0.2, 10, TRUE) - EXPON.DIST(0.1, 10, TRUE)→ \(0.2325\)
Exercise 3, question 2
The mean time between car arrivals is \(1/360=0.002778\) hours. The probability that the next car will arrive within the next 10 seconds is \(0.6321\).
Ten seconds is \(10/3600 = 1/360\) of an hour. Using a rate of \(\lambda = 360\) cars per hour in Excel:
=EXPON.DIST(1/360, 360, TRUE)→ \(0.6321\)
11 Inference I
Exercise 1, question 1
In a modern version of Excel, type the following in the top-left cell of an empty block; it will spill into a \(10 \times 1000\) grid where each column is a sample of size \(10\):
=RANDARRAY(10, 1000, 100, 200, FALSE)
In an older version, enter =100 + 100*RAND() in the top-left cell and copy it across \(1000\) columns and down \(10\) rows.
Because the values are drawn from a uniform distribution on \([100, 200]\), the population mean is \(\mu = \frac{100+200}{2} = 150\) and the population standard deviation is \(\sigma = \frac{200-100}{\sqrt{12}} \approx 28.87\). (The values recalculate on every edit; to freeze a sample, copy the block and use Paste Special → Values.)
Exercise 1, question 2
Below the block, compute the mean of each column and copy it across all \(1000\) columns:
=AVERAGE(A1:A10)→ the mean of the first sample
This produces \(1000\) sample means. Summarize them with:
=AVERAGE(...)→ about \(150\), matching the population mean (unbiasedness)=STDEV.S(...)→ about \(9.1\)
The standard deviation of the sample means is far smaller than the population standard deviation (\(\approx 28.87\)), and it matches the standard error \(\sigma / \sqrt{n} = 28.87 / \sqrt{10} \approx 9.13\).
Exercise 1, question 3
Select the \(1000\) sample means and go to Insert → Charts → Histogram (or use the bins-and-PivotTable method from Descriptive Statistics II). Although the population is uniform, the distribution of the sample means is bell-shaped. This is the Central Limit Theorem in action.
Using a normal distribution with mean \(150\) and standard error \(9.13\):
=NORM.DIST(160, 150, 9.13, TRUE) - NORM.DIST(140, 150, 9.13, TRUE)→ about \(0.73\)
There is roughly a \(73\%\) probability that the sample mean falls between \(140\) and \(160\). Because the numbers are random, your exact value will vary slightly each time the sheet recalculates.
Exercise 2, question 1
The expected value is \(80\) (equal to the population mean). The standard error is \(1.4\), and the probability is \(98.38\%\).
In Excel:
=14/SQRT(100)→ \(1.4\) (standard error)=NORM.DIST(85, 80, 1.4, TRUE) - NORM.DIST(77, 80, 1.4, TRUE)→ \(0.9838\)
Exercise 2, question 2
The probabilities are \(24.66\%\) and \(1.8\%\).
For the mean of four cars, use the standard error \(3.5/\sqrt{4}\):
=1 - NORM.DIST(35, 33.8, 3.5/SQRT(4), TRUE)→ \(0.2466\)
For all four cars, first find the probability that one car exceeds \(35\), then raise it to the fourth power (the draws are independent):
=(1 - NORM.DIST(35, 33.8, 3.5, TRUE))^4→ \(0.018\)
Exercise 3, question 1
The expected value is \(0.75\) (the same as the population). The standard error is \(\sqrt{p(1-p)/n} = 0.03\), and the probability is \(0.8975\).
In Excel:
=SQRT(0.75*0.25/200)→ \(0.0306\)=NORM.DIST(0.8, 0.75, SQRT(0.75*0.25/200), TRUE) - NORM.DIST(0.7, 0.75, SQRT(0.75*0.25/200), TRUE)→ \(0.8975\)
Exercise 3, question 2
The probability with a sample of \(50\) is \(69.29\%\). With a sample of \(200\) it rises to \(84.33\%\). As the sample size increases, the standard error shrinks, so the sampling distribution of \(\hat{p}\) becomes tighter around \(0.23\) and more of its area lies above \(0.20\).
In Excel:
=1 - NORM.DIST(0.2, 0.23, SQRT(0.23*0.77/50), TRUE)→ \(0.6929\)=1 - NORM.DIST(0.2, 0.23, SQRT(0.23*0.77/200), TRUE)→ \(0.8433\)
Exercise 4, question 1
The process appears to be out of control. In the early samples the machine is not filling the cans with enough drink; it recovers in later periods, but the early behavior is a warning sign.
Build a control chart. The center line is \(350\), and the limits are three standard errors away, where the standard error is \(10/\sqrt{10}\):
=350 + 3*(10/SQRT(10))→ \(359.49\) (upper limit)=350 - 3*(10/SQRT(10))→ \(340.51\) (lower limit)
Plot the eight sample means together with the two limits: put the sample numbers, the sample means, and two columns holding the constant limits side by side, then insert a Line or Scatter chart. Several early samples fall below the lower limit of \(340.51\), confirming the process is out of control.
Exercise 4, question 2
The process looks in control. All five proportions fall within three standard errors of the mean.
The center line is \(0.01\), and the limits use the standard error \(\sqrt{0.01 \times 0.99 / 1000}\):
=0.01 + 3*SQRT(0.01*0.99/1000)→ \(0.0194\) (upper limit)=0.01 - 3*SQRT(0.01*0.99/1000)→ \(0.0006\) (lower limit)
Plotting the five proportions against these limits shows every point inside the band, so the process is under control.
12 Inference II
Exercise 1, question 1
For an exponential distribution with rate \(\lambda = 0.02\), the mean and standard deviation are both \(1/\lambda = 50\). To draw an exponential value in Excel, transform a uniform random number:
=-LN(RAND())/0.02
Fill a block of \(10{,}000\) rows and \(50\) columns with this formula; each row is a sample of size \(50\). (The values recalculate on every edit, so copy the block and use Paste Special → Values to freeze them.)
Exercise 1, question 2
In the column just after the data, average each row and copy the formula down all \(10{,}000\) rows:
=AVERAGE(A2:AX2)→ the mean of the first sample
Summarizing the \(10{,}000\) sample means:
=AVERAGE(...)→ about \(50\), matching the population mean=STDEV.S(...)→ about \(7.07\), which matches the standard error \(\sigma/\sqrt{n} = 50/\sqrt{50} \approx 7.07\)
Exercise 1, question 3
With the standard error stored in a cell (call it SE \(\approx 7.07\)), the limits are the sample mean plus or minus \(z_{0.05} \times SE\):
=FirstMean - NORM.S.INV(0.95)*SE→ lower limit=FirstMean + NORM.S.INV(0.95)*SE→ upper limit
Because a typical sample mean sits near \(50\), the interval will usually contain the population mean. About \(90\%\) of such intervals do.
Exercise 1, question 4
Rather than build \(10{,}000\) pairs of limits, note that an interval includes \(50\) exactly when the sample mean is within \(z_{0.05} \times SE\) of \(50\). Add a helper column:
=IF(ABS(SampleMean - 50) <= NORM.S.INV(0.95)*SE, 1, 0)
Copy it down and sum it with =SUM(...). About \(9{,}000\) of the \(10{,}000\) intervals contain the population mean, which is exactly what a \(90\%\) confidence level means.
Exercise 2, question 1
The 90% interval is \([94.52,\ 114.68]\) and the 95% interval is \([92.44,\ 116.76]\). The higher the confidence level, the wider the interval.
Since \(\sigma\) is unknown, use the \(t\)-distribution. CONFIDENCE.T returns the margin of error directly:
=CONFIDENCE.T(0.10, 28.8, 24)→ \(10.08\), so the 90% interval is \(104.6 \pm 10.08\)=CONFIDENCE.T(0.05, 28.8, 24)→ \(12.16\), so the 95% interval is \(104.6 \pm 12.16\)
Exercise 2, question 2
The interval for \(n = 16\) is \([30.75,\ 66.61]\) and for \(n = 25\) is \([34.79,\ 62.57]\). As the sample size grows, the interval gets narrower and more precise.
In Excel:
=CONFIDENCE.T(0.05, 33.64, 16)→ \(17.93\), so the interval is \(48.68 \pm 17.93\)=CONFIDENCE.T(0.05, 33.64, 25)→ \(13.89\), so the interval is \(48.68 \pm 13.89\)
Exercise 3
The 95% interval for Group 1 is \([-0.53,\ 2.03]\) and for Group 2 is \([0.90,\ 3.76]\).
The data is sorted by group, so with extra in column A the ten Group 1 values are in A2:A11 and the ten Group 2 values in A12:A21. For each group, find the mean and the margin of error:
=AVERAGE(A2:A11)→ \(0.75\) for Group 1, and=AVERAGE(A12:A21)→ \(2.33\) for Group 2=CONFIDENCE.T(0.05, STDEV.S(A2:A11), 10)→ the margin of error, givingAVERAGE ± margin
(You can also get each group’s mean with =AVERAGEIF(B:B, "1", A:A).) Drug 2 appears more effective. Its interval lies entirely above zero, so it is unlikely to have no effect, and its mean increase in sleep is \(2.33\) hours, well above Drug 1’s \(0.75\) hours.
Exercise 4, question 1
The 90% interval is \([0.319,\ 0.481]\) and the 95% interval is \([0.304,\ 0.496]\). Neither includes \(0.5\), so at both levels we can conclude the population proportion differs from \(0.5\).
With \(\hat{p} = 0.4\) and \(n = 100\), the standard error is =SQRT(0.4*0.6/100) \(= 0.049\). The margins of error are:
=NORM.S.INV(0.95) * SQRT(0.4*0.6/100)→ \(0.081\), giving \(0.4 \pm 0.081\)=NORM.S.INV(0.975) * SQRT(0.4*0.6/100)→ \(0.096\), giving \(0.4 \pm 0.096\)
Exercise 4, question 2
The 95% confidence interval is \([0.128,\ 0.186]\).
The data is in tidy form with columns Hair, Eye, Sex, and n (the count). Total the Hazel rows and divide by the overall total to get the sample proportion:
=SUMIF(Eye_range, "Hazel", n_range)→ \(93\) Hazel-eyed students=SUM(n_range)→ \(592\) students, so \(\hat{p} = 93/592 = 0.157\)
Then the margin of error and interval are:
=NORM.S.INV(0.975) * SQRT(0.157*(1-0.157)/592)→ \(0.029\), giving \(0.157 \pm 0.029 = [0.128,\ 0.186]\)
13 Inference III
Exercise 1, question 1
The test statistic is \(-1.6\). The two-tailed p-value is \(13.04\%\), which is greater than \(5\%\), so we cannot reject the null. The population mean is not statistically different from \(50\).
In Excel:
=(46 - 50) / (10 / SQRT(16))→ \(-1.6\) (test statistic)=T.DIST.2T(ABS(-1.6), 15)→ \(0.1304\) (two-tailed p-value)
The critical value =T.INV.2T(0.05, 15) is \(2.131\). Since \(|-1.6| < 2.131\), we fail to reject the null.
Exercise 1, question 2
The null hypothesis cannot be rejected: the left-tailed p-value of \(1.9\%\) is greater than the \(1\%\) significance level.
Enter the data in a range (say A2:A7). In Excel:
=AVERAGE(A2:A7)→ \(92\) and=STDEV.S(A2:A7)→ \(6.96\)=(92 - 100) / (6.96 / SQRT(6))→ \(-2.82\) (test statistic)=T.DIST(-2.82, 5, TRUE)→ \(0.019\) (left-tailed p-value)
Exercise 1, question 3
The null hypothesis can be rejected: the right-tailed p-value of \(0.2\%\) is less than the \(10\%\) significance level.
Enter the data in a range (say A2:A11). In Excel:
=AVERAGE(A2:A11)→ \(256.4\) and=STDEV.S(A2:A11)→ \(38.28\)=(256.4 - 210) / (38.28 / SQRT(10))→ \(3.83\) (test statistic)=T.DIST.RT(3.83, 9)→ \(0.002\) (right-tailed p-value)
Exercise 2
The claim that the average waiting time is \(92\) minutes can be rejected at the \(10\%\), \(5\%\), and \(1\%\) significance levels.
With the waiting column in column B (272 observations), in Excel:
=AVERAGE(B2:B273)→ \(70.9\) and=STDEV.S(B2:B273)→ \(13.6\)=(70.9 - 92) / (13.6 / SQRT(272))→ \(-25.6\) (test statistic)=T.DIST.2T(ABS(-25.6), 271)→ approximately \(0\) (two-tailed p-value)
The sample mean is so far below \(92\) that the p-value is essentially zero, so we reject the null at any conventional level.
Exercise 3, question 1
The hypotheses are \(H_{0}: p = 0.4\) and \(H_{a}: p \neq 0.4\). The two-tailed p-value is \(0.102\), greater than \(0.05\), so we cannot reject the null. The population proportion is not significantly different from \(0.4\).
In Excel:
=(0.48 - 0.4) / SQRT(0.4*(1-0.4)/100)→ \(1.633\) (test statistic)=2*(1 - NORM.S.DIST(1.633, TRUE))→ \(0.102\) (two-tailed p-value)
Exercise 3, question 2
The sample proportion is \(128/320 = 0.4\). The left-tailed p-value of \(3.6\%\) is less than \(5\%\), so we reject the null and conclude the population proportion is less than \(0.45\).
In Excel:
=(0.4 - 0.45) / SQRT(0.45*(1-0.45)/320)→ \(-1.80\) (test statistic)=NORM.S.DIST(-1.80, TRUE)→ \(0.036\) (left-tailed p-value)
Exercise 3, question 3
The hypotheses are \(H_{0}: p \leq 0.5\) and \(H_{a}: p > 0.5\). The right-tailed p-value of \(0.26\) is greater than the \(1\%\) significance level, so we cannot reject the null. We therefore cannot conclude that more than \(50\%\) of the population is below \(10\).
Enter the data in a range (say A2:A11). In Excel:
=COUNTIF(A2:A11, "<10") / COUNT(A2:A11)→ \(0.6\) (sample proportion)=(0.6 - 0.5) / SQRT(0.5*(1-0.5)/10)→ \(0.632\) (test statistic)=1 - NORM.S.DIST(0.632, TRUE)→ \(0.264\) (right-tailed p-value)
Exercise 4
We reject the null hypothesis: the proportion of hazel-eyed students is significantly different from \(5\%\).
The data is tidy, with columns Hair, Eye, Sex, and n. In Excel:
=SUMIF(Eye_range, "Hazel", n_range)→ \(93\) and=SUM(n_range)→ \(592\), so \(\hat{p} = 93/592 = 0.157\)=(0.157 - 0.05) / SQRT(0.05*(1-0.05)/592)→ \(11.96\) (test statistic)=2*(1 - NORM.S.DIST(11.96, TRUE))→ approximately \(0\) (two-tailed p-value)
The sample proportion of \(15.7\%\) is nearly \(12\) standard errors above the claimed \(5\%\), so we reject the null decisively.
14 Regression and Inference
Exercise 1, question 1
We cannot reject the null: the p-value of \(0.47\) is greater than \(0.05\).
In Excel:
=(0.15*SQRT(25-2)) / SQRT(1 - 0.15^2)→ \(0.73\) (test statistic)=T.DIST.2T(0.73, 23)→ \(0.47\) (two-tailed p-value)
Exercise 1, question 2
The correlation is \(0.44\) and the test statistic is \(7.89\). The p-value is approximately \(0\), so we reject the null hypothesis: the correlation between Hits and Salary is highly significant.
In Excel (there are \(263\) players with a recorded salary):
=CORREL(Hits_range, Salary_range)→ \(0.44\)=(0.44*SQRT(263-2)) / SQRT(1 - 0.44^2)→ \(7.89\) (test statistic)=T.DIST.2T(7.89, 261)→ approximately \(0\)
Exercise 2, question 1
There is no strong reason to believe the variances differ, since players are recruited from a common pool. At the \(5\%\) level the difference in mean Hits is not significant, so we cannot reject the null.
Copy the Hits for NewLeague = "A" into one column and the Hits for NewLeague = "N" into another (or filter the data). This is an unpaired, two-tailed test assuming equal variances:
=T.TEST(A_range, N_range, 2, 2)→ \(0.28\) (p-value)
Since \(0.28 > 0.05\), we fail to reject the null.
Exercise 2, question 2
There may be reason to believe the variances differ, so we use an unequal-variance test. At the \(5\%\) level we cannot reject \(H_{0}: \mu_{male} - \mu_{female} \leq 0\): there is no evidence that males survive longer.
Run Data → Data Analysis → t-Test: Two-Sample Assuming Unequal Variances on the male and female survival times. The male mean (\(26.78\)) is actually below the female mean (\(28.10\)), giving a test statistic of \(-0.31\). Because the difference points opposite to the alternative, the one-tailed p-value is about \(0.62\), far above \(0.05\), so we fail to reject the null.
Exercise 3
The drug has an effect: we reject \(H_{0}: \bar{d} = 0\). The mean difference in extra sleep is statistically different from zero.
Because the same patients are measured twice, use a paired, two-tailed test. With the Group 1 values in A2:A11 and the Group 2 values in A12:A21:
=T.TEST(A2:A11, A12:A21, 2, 1)→ \(0.0028\) (p-value)
Since \(0.0028 < 0.01\), we reject the null at the \(1\%\) level.
Exercise 4, question 1
RBI and Years are statistically significant (p-values near \(0\)): salary rises with experience and with RBIs. Home runs are not significant (p-value about \(0.14\)). The \(F\)-statistic is highly significant, so the predictors are jointly significant. However, the \(R^2\) and adjusted \(R^2\) are only about \(0.32\), so the model explains just \(32\%\) of the variation in salary, so more variables would be needed for a better fit.
Copy HmRun, RBI, and Years into three adjacent columns (the ToolPak needs a contiguous X range), then run Data → Data Analysis → Regression with Salary as the Input Y Range and those three columns as the Input X Range. The output gives:
- each coefficient’s p-value (the individual tests): RBI and Years significant, HmRun not
- the Significance F (the joint test): approximately \(0\)
- R Square and Adjusted R Square: about \(0.33\) and \(0.32\)
Exercise 4, question 2
The predicted salary is \(619.93\) (thousand dollars), with a \(95\%\) prediction interval of \([-129.89,\ 1369.7]\).
Using the coefficients from the regression output (\(\hat{\alpha}=-90.09\), \(b_{HmRun}=-7.35\), \(b_{RBI}=9.16\), \(b_{Years}=32.82\)), the point prediction is:
=-90.09 + (-7.35)*28 + 9.16*57 + 32.82*12→ \(619.93\)
The prediction interval widens the estimate by \(t \times s_e\), where \(t =\) T.INV.2T(0.05, 259) \(\approx 1.97\) and \(s_e\) is the regression’s standard error (\(\approx 381\) for a new observation). This gives \(619.93 \pm 749.8 = [-129.89,\ 1369.7]\). The interval is very wide, and even includes negative salaries, because the model explains only about a third of the variation in salary.