The ratio is used to compare or find a relationship between two or more numbers. And the sign used to represent it 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 ratio can also be calculated with the help of excel by using the following simple steps.
How To Calculate The Ratio In Excel
Figure 1. The GCD Function in Excel
Calculating The Ratio Between Height And Weight Of Two Persons
First, create a table as shown in the above 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.
What is GCD?
GCD function is used to find out the Greatest Common Divisor between any two or more numbers. 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 we are finding the GCD of C2 and D2, we will calculate it by typing
in the cell next to them i.e. Cell E and extend it to the last entry using concatenation through “Fill Handle”. (please see the picture below)
Figure 2. Calculating the Greatest Common Divisor (GCD)
Figure 3. Fill Handle Used For Concatenation
Explaining The Ratio Calculation Using GCD
Now we have calculated the GCD of all the five entries in column E. The next step is to calculate the ratio by the following formula:
This complex looking formula simply explains the following thing;
(The number of time the GCD appears in number 1) &”:”& (the number of times GCD appears in Number 2)
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 4. 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 5. Calculating Ratio of Two Numbers
The GCD function can only be used for integers.