Table of Contents
Calculating ratios in Excel can be done by dividing one number by another and formatting the resulting value as a percentage. To illustrate, if you would like to calculate the ratio of apples to oranges in a basket, you would divide the number of apples by the number of oranges and format the result as a percentage. To do this, enter the formula “=A2/B2″ into a blank cell, where A2 is the cell containing the number of apples and B2 is the cell containing the number of oranges. Then, select the cell containing the formula and click the Percent Style button in the Home ribbon. This will display the ratio of apples to oranges in the form of a percentage.
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 .