< Go Back

Basic inventory formula example

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 (“,”)

GCD formula:

=GCD(number1, number2)

As we are finding the GCD of C2 and D2, we will calculate it by typing

=GCD(C2,D2)

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:

Ratio:

=number1/GCD(number1,number2)&”:”&number2/GCD(number1,number2)

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:

Ratio:

=C2/GCD(C2,D2)&”:”&D2/GCD(C2,D2)

And since, we have already calculated the GCD(C2,D2) in column E, we will just replace it by E2 as shown below:

C2/E2&”:”&D2/E2

Figure 4. Calculating Ratio of Two Numbers In Excel Using GCD

The results will be calculated as follows:

Ratio:

=C2/GCD(C2,D2)&”:”&D2/GCD(C2,D2)

200/GCD(200,100) &”:”& 100/GCD(200,100)

200/100 &”:”& 100/100

2 &”:”& 1

2:1

Figure 5. Calculating Ratio of Two Numbers

Note 

The GCD function can only be used for integers.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar