How to Calculate Ratio in Excel?

When doing financial analysis, it is common to calculate ratios in Excel.

We need to find ratios not just in financial analysis but also in many other situations (such as screen sizes or room dimensions).

In this article, I will show you a couple of simple ways to calculate ratios in Excel.

Method 1 – Using GCD Function to Calculate Ratio in Excel

In Excel, there is no direct function for calculating the ratio. But we can create formulas to calculate ratios using other helpful Excel functions.

One of the functions that we can use to calculate ratios is the GCD function.

The table below shows the number of male and female employees at three companies. Column A represents the company’s name, Column B shows the number of male employees, and Column C shows the number of female employees for each company.

Now I want to find the male-to-female employment ratio for each company in Column D.

I can use the below formula to calculate the ratios.

=B2/GCD(B2,C2)&" : "&C2/GCD(B2,C2)

In this formula, I am using the Excel GCD function to simplify the ratio.

The GCD function is used to calculate the highest common denominator of given numbers. The syntax of the GCD function is GCD(number1, [number2], …).

In the initial formula, first, I divide the first number (number of males) by the highest common denominator of the two numbers (males and females), which is given to us by the GCD function.

Then, I combine spaces and colons (“ : ”) using an ampersand (&) sign.

After that, I divide the second number (number of females) by the greatest common denominator of the two numbers (males and females).  

Also read: How to Convert Decimal to Fraction in Excel

Method 2 – Using the TEXT Function to Calculate Ratio in Excel

In addition to the previously mentioned method using the GCD function, I can calculate ratios in Excel by combining the SUBSTITUTE and TEXT functions.

The below table shows the number of male employees and the number of female employees of three companies.

Column A shows the company name, Column B shows the number of male employees, and Column C shows the number of female employees of each company.

In Column D, I want to find the male-to-female staff ratio for each company.

I can use the below formula to calculate the ratios.

=SUBSTITUTE(TEXT(B2/C2,"# / #"),"/"," : ")

First, I divide the number of males (which is cell B2) by the number of females (which is cell C2). This would give me the result in decimal (such as 1.6)

TEXT function allows us to format a given value by specifying the format in double quotes.

In our case, we first calculate the ratio in decimal by using the B2/C2 as the first argument, and then specify the format as “#/#”. This would give us the result of the calculation as a fraction.

The syntax of the TEXT function is TEXT(value, format_text).

I am using the B2/C2 as the first argument of the function. Then, for the second argument, I enter the format code as “#/#”.

I have to enter the format code within quotes. As I have entered one # after the forward slash (/), I will get the remainder up to one digit.

By increasing the number of # signs after the forward-slash, I can increase the accuracy of the ratio.

Now, I want to replace the forward slash with a colon. To improve the readability of the ratio, I want to insert the colon between two spaces. To do this, I am using the Excel SUBSTITUTE function.

The syntax of the SUBSTITUTE function is SUBSTITUTE(text, old_text, new_text, [instance_num]).

For the first argument, I am using the result of the TEXT function. For the second argument, I have to give the text that I want to replace. In this case, I want to replace the forward slash (/).

So, I enter a forward slash within quotes for the second argument.

Next, I have to enter the new text I want to have in place of the replaced text. As I want to have a colon in between two spaces, I enter that within quotes (“ : ”).

Also read: Excel Percentage Difference Formula

Method 3 – Using the ROUND Function to Calculate Ratio in Excel

If you want to compare ratios, it’s a good idea to have the same denominator (such as 1.6:1 or 2.5:1).

To calculate ratios like that, you can use Excel’s ROUND function.

The table below shows the number of male and female employees in three companies.

Each company’s name is shown in Column A along with its employee counts for male and female in Columns B and C, respectively.

Now I want to know how many male employees each company has in comparison to one female employee.

I can use the below formula to calculate the ratios.

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

The syntax of the ROUND function is ROUND(number,num_digits).

For the first argument of the ROUND function, I am entering the calculation of the number of males divided by the number of females.

So, I am entering B2/C2. The second argument is the number of digits to be displayed. In this case, I like to have only one digit.

If you want to see two digits enter 2, and so on.

Next, I combine the latter part of the ratio, common to this ratio calculation, using an ampersand (&) sign.

So, I am entering a space, a colon, a space, and 1 within quotes (“ : 1”).

In the above formula, we made the denominator consistent and rounded the numerator values.

You can also do the opposite, where the numerator is consistent and the denominator is rounded.

For example, if I want to find the number of female employees per male employee, I can change my formula to below.

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

Also read: How to Round Numbers in Excel Without Formula?

Method 4 – Using Custom Number Formatting to Calculate Ratio in Excel

You can also use custom number formatting to calculate ratios in Excel.

The table below shows the number of male and female employees at three different companies.

Column A represents the company’s name, Column B shows the number of male employees, and Column C shows the number of female employees for each company.

Now I want to find the male-to-female ratio for each company in Column D.

I can follow the below steps to do the calculation.

1. Go to cell D2 and type the below formula.

=B2/C2

2. Press “Control + 1” to open the “Format Cells” dialog box.

3. Go to the “Number” tab and select the “Custom” category.

4. Go to the “Type:” box and enter the below format code.

#/#

5. Click the “OK” button of the Format Cells Dialog box.

6. Copy cell D2 and select the below cells.

7. Go to the Home Tab and expand the Paste Options. Then, select “Formulas and Number Formatting”.

Then, I can see the ratios as follows. But, in this method,  the ratios are shown with a forward slash instead of a colon.

In this article, I’ve covered four different methods you can use to calculate ratios in Excel. In most cases, you’re better off using the GCD function method to get the ratios of two values. You can also use it to get the ratio in case you have three or more than three values.

And in case you want to keep a consistent denominator or numerator for all your ratios, you can use the round function technique I’ve covered.

Other Excel articles you may also like:

• Calculate Cumulative Percentage in Excel
• Convert KG to lbs (Pound) in Excel
• How‌ ‌to‌ ‌Use‌ ‌Pi‌ ‌(π) in‌ ‌Excel‌ ‌