Sensitivity Specificity Positive and Negative Predictive Values using Microsoft Excel

In the video lecture we explore four metrics. Sensitivity, specificity, positive predictive value, and negative predictive value. This lecture follow on our exploration of probabilities.
In this video we consider two binary variables. The categories of the first indicates whether a subject has a trait (a disease) or not. The second is a variable that expresses the result of a test to indicate the presence or absence of the trait, by returning either a positive or a negative result.
Such a test can correctly indicate the presence of disease. Such a results is a true positive. It might return a negative result in the presence of the disease. This is a false negative. If the test returns a negative result in the absence of the disease, we term this a true negative result. Finally, if the test result is positive in the absence of the disease, we term the result a false positive.
We can use the joint frequencies of the four possible outcomes to calculate the proportion (or probability) of cases in which the test will be positive given that the disease is present (the sensitivity) or the proportion of cases in which the test returns a negative result, given that the disease is absent (the specificity). The sensitivity and specificity help us determine if test should be used.
Once the test is used, and returns a positive or negative result, we can calculate its predictive values. The positive and negative predictive values require knowledge of the prevalence of the disease in the population. The positive predictive value (PPV) indicates the probability that a subject really has the disease in the case of a positive result. The negative predictive value (NPV) indicates the probability that the disease is really absent given a negative result.
If the prevalence is lower in the population, the resultant PPV trends lower and the NPV trends higher. If the prevalence is higher, the PPV trends higher and the NPV trends lower.
The original file can be found at github.com/jua...
Click on the download button in the top right of the screen to get your own copy of the spreadsheet.