How to Calculate a Ratio in Excel

To calculate a ratio in Excel, you divide one number by the other, then decide how to show it: as a plain number (20), as text in the familiar style (20:1), or as a simplified whole-number ratio (16:9). Excel has no RATIO function, but division, ROUND, the & operator and GCD cover every case. This guide is for anyone comparing two numbers, such as students per teacher, wins to losses or debt to income.

What is a ratio in Excel?#

A ratio compares two numbers by showing how many times one fits into the other. "20 to 1" means there are 20 of the first for every 1 of the second. In Excel, a ratio is just a division, plus some formatting if you want it to look like 20:1.

All the examples below use this table of schools in A1:C6:

How to calculate a ratio with simple division#

The simplest ratio is the first number divided by the second. In D2, type:

=B2/C2

The result is 20: Oak has 20 students per teacher. Fill the formula down to D6 (Windows: Ctrl+D; Mac: ⌘D) to get 12, 22.5, 12 and 23.333… for the other schools.

This plain number is the most useful form for analysis. You can sort it, chart it, average it and compare it with a target. The order matters: =C2/B2 gives the opposite ratio, 0.05 teachers per student. Put the thing you are counting "per" second. If you're new to typing formulas like this, the first formulas lesson walks through it.

How to show a ratio as 20:1#

To display a ratio in the A:1 style, join the result of the division to the text ":1" with the & operator. Round it first so you don't get long decimals:

=ROUND(B2/C2,1)&":1"

To put the 1 first, reverse it: ="1:"&ROUND(B2/C2,1) returns 1:20.

The result is text, not a number. It looks right, but you can't sum, average or chart it, and sorting goes by character, not by value, so "9:1" lands after "20:1". Keep the plain division in one column and the display version in another.

How to calculate a simplified ratio with GCD#

To show a ratio as two whole numbers, such as 45:2, divide both numbers by their greatest common divisor (GCD), the largest whole number that divides both evenly:

=B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)

For Pine, GCD(315,14) is 7, so the result is 45:2 (315 ÷ 7 = 45 and 14 ÷ 7 = 2). For Oak it is 20:1, and for Birch 70:3.

This is the method for screen sizes and mixing ratios. A screen of 1920 by 1080 pixels gives GCD 120, so the formula returns 16:9. GCD only works with whole numbers: it drops anything after the decimal point, so 2.5 is treated as 2. Multiply decimals up to whole numbers first (for example, by 10) before you use it.

How to round a ratio#

Use ROUND when the number of decimals matters to the result, and number formatting when it only matters to the look. =ROUND(B6/C6,1) stores 23.3. Formatting =B6/C6 to one decimal also shows 23.3, but Excel keeps 23.333… underneath, which changes later sums.

=ROUND(number, num_digits)

num_digits is how many decimals to keep: 1 gives 23.3, 0 gives 23 and 2 gives 23.33. ROUNDUP and ROUNDDOWN always round in one direction, which helps when a ratio is a limit, such as "no more than 23 students per teacher". The ROUND function guide has more examples.

How to show a ratio as a percentage#

A percentage compares a part with the whole, so divide by the total, not by the other part. A team with 45 wins and 15 losses has a win-to-loss ratio of 3:1, but a win rate of:

=45/(45+15)

That returns 0.75. Apply the Percent format (Home > %, or Ctrl+Shift+% on Windows and ⌃⇧% on a Mac) to show 75%. Mixing these two up is a common mistake: 45/15 = 3 is a ratio, not 300% of anything useful. Percentages, ratios and discounts all come down to the same division; see how to calculate a discount in Excel and how to calculate tax in Excel for related formulas.

How to compare ratios with a target or a total#

Once the ratio is a plain number, you can test it like any other value. To flag schools with more than 20 students per teacher:

=IF(B2/C2>20,"Over","OK")

Filled down, this returns Over for Pine (22.5) and Birch (23.3), and OK for the rest. Oak is exactly 20, so it is not over.

For an overall ratio across all schools, divide the totals. Don't average the separate ratios:

=SUM(B2:B6)/SUM(C2:C6)

This returns 17.9 when rounded (1,041 students ÷ 58 teachers). =AVERAGE(D2:D6) on the five ratios gives 18.0, because it treats a small school the same as a large one. Here the gap is small, but it grows when the groups are very different in size. The sum-divided-by-sum version is the true overall ratio.

How to avoid #DIV/0! in a ratio#

If the second number is zero or the cell is empty, the division returns #DIV/0!. Catch it with IFERROR:

=IFERROR(B2/C2,"")

This shows a blank instead of the error. To be stricter, test for zero yourself, so other errors (such as text in the cell) still show:

=IF(C2=0,"",B2/C2)

Use one of these whenever ratios feed a report or an invoice. Error values in one cell spread to every total that uses it; the invoice with formulas guide shows the same habit for line totals.

Practise it#

Try the exercise on this page: it combines division, ROUND and & in one formula. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet to control decimals properly, and go back to the first formulas lesson if division and cell references still feel new. Each step is checked as soon as you press Enter.