A ratio is used to compare or find a relationship between two or more numbers. And the sign used to represent ration is a colon “:”.
For example, if there are 13 apples and 10 oranges in a basket, the ratio of apples to oranges will be 13:10 or 13/10. This article explains to you how to calculate a ratio from two numbers in Excel with the help of following simple steps and formula.
You need to use following formula syntax to calculate a ratio from two numbers:
This syntax of formula uses two numbers (number1 and number2) along with another function GCD, combining the two result with Ampersand operator (&) and colon sign (:).
Now the syntax for Excel GCD function is:
Excel GCD function is used to find out the Greatest Common Divisor between any two or more numbers used in above formula syntax to calculate a ratio from two numbers.
First, we will calculate GCD to find the ratios later on. The formula may seem tricky but is extremely easy to calculate by simply typing GCD in the desired cell and then by entering the numbers whose greatest common denominator is required (separate the numbers with the help of a comma (“,”) as per GCD formula above.
For instance, if we are finding the Greatest Common Divisor of C2 and D2, then we will calculate it by using Excel GCD function in a cell. After calculating GCD we will use this output in the formula to calculate a ratio from two numbers as given in formula section above.
To create a table as shown in the below picture. In our example, we are going to calculate the ratio between the heights and weights of five different persons. In column E, we will calculate the GCD while in Column F, the ratio will be calculated.
In cell E2 we will use the following formula to calculate Greatest Common Divisor using GCD function, and extend will it to the last entry using “Fill Handle” option. (please see the Figure 2 below)
Figure 1. Calculating the Greatest Common Divisor (GCD)
Figure 2. Fill Handle Used For Concatenation
Now we have calculated the GCD of all the five entries in column E. The next step is to calculate the ratio by the formula given below. In our example of height and weight, we can replace number 1 and number 2 by C2 and D2 respectively. So, the formula will become:
And since, we have already calculated the GCD(C2,D2) in column E, we will just replace it by E2 as shown below:
Figure 3. Calculating Ratio of Two Numbers In Excel Using GCD
The results will be calculated as follows:
=200/GCD(200,100) &”:”& 100/GCD(200,100)
=200/100 &”:”& 100/100
=2 &”:”& 1
Figure 4. Calculating Ratio of Two Numbers
- The Excel GCD function can only be used for integers.