Logistic Regression is a statistical method that we use to fit a regression model when the response variable is binary. To assess how well a logistic regression model fits a dataset, we can look at the following two metrics:

**Sensitivity:**The probability that the model predicts a positive outcome for an observation when indeed the outcome is positive. This is also called the “true positive rate.”**Specificity:**The probability that the model predicts a negative outcome for an observation when indeed the outcome is negative. This is also called the “true negative rate.”

One way to visualize these two metrics is by creating a **ROC curve**, which stands for “receiver operating characteristic” curve. This is a plot that displays the sensitivity and specificity of a logistic regression model.

The following step-by-step example shows how to create and interpret a ROC curve in Excel.

**Step 1: Enter the Data**

First, let’s enter some raw data:

**Step 2: Calculate the Cumulative Data**

Next, let’s use the following formula to calculate the cumulative values for the Pass and Fail categories:

- Cumulative Pass values:
**=SUM($B$3:B3)** - Cumulative Fail values:
**=SUM($C$3:C3)**

We’ll then copy and paste these formulas down to every cell in column D and column E:

**Step 3: Calculate False Positive Rate & True Positive Rate**

Next, we’ll calculate the false positive rate (FPR), true positive rate (TPR), and the area under the curve AUC) using the following formulas:

- FPR:
**=1-D3/$D$14** - TPR:
**=1-E3/$E$14** - AUC:
**=(F3-F4)*G3**

We’ll then copy and paste these formulas down to every cell in columns F, G, and H:

**Step 4: Create the ROC Curve**

To create the ROC curve, we’ll highlight every value in the range **F3:G14**.

Then we’ll click the **Insert** tab along the top ribbon and then click **Insert Scatter(X, Y)** to create the following plot:

**Step 5: Calculate the AUC**

The more that the curve hugs the top left corner of the plot, the better the model does at classifying the data into categories.

As we can see from the plot above, this logistic regression model does a pretty good job of classifying the data into categories.

To quantify this, we can calculate the AUC (area under the curve) which tells us how much of the plot is located under the curve.

The closer AUC is to 1, the better the model. A model with an AUC equal to 0.5 is no better than a model that makes random classifications.

To calculate the AUC of the curve, we can simply take the sum of all of the values in column H:

The AUC turns out to be **0.802662**. This value is fairly high, which indicates that the model does a good job of classifying the data into ‘Pass’ and ‘Fail’ categories.

**Additional Resources**

The following tutorials explain how to create other common plots in Excel:

How to Plot a CDF in Excel

How to Create a Survival Curve in Excel

How to Create a Statistical Process Control Chart in Excel

Thank you for the post, it is very helpful. One question, though. Say I have decoys that involve both true negatives and false negatives. And they are supposed to produce different energy values. Should I set various energy intervals and run the test and distinguish how many are true and how many are false negatives among them and write in the corresponding cell?

Thank you, it helped me a lot.

Nevertheless, it is not totaly correct: in this way the AUC is overestimated.

You can try inserting in F an G the same values (like it was a teoretical diagonal), it will not give you 0.5.

The correct formula should be:

((F3-F4)*G3)-((F3-F4)*(G3-G4)/2)

Really its a helpful model, easy to understand

Hello there is a mistake for line :

FPR: =1-D3/$D$14

TPR: =1-E3/$E$14

There is an inversion the good is :

TPR: =1-D3/$D$14

FPR: =1-E3/$E$14