8.3 C
London
Friday, March 14, 2025
HomeExcelDescriptive Statistics in ExcelHow to Calculate Ratios in Excel (With Example)

How to Calculate Ratios in Excel (With Example)

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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 greatest common divisor (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)

The following screenshot shows how to use this formula in practice:

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:

calculate ratio in Excel

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 here.

Additional Resources

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

Excel: How to Find Unique Values from Multiple Columns
Excel: How to Match Two Columns and Return a Third

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories