The ROC Curve is a plot of values of the False Positive Rate (FPR) versus the True Positive Rate (TPR) for a specified cutoff value.

Example 1: Create the ROC curve for Example 1 of Classification Table.

We begin by creating the ROC table as shown on the left side of Figure 1 from the input data in range A5:C17.

Figure 1 – ROC Table and Curve

First we create the cumulative values for Failure and Success (columns D and E) and then the values of FPR and TPR for each row (columns F and G). E.g. the entries for row 9 are calculated via the following formulas:

Figure 2 – Selected formulas from Figure 1

The ROC curve can then be created by highlighting the range F7:G17 and selecting Insert > Charts|Scatter and adding the chart and axes titles (as described in Excel Charts). The result is shown on the right side of Figure 1. The actual ROC curve is a step function with the points shown in the figure.

Observation: The higher the ROC curve (i.e. the closer to the line y = 1) the better the fit. In fact the area under the curve (AUC) can be used for this purpose. The closer AUC is to 1 (the maximum value) the better the fit. Values close to .5 show that the model’s ability to discriminate between success and failure is due to chance.

For Example 1, the AUC is simply the sum of the areas of each of the rectangles in the step function. The formula for calculating the area for the rectangle corresponding to row 9 (i.e. the formula in cell H9) is shown in Figure 2. The formula for calculating the AUC (cell H18) is =SUM(H7:H17). The calculated value of .889515 shows a pretty good fit.