How to Calculate Ratio in Excel (4 Easy Formulas)

A ratio shows the quantitative relation between two positive numeric values, indicating how many times one number contains the other. More commonly, you may be accustomed to seeing ratios expressed as a fraction e.g. 1:3 holding the equivalence of 1/3.

Whether as a fraction, decimal, or with the ratio symbol, ratios have wide use in mathematics and even in the business world. The concept of ratios is paramount for analysis and you might be familiar with financial ratios (liquidity, profitability, efficiency ratios, etc.) in business, win/loss ratio in sports and games, or a simple ratio for quantitative measures in baking, measurement, etc.

Excel doesn’t have any function or feature as yet for calculating a ratio itself. Nonetheless, we can get our way around with some Excel tricks by using certain functions as part of simple formulas. We’ll demonstrate 4 methods of calculating ratios in Excel; namely, they are the division method and the GCD, SUBSTITUTE & TEXT, and the ROUND functions. Ready to dive in?

Let’s get calculating!

Number 1 as Denomination

Taking a small example, if you were to calculate the ratio of the numbers 60 and 120, it would be 1:2. This is the most typical form of ratios, with the terms of the ratios in the simplest form of divisors. This ratio can also be written as 0.5:1 in which case, the purpose of the ratio is to have the number 1 as the denomination.

This method may be required to keep the base stable and find the relative quantity of the other number against 1. We have a couple of methods under this form of ratio. Let's find out what they are.

Using Division Method

Beginning with the basic concept of ratios, our first method uses division to arrive at a ratio of one value against the other. The purpose of this method is not only to divide the values but also to determine the ratio against the number 1 as explained above.

We require the consequent to remain 1 and will aim to find the antecedent by dividing one value from the other. If this is confusing you, let us show you an example; it’s actually much easier than it sounds. Even if you were a math and stats backbencher, you’ll get hold of this in no time. See the formula below:

Espresso guide below. Let’s admit it, if you've never come across this before, you're thrilled right now (or enlightened, to say the least). The guide shows the measurements of brewed coffee (mostly an espresso shot) and milk in various coffee beverages. Since it would be incredibly helpful to have their ratios, we’ve wasted no time in calculating them.

Using the aforementioned formula, the values in column C have been divided by the values in column D. Dividing C3 by D3 (60/30) gives us 2. Now, to place this value against the number 1, we have used the & operator to add two text elements. The first is the classic ratio sign, the colon “:”. Along with that, we’ve added the number 1. The results of using this formula to calculate ratios in our example have turned out like this:

That was neat but matters aren’t always going to be as straightforward as this. You won’t always be working with numbers that produce clear-cut integers and decimals. If that’s the case for you, you can still use this formula with the ROUND function. In fact, we can show you how it's done.

Using ROUND Function

Calculating ratios is easy with the help of the ROUND function. How does the ROUND function help? The ROUND function rounds a number off to the mentioned number of digits. The number of digits mentioned as a positive integer will round the number off to the integer number of decimal places.

For our second example, the ROUND function will be well handy to get, let's say a maximum of 2 decimal places. Why must you use the ROUND function and not adjust the result by decreasing the decimals or applying a number format? That is because only part of the result will be a decimal; the rest will be affixed text and therefore, the result as a whole can’t be given a number treatment. Hence, we need the ROUND function to take care of the decimal beforehand. See how it happens:

The formula itself is quite like the previous one, only with the ROUND function added. And why is it added? Cast a look at the example below. 10 teams have played a certain number of matches (listed in column C) and the matches won and lost by them have been jotted down in columns E and F. Looking at the numbers on their own doesn’t give us an instant understanding of the teams’ performances.

Calculating the win to loss ratio would be ideal and is achievable with the simple division method. But if the results carry several decimal places, the data would be hard to read. Consequently, the ROUND function has been added to the formula where it divides E3 by F3 and sets the decimal places to 2 digits. This takes the original answer of the division from 1.722222222 down to 2 decimal places as 1.72.

Next, the & operator joins the text string “:1” to the result of the ROUND function and the outcome is 1.72:1. This implies that for every loss, Team C has at least more than one victory. The rest of the ratios calculated using the ROUND function for the teams follow as:

Note: You could even set the decimal places in the ROUND function to one digit but for our scenario, it’s best to have 2 decimal places. If we look at Team B and D's ratios, had there been only 1 decimal place to display, 1.24 would be rounded down to 1.2, and 1.16 would be rounded up to 1.2 as well. This would have made both teams' win-loss ratios look identical. For a better assessment of the performance of the teams, 2 decimal places would be apt. You can choose to set more or lesser decimal places as per your data.

Terms of Ratio in Simplest Form

A ratio is said to be is the simplest form when the numbers are expressed as natural numbers with no common factor between them.

Using GCD Function

The ratios we just saw were very filtered down because the ratio had to be denominated by the number 1. As per your requirement, you may need the terms of the ratio to be in their simplest form of divisors instead of 1 as the denomination. For the latter type, you can calculate ratios with the GCD function.

The GCD function returns the greatest common divisor. The greatest common divisor of both numbers will be used to find a common denominator in the numbers. The common denominators will make up the terms of the ratio. The formula below will show you how to use the GCD function to calculate ratios:

We need two results from this formula that will be conjoined with a colon to arrive at the ratio. But if we process the GCD part first, the rest of the formula becomes quite simple to understand. The GCD of the numbers E3 and F3 has been calculated by the GCD function as 1. Since there is no common divisor, the number 1 has been returned. Now E3 is divided by 1 and F3 is also divided by 1, resulting in 31 and 18 respectively.

These results have been pieced together by the & operator but for the ratio effect, of course we require the colon. The output of the formula is 31:18. Here you can see how the GCD function has helped us to calculate the win/loss ratios for our example case:

Using SUBSTITUTE & TEXT Functions

This is another easy way to calculate ratios that will produce results identical to the GCD function. And that is by using the SUBSTITUTE and TEXT functions. The SUBSTITUTE function replaces the specified text with new text. The TEXT function converts a value into text using the provided number format.

How is that going to work for us? The first step is to convert the two individual numbers in E3 and F3 into a recognized number format, in this case, into a fraction. Then the SUBSTITUTE function can replace the forward-slash (/) in the fraction with a colon (:). That will effectively change the fraction into a ratio. This is the formula you can use to calculate a ratio using the SUBSTITUTE and TEXT functions:

The TEXT function starts off by taking the numbers 31 and 18 and converting them into a fraction as per the given number format "#/######”. We have the fraction 31/18 now. The SUBSTITUTE function replaces the forward-slash in 31/18 with a colon to form the ratio. The final result is 31:18. The other ratios calculated using this formula with the SUBSTITUTE and TEXT functions are shown as follows:

Are you good to calculate ratios in Excel now? We thought so too. With the right Excel tricks in hand, you should be tossing over ratios in no time.

If you find yourself a little lost on what to do next time you’re computing ratios, you can remember that ratios can be expressed as fractions which can be converted to ratios. Good trick, is it not? Want to master other Excel tasks? There’s a profusion to explore and we’ll be back with +1 to that profusion to clear some Excel confusion.