Calculating ratios in Microsoft Excel can be an efficient and straightforward method for comparing different values in financial analysis, measuring productivity, or evaluating performance. In this article, we will explore the process of calculating ratios in Excel using various methods such as the divide and GCD functions.

Method 1: Using The Divide Function

Step 1:

Enter your data into two separate cells. For example, let’s assume we want to find the ratio between numbers 4 and 20.

A1: 4

B1: 20

Step 2:

In another cell, enter the formula “=A1/B1” to divide the first number by the second number. In this example, you would enter:

C1: =A1/B1

The result will appear in cell C1 as “0.2”. To display it as a simplified ratio (e.g., “1:5”), follow steps listed in Method 3.

Method 2: Using The GCD(Greatest Common Divisor) Function

Step 1:

Enter your two numbers in separate cells:

A1: 4

B1: 20

Step 2:

In another cell, use the GCD function to find the greatest common divisor of these two numbers:

C1: =GCD(A1,B1)

The result will appear in cell C1 as “4”.

Step 3:

Now, divide each of your original values by the GCD result:

D1: =A1/C1

E1: =B1/C1

In our example, this would calculate as follows:

D1 (Ratio part A) : =4/4 = １

E１(Ratio part B): =20/4=５

Step 4:

Combine the results from Step3 separated by a colon to create the final ratio_result.

F1: =D1&”:”&E1

This will display the ratio as “1:5” in cell F1.

Method 3: Converting the Decimal Result to a Simplified Ratio

Step 1:

Let’s assume you have a decimal result from Method 1, like “0.2”, in cell C1.

Step 2:

To convert it into a simplified ratio, first find the number of decimal places using the following formula:

D1: =LEN(SUBSTITUTE(TEXT(C1,”0.00000″)&””,”.”,””))-LEN(TEXT(C1,”0.00000″))

Step 3:

Next, calculate the denominator using the formula “=10^(number of decimal places)”:

E1: =10^D1

In our example, E1 would equal “10”.

Step 4:

Calculate the numerator using the following formula:

F1: =ROUND(C1*E1,0)

In our example, F1 would equal “2”.

Step 5:

Find GCD for numerator and denominator:

G1: =GCD(F1,E1)

In this case, GCD would be “2”.

Step 6:

Divide both numerator and denominator by the GCD obtained in Step 5:

H1: =F1/G1

I１：=E１/G１

In our example, this would result in H１：＝２/２＝１ and I１：＝１０/２＝５

Step 7:

Combine the results from Step６ separated by a colon to create simplified_ratio_result.

J１：＝H１＆”：”＆I１

In our example, J1 would now display as “１：５”.