Understanding True Positives and Model Accuracy with Excel
When working with machine learning models, especially classification tasks, it's important to evaluate how well your model performs. One simple way to do this is using Excel—no programming needed!
In this post, we’ll walk through True Positives (TP), other related metrics, and how to visualize results.
What is a True Positive (TP)?
A True Positive occurs when:
Your model predicts a positive outcome, and the actual outcome is also positive.
Example: a COVID-19 detection model
| Patient | Actual (Has Disease?) | Predicted (Model Says?) |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 0 |
| 3 | 0 | 1 |
| 4 | 1 | 1 |
| 5 | 0 | 0 |
| 6 | 0 | 0 |
From this table:
- TP = 2 (Patients 1 and 4)
- TN = 2 (Patients 5 and 6)
- FP = 1 (Patient 3)
- FN = 1 (Patient 2)
Step 1: Enter Data in Excel
Create a table in Excel:
| A (Actual) | B (Predicted Class) |
|---|---|
| 1 | 1 |
| 1 | 0 |
| 0 | 1 |
| 1 | 1 |
| 0 | 0 |
| 0 | 0 |
Step 2: Compute TP, TN, FP, FN
Use COUNTIFS formulas:
- True Positive (TP):
=COUNTIFS(A2:A7,1,B2:B7,1) - True Negative (TN):
=COUNTIFS(A2:A7,0,B2:B7,0) - False Positive (FP):
=COUNTIFS(A2:A7,0,B2:B7,1) - False Negative (FN):
=COUNTIFS(A2:A7,1,B2:B7,0)
Step 3: Compute Accuracy
Accuracy measures the proportion of correct predictions:
=(TP + TN) / (TP + TN + FP + FN)
In our example: (2 + 2) / (2 + 2 + 1 + 1) = 0.667 (66.7%)
Step 4: Visualize with a Confusion Matrix
Create a simple confusion matrix in Excel:
| Predicted 1 | Predicted 0 | |
|---|---|---|
| Actual 1 | TP = 2 | FN = 1 |
| Actual 0 | FP = 1 | TN = 2 |
Add color formatting to make it visually appealing. This helps you quickly see where the model performs well and where it makes mistakes.
Bonus: ROC Curve and AUC
If you have predicted probabilities, you can also:
- Sort predictions by probability.
- Calculate TPR (Recall) and FPR for different thresholds.
- Plot FPR vs TPR → This is your ROC curve.
- Compute area under the curve (AUC) using Excel’s trapezoidal rule.
This shows how well your model separates the classes.
Takeaway
- TP, TN, FP, FN are the building blocks of model evaluation.
- Excel is great for learning and demonstrating these concepts.
- Once comfortable, you can use Python or MATLAB for larger datasets or publication-quality plots.
Comments
Post a Comment