Roc Curve Excel 2021

(Adjust ranges to match your data; this is an array formula – press Ctrl+Shift+Enter on older Excel versions)

This write-up covers the theory, the step-by-step construction in Excel, and the calculation of the Area Under the Curve (AUC).

By understanding the strengths and limitations of using Excel for ROC curve analysis, users can make informed decisions about their data analysis workflow. roc curve excel

Assume:

You need to calculate the Cumulative True Positives and Cumulative False Positives for every unique probability score (threshold). (Adjust ranges to match your data; this is

| Actual (1=Yes, 0=No) | Predicted Probability | | :--- | :--- | | 1 | 0.92 | | 0 | 0.68 | | 1 | 0.85 | | 0 | 0.31 | | 1 | 0.97 | | ... | ... |

To create the curve, your spreadsheet should contain at least two primary columns: | Actual (1=Yes, 0=No) | Predicted Probability |

| Actual | Prob | Cum TP | Cum FP | Sensitivity (TPR) | FPR | | :--- | :--- | :--- | :--- | :--- | :--- | | 1 | 0.99 | 1 | 0 | 0.02 | 0.00 | | 1 | 0.95 | 2 | 0 | 0.04 | 0.00 | | 0 | 0.90 | 2 | 1 | 0.04 | 0.03 | | 1 | 0.88 | 3 | 1 | 0.06 | 0.03 | | ... | ... | ... | ... | ... | ... |

While the process is manageable, it can be tedious, especially for large datasets.

While specialized statistical software exists, you can build a fully functional ROC curve and calculate the directly in Microsoft Excel . 1. Key Concepts: Sensitivity vs. Specificity