This tutorial explains how to compute confusion matrix in Excel, along with examples.

Please ensure you have the following two columns in Excel:

- Binary Dependent Variable (0 / 1)
- Predicted probabilities

Let's say you have data for dependent variable in cells **A2:A100** and probabilities in cells **B2:B100**.

The confusion matrix is shown in the table below for your reference.

Predicted Negative(0) | Predicted Positive(1) | |
---|---|---|

Actual Negative(0) | True Negative (TN) | False Positive (FP) |

Actual Positive(1) | False Negative (FN) | True Positive (TP) |

## Step 1 : Classify Predictions

The first step is to create a column to classify predictions. Let's say probability cutoff is 0.5 which means assigning 1 if the predicted probability is 0.5 or higher, otherwise 0. Enter the following formula in cell C2.

=IF(B2 >= 0.5, 1, 0)

Drag the formula down to the last row (i.e C100) to cover all rows.

## Step 2 : Count Positives and Negatives

Enter the following formula in cell D2.

=COUNTIFS(A2:A100, 1, C2:C100, 1)

Enter the following formula in cell E2.

=COUNTIFS(A2:A100,0, C2:C100, 0)

Enter the following formula in cell F2.

=COUNTIFS(A2:A100,1, C2:C100, 0)

Enter the following formula in cell G2.

=COUNTIFS(A2:A100,0, C2:C100, 1)

## Step 3 : Calculate Confusion Matrix

The True Positive Rate is also referred to as Recall in the confusion matrix. Enter the following formula in cell H2.

=D2 / (D2 + F2)

Enter the following formula in cell I2.

=G2 / (G2 + E2)

Enter the following formula in cell J2.

=E2/(E2+G2)

Enter the following formula in cell K2.

=F2 / (F2 + D2)

Enter the following formula in cell L2.

=(D2+E2)/COUNT(C2:C100)

Enter the following formula in cell M2.

=D2/(D2+G2)

F1 score is the harmonic mean of precision and recall. It performs well on a dataset having low event rate.

=2 * (H2 * M2) / (H2 + M2)

The following VBA code is a user-defined function that automates all the steps shown above to compute confusion matrix.

```
Function ConfusionMatrix(predictions As Range, actuals As Range, threshold As Double) As Variant
Dim n As Long
Dim tpr As Double, fpr As Double, tnr As Double, fnr As Double, accuracy As Double
Dim sorted_indices() As Long
Dim i As Long
' Check if the predictions and actuals ranges have the same number of elements
If predictions.Count <> actuals.Count Then
MsgBox "Predictions and actuals must have the same number of elements."
Exit Function
End If
n = predictions.Count
ReDim sorted_indices(1 To n)
' Sort predictions and actuals in descending order of predictions
sorted_indices = SortIndicesDescending(predictions)
Dim num_pos As Long, num_neg As Long
num_pos = WorksheetFunction.CountIf(actuals, 1)
num_neg = n - num_pos
If num_pos = 0 Or num_neg = 0 Then
MsgBox "There must be both positive and negative actual values."
Exit Function
End If
Dim tp_count As Long, fp_count As Long, tn_count As Long, fn_count As Long
tp_count = 0
fp_count = 0
tn_count = 0
fn_count = 0
' Calculate TP, FP, TN, FN for the specified threshold
For i = 1 To n
If predictions.Cells(sorted_indices(i)).Value >= threshold Then
If actuals.Cells(sorted_indices(i)).Value = 1 Then
tp_count = tp_count + 1
Else
fp_count = fp_count + 1
End If
Else
If actuals.Cells(sorted_indices(i)).Value = 1 Then
fn_count = fn_count + 1
Else
tn_count = tn_count + 1
End If
End If
Next i
tpr = Round(tp_count / num_pos, 4)
fpr = Round(fp_count / num_neg, 4)
fnr = Round(fn_count / num_pos, 4)
tnr = Round(tn_count / num_neg, 4)
accuracy = Round((tp_count + tn_count) / n, 4)
' Display TPR, FPR, TNR, and FNR in one message box
ConfusionMatrix = "Accuracy: " & accuracy & ", " & _
"True Positive Rate: " & tpr & ", " & _
"False Positive Rate: " & fpr & ", " & _
"True Negative Rate: " & tnr & ", " & _
"False Negative Rate: " & fnr
End Function
```

- In Excel, open the VBA editor by pressing
**Alt + F11**keyboard shortcut key. - Select
**Insert**>**Module**to create a module. - Paste the above VBA code in the module.
- Close the VBA editor by clicking the 'X' in the top-right corner.
- In any cell, enter this formula :
=ConfusionMatrix(B2:B100,A2:A100,0.5)

It follows this syntax -`=ConfusionMatrix(probability_range,dependent_variable_range,cutoff)`

How to Create ROC Curve in Excel

Excel Template : Gain and Lift Charts

Free Excel Add-In for Logistic Regression

Share Share Tweet