A ratio is used to compare two numbers. Ratios are useful for understanding how large one value is compared to another.

For example, suppose A = 40 and B = 10.

To calculate the ratio of A to B we can use the following two step process:

Step 1: Find the (the largest integer that will divide each value)

• The largest value that will divide into both 40 and 10 is 10.

Step 2: Divide each value by the greatest common divisor and write the result as A:B.

• The ratio of 40 to 10 would be written as 4:1.

To calculate the ratio between any two numbers in Excel, we can use the following formula:

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

This particular formula calculates the ratio between the value in cell A2 to the value in cell B2, using the GCD function in Excel to automatically find the greatest common divisor between the two values.

The following example shows how to use this function in practice.

Example: Calculate Ratios in Excel

Suppose we have the following two lists of values in Excel:

Suppose we would like to calculate the ratio of Value 1 to Value 2 in each row.

We can type the following formula into cell C2:

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

It turns out that the greatest common divisor of 12 and 5 is 1. Thus, when we divide each value by 1 we’re simply left with a ratio of 12:5.

We can copy and paste this formula in cell C2 down to every remaining cell in column C to calculate the ratio for the two values in each row:

Here’s how to interpret the results:

• The ratio between 12 and 5 is 12:5.
• The ratio between 40 and 10 is 4:1.
• The ratio between 20 and 8 is 5:2.

And so on.

Note: You can find the complete documentation for the GCD function in Excel .

Additional Resources

The following tutorials explain how to perform other common tasks in Excel: