Confusion matrix, precision and recall: a worked example in Excel

The four cells of a confusion matrix

First decide which class is the positive one: the thing you want to find. Here it is a customer who churns. Every prediction then falls into one of four cells:

• True positive (TP): predicted to churn, and did churn.
• False positive (FP): predicted to churn, but stayed. A false alarm, or type I error.
• False negative (FN): predicted to stay, but churned. A miss, or type II error.
• True negative (TN): predicted to stay, and stayed.

The matrix is usually drawn with the actual class in the rows and the predicted class in the columns. Check the layout before you read one: scikit-learn's confusion_matrix puts the negative class first, so its top-left cell is TN and its layout is [[TN, FP], [FN, TP]]. Many textbooks put TP in the top-left corner instead.

Counting TP, FP, FN and TN with COUNTIFS

Column B holds the churn score from the model, a probability between 0 and 1, and column C the actual outcome. Column D turns the score into a prediction with the threshold in D23: =IF(B2>=D$23,"Yes","No"). Each cell of the matrix is then one COUNTIFS with two conditions, actual and predicted:

• TP: =COUNTIFS($C$2:$C$21,"Yes",D$2:D$21,"Yes") returns 3.
• FP: actual "No", predicted "Yes": 2.
• FN: actual "Yes", predicted "No": 2.
• TN: actual "No", predicted "No": 13.

The four counts must add up to the 20 customers. The mixed references keep the actual column fixed and let the prediction column move, so the same formulas copied one column to the right count the predictions at threshold 0.3. If COUNTIFS is new to you, our SUMIFS examples cover the criteria syntax.

Worked example: a churn model on 20 test customers

Churn scores and actual outcomes are the yellow inputs, and so are the two thresholds in row 23. The predictions, the confusion matrix and every metric are formulas.

Worked example: a churn model on 20 test customers

	A	B	C	D	E
1	Customer	Churn score	Churned?	Predicted, t = 0.5	Predicted, t = 0.3
2	C01	0.08	No	No	No
3	C02	0.91	Yes	Yes	Yes
4	C03	0.36	No	No	Yes
5	C04	0.12	No	No	No
6	C05	0.55	No	Yes	Yes
7	C06	0.03	No	No	No
8	C07	0.47	Yes	No	Yes
9	C08	0.19	No	No	No
10	C09	0.72	No	Yes	Yes
11	C10	0.27	Yes	No	No
12	C11	0.05	No	No	No
13	C12	0.64	Yes	Yes	Yes
14	C13	0.24	No	No	No
15	C14	0.10	No	No	No
16	C15	0.41	No	No	Yes
17	C16	0.78	Yes	Yes	Yes
18	C17	0.15	No	No	No
19	C18	0.02	No	No	No
20	C19	0.29	No	No	No
21	C20	0.06	No	No	No
22
23	Threshold t			0.50	0.30
24	True positives (TP)			3	4
25	False positives (FP)			2	4
26	False negatives (FN)			2	1
27	True negatives (TN)			13	11
28	Accuracy			80.0%	75.0%
29	Precision			60.0%	50.0%
30	Recall (sensitivity)			60.0%	80.0%
31	F1 score			60.0%	61.5%
32	Specificity			86.7%	73.3%

The customers and churn scores are fictional. In practice, measure these metrics on a test set the model was not trained on.

Accuracy, and why it misleads

Accuracy = (TP + TN) ÷ all = (3 + 13) ÷ 20 = 80%. That sounds good until you notice that only 5 of the 20 customers churned. A "model" that predicts that nobody churns is right about the 15 who stayed, which gives 75% accuracy, while catching no churners at all. Whenever one class is rare, as with churn, fraud or disease, accuracy mostly measures the majority class. Precision and recall focus on the class you care about.

Precision and recall

Precision = TP ÷ (TP + FP) = 3 ÷ 5 = 60%. Of the customers the model flags, 60% really churn. The other 40% receive a retention offer they did not need.

Recall = TP ÷ (TP + FN) = 3 ÷ 5 = 60%. Of the customers who really churned, the model found 60%. Recall is also called sensitivity or the true positive rate.

A way to remember the difference: precision looks at everything the model predicted as positive, recall at everything that actually was positive. Which matters more depends on the cost of each mistake. A spam filter needs high precision, because binning a real email is expensive. A medical screening test needs high recall, because a missed case is worse than a follow-up test.

F1 score and specificity

F1 = 2 × precision × recall ÷ (precision + recall), the harmonic mean of the two, is 60% at threshold 0.5. The harmonic mean punishes imbalance: a model with 100% precision and 10% recall has an F1 of only 18.2%, not the 55% a simple average would suggest.

Specificity = TN ÷ (TN + FP) = 13 ÷ 15 = 86.7%, the share of loyal customers the model correctly leaves alone. It is the recall of the negative class. One minus specificity is the false positive rate, 13.3% here, which is the horizontal axis of a ROC curve.

Moving the threshold: the precision-recall trade-off

Column E uses a threshold of 0.3. The model now flags 8 customers instead of 5. It catches one more churner (TP 4, FN 1) but also raises two more false alarms (FP 4). Recall rises to 80%, precision falls to 50%, accuracy drops to 75%, specificity to 73.3%, and F1 edges up to 61.5%.

Which threshold is better is a business question. Suppose a retention offer costs $20 per flagged customer and a lost customer costs $300, and the offer keeps everyone who receives it. At 0.5: 5 offers × $20 + 2 missed churners × $300 = $700. At 0.3: 8 × $20 + 1 × $300 = $460. The lower threshold is cheaper even though its accuracy is worse. Repeat the calculation for many thresholds and you get a precision-recall curve.

More than two classes

With three or more classes, the confusion matrix has one row and one column per class. Calculate precision and recall for each class in turn, treating it as positive and all the others as negative. A macro average is the simple mean of the per-class values and treats every class equally. A micro average pools the TP, FP and FN counts of all classes first, so common classes weigh more.

Common mistakes

• Not deciding which class is positive. Swap the classes and false positives become false negatives. Precision and recall change with them.
• Judging a model on imbalanced data by accuracy. Compare it with the accuracy of always predicting the majority class, 75% here.
• Reading a matrix in the wrong orientation. Some tools put actuals in rows, others in columns, and scikit-learn puts TN in the top-left corner. Check the labels.
• Measuring on the training data. A model always looks better on the rows it learned from. Use a held-out test set.
• Keeping the default threshold of 0.5. The best threshold depends on what a false positive and a false negative cost.
• Averaging precision and recall. The F1 score uses the harmonic mean, which stays low when either of the two is low.