How to calculate accuracy ratio in Excel using only a formula
Tags: excel, risk, statistics, datascience
Published: 2021-11-15 13:48:36

---
title: How to calculate accuracy ratio in Excel using only a formula
published: true
description: 
tags: excel, risk, statistics, datascience
canonical_url: https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/
cover_image: https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/index.png
---

Risk practitioners often use accuracy ratio (AR) to measure the discriminatory power of [binary classification](https://en.wikipedia.org/wiki/Binary_classification) models, such as models of credit default and insurance fraud. The closer AR is to 1, the higher the discriminatory power of the model.

## Definition

![Cumulative Accuracy Profile](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/cap.png?1641439855020)

The above diagram shows the [cumulative accuracy profiles](https://en.wikipedia.org/wiki/Cumulative_accuracy_profile) of a realistic model, a random model, and a perfect model.

As the proportion of observations increases, a perfect model would correctly classify all events before all non-events, but a random model would indiscriminately classify events and non-events together. A realistic model would be somewhere in between.

AR is the ratio of the area between the cumulative accuracy profiles of the realistic and random models (B) to the area between the cumulative accuracy profiles of the perfect and random models (A+B).

Unfortunately, Excel does not come with a native function for calculating AR. It is possible to calculate AR in Excel manually, but the process involves auxiliary rows and columns with complicated formulas that have to be adjusted as observations are added or removed. The problem is exacerbated with the standard error of AR.

## QRS.DISC.AR

Fortunately, [QRS Toolbox for Excel](https://addin.qrstoolbox.com) includes the [QRS.DISC.AR](https://addin.qrstoolbox.com/pages/docs/disc.ar) function for calculating AR. It is applicable to both grouped and ungrouped data.

To try QRS.DISC.AR yourself, first [add QRS Toolbox](https://addin.qrstoolbox.com/pages/support/#how-to-add-toolbox) to your instance of Excel and then open the [example workbook](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/example.xlsx).

![Ungrouped data](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/data-ungrouped.png?1641439855020)

The workbook contains 2 worksheets. In the UNGROUPED worksheet:
* Cells A2&ndash;A2001 contain credit scores for 2000 borrowers. The scores range between 0 for least creditworthy and 100 for most creditworthy.
* Cells B2&ndash;B2001 contain ones if credit default occurred and zeros otherwise.

![Grouped data](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/data-grouped.png?1641439855020)

In the GROUPED worksheet, cells A2&ndash;E8 contain data from the previous worksheet grouped into 7 score ranges, each with an alphabetical rating grade.

## Ungrouped data example

```excel
=QRS.DISC.AR(A2:A2001, B2:B2001)
```
![Formula](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/formula-1.png?1642648218982)

To calculate AR of the ungrouped data, open the UNGROUPED worksheet and enter the formula `=QRS.DISC.AR(A2:A2001, B2:B2001)` in cell D1. The result is -0.794, which is generally considered to be a large AR in absolute terms.

The result is negative-valued, because the credit scores and credit default events in this example are negatively correlated by design. In a perfect model, score=0 corresponds to event=1, and score=100 corresponds to event=0.

### Significance test

```excel
=QRS.DISC.AR(A2:A2001, B2:B2001, "TEST", "RAG")
```
![Formula](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/formula-2.png?1642648218982)

To determine the statistical significance of the AR, add `"TEST", "RAG"` to the formula. The result now contains a second row with a red/amber/green rating that summarizes the significance test.

The AR in this example has a green rating. A green/amber rating means the AR is significant at the 5%/10% significance level. A red rating means the AR is not significant at the 10% significance level.

Please read the [documentation](https://addin.qrstoolbox.com/pages/docs/disc.ar) to learn how to return the p-value and other useful information about the significance test, as well as how to change the significance levels of the ratings.

### Labels and transpose

```excel
=QRS.DISC.AR(A2:A2001, B2:B2001, "TEST", "RAG", "LABELS", TRUE)
```
![Formula](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/formula-3.png?1642648218982)

To add labels to the result, add `"LABELS", TRUE` to the formula. To swap the rows and columns of the result, add `"TRANSPOSE", TRUE` to the formula.

## Grouped data example

```excel
=QRS.DISC.AR(B2:B8, D2:E8)
```
![Formula](https://addin.qrstoolbox.com/pages/demos/how-to-calculate-accuracy-ratio-excel/formula-4.png?1642648218982)

To calculate AR of the grouped data, switch to the GROUPED worksheet and enter the formula `=QRS.DISC.AR(B2:B8, D2:E8)` in cell F9. The result is -0.797, which is similar to the AR of the ungrouped data.

The `TEST`, `LABELS`, and `TRANSPOSE` options can be used as before.

## Final remarks

If you find QRS.DISC.AR useful, please share this page with other potential users.