10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
Probability, AUC, and Excel Linest Function
6/10 points earned (60%) You haven't passed yet. You need at least 70% to pass. Review the material and try again! You have 3 attempts every 8 hours.
Review Related Lesson
1/1
points
1. Keep the 125 outcomes in the Histogram Spreadsheet unchanged. Change the bin ranges so that bin 1 is [-3, -1), bin 2 is [-1,1) bin 3 is [1, 3).
Histograms Spreadsheet.xlsx What is the approximate probability that a new outcome will fall within bin 1? 5% 4% Correct Response
The approximate probability is (1+4)/125.
5 .4
0/1 points
https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
1/7
10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
points
2. Use the Excel Probability Functions Spreadsheet.
Excel_Probability_Functions.xlsx Assume a continuous uniform probability distribution over the range [47, 51.5]. What is the skewness of the probability distribution? 49.25 0 2.17 1.69 Incorrect Response
That is the variance.
0/1 points
3. Use the Excel Probability Functions Spreadsheet, provided in question #2. Assume a continuous uniform probability distribution over the range [-12, 20] What is the entropy of this distribution? 6 bits 4 bits Incorrect Response
That would be log2(16).
3 bits 5 bits https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
2/7
10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
1/1
points
4. Use the Excel Probability Functions Spreadsheet that was previously provided. Assume a Gaussian Probability function with mean = 3 and standard deviation =4. What is the value of f(x) at f(3.5)? 4.05 .352 .099 Correct Response
This is Excel normdist(3.5, 3,4, false)
.550
0/1
points
5. Use the Excel Probability Functions Spreadsheet previously provided in this quiz. Assume a Gaussian Probability Distribution with mean = 3 and standard deviation = 4. What is the cumulative distribution at x = 7? .960 Incorrect Response
Be sure and include the mean of 3.
.060 .841
https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
3/7
10/22/2016
.841
Probability, AUC, and Excel Linest Function | Coursera
1.00
1/1
points
6. Use the AUC Calculator Spreadsheet.
AUC_Calculator and Review of AUC Curve.xlsx If the “modi⤀褅cation factor” in the original example given in the AUC Calculator Spreadsheet is changed from -1 to -2, what is the change in the actual Area Under the ROC Curve? The area decreases No change Correct Response
The spreadsheet estimate changes slightly --- from .64 [.6377] to .64 [.6388] – this change is due to the version with -1 using more bins on the data, making it slightly more accurate.
The area increases
1/1
points
7. Use the AUC Calculator Spreadsheet provided in question #6. If the “modi⤀褅cation factor” in the original example given in the AUC Calculator Spreadsheet is changed from -1 to -2, what is the threshold (row 10) that results in the lowest cost per event? .9 .45 Correct Response
Changing the scale does not change the minimum cost per event,
https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
4/7
10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
Changing the scale does not change the minimum cost per event, which remains $975. However, it will change the value of the threshold above which all results are classi⤀褅ed positive, from .9 to .45.
1.3 3.5
0/1 points
8. Refer to the AUC Calculator Spreadsheet previously provided. Assume a binary classi⤀褅cation model is trained on 200 ordered pairs of scores and outcomes and has an AUC of .91 on this “training set.” The same model, on 5,000 new scores and outcomes, has an AUC of .5. Which statement is most likely to be correct? The original model identi⤀褅ed signal as noise and has no predictive value on new data. The original model is expected to perform worse on test set data and is functioning acceptably. The model over⤀褅t the training set data and will need to be improved to work better on the new data. Incorrect Response
The clue that something more serious than over-⤀褅tting is going on is that the model’s AUC of .5 on the much larger set of 5,000 is no better than assigning binary classi⤀褅cation purely at random. Rather than over⤀褅tting, it is more likely that the predictive model has no value at all.
1/1 points
9.
Refer to the Excel Linest Function Spreadsheet. https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
5/7
10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
Refer to the Excel Linest Function Spreadsheet.
Excel Linest Function.xlsx If a multivariate linear regression gives a weight beta(1) of 0.4 on x(1) = “age in years,” and a new input x(7) of “age in months” is added to the regression data, which of the following statements is false? If the x(1) data are removed, the new beta(7) on the new x(7) data will be 0.4. Correct Response
This statement is false. Because the x(1) and x(7) data are collinear, the model R^2 is unchanged, but because x(7) has 12 times the standard deviation of x(1), the correct coe⤀ꀈcient will be 1/12 as much, or .0333.
If the x(1) data are removed, the new beta(7) on the new x(7) data will be .033 Using Excel linest, and including x(1) and x(7) data, the new beta(7) on the age in months will be 0.
1/1 points
10. Use the Excel Linest Function Spreadsheet that was provided in question #9. What is the Correlation, R for the linear regression shown in the example? .778 or - .778 Correct Response
We know R is the square root of R^2 [Cell B42] but don’t immediately know whether the correlation is positive or negative.
.606 .367
https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
6/7
10/22/2016
Probability, AUC, and Excel Linest Function | Coursera
https://www.coursera.org/learn/analyticsexcel/exam/T3KJW/probabilityaucandexcellinestfunction
7/7