Do you need to calculate a ratio in Excel but don’t know where to start?
You’re in luck! In this blog post, we will show you how to do just that.
We will also provide some tips on how to make the process easier.
So read on and learn how to whip up those ratios in no time!
Table of Contents
How to Calculate Ratio in Excel
How To Calculate Ratio in Excel with multiple numbers
If you like to keep your data more private, use Excel.
It is not on the internet like Google Sheets; thus, the only way someone else can see your data is by emailing it to them.
Google Sheets and Excel might look different, but they have similar functions.
Step 1
First, you will have to find the Greatest Common Denominator or GCD between the two numbers.
For the GCD of the first data under Number 1 and Number 2.
Use the formula =GCD(A2,B2) to do it.
Step 2
Now that you have the GCD formula that you will use to find the ratio of the two numbers, start by looking for the 1st part of the ratio.
Use the formula =A2/GCD(A2,B2).
Step 3
The ratio will not be a ratio without the symbol ‘:.’
To add the colon, you must separate it from the first formula by adding ‘&’ between it and the symbol.
The formula will be =A2/GCD(A2,B2)&”:”.
Step 4
Lastly, to find the last number in the ratio, you must repeat the following formula with ‘&.’
The ratio for Number 2 is the same as how you find it for Number 1, but you will replace the dividend with “B2″.
The overall formula for the ratio in Excel is =A2/GCD(A2,B2)&”:”&B2/GCD(A2,B2).
Step 5
You don’t have to complete the lengthy calculation one at a time if you have a list of numbers that also require a ratio.
Hold the left mouse button down while clicking the little box in the bottom-right corner of the first ratio.
Step 6
Drag your cursor down until the last data.
Instead of running the line, you will notice a box forming on the cells where you want ratios.
Step 7
Release the left click, and you will see all the ratios.
How to calculate the ratio of multiple numbers?
There are instances where you will have to find the ratio of multiple numbers.
In this case, the structure is the same, but the formula will have additional elements.
Step 1
For this example, three sets of numbers need a ratio.
Find the greatest common denominator between these numbers using the formula =GCD(A2, B2,C2).
You will also use this for the 2nd and 3rd numbers of the ratio.
Step 2
Now that you have the GCD, you may start computing the first part of the formula.
Enter the formula =A2/GCD(A2,B2,C2).
Step 3
Like last time, to add the symbol for ratio, you will need to put an ‘&’ between the formula and the ‘:.’
The formula must be =A2/GCD(A2,B2,C2)&”:”.
Step 4
For the ratio of Number 2, you will do the same formula as Number 1.
However, before adding the next formula, add ‘&’ as a divider to the colon and the 2nd number of the ratio.
You will also replace the dividend with “B2” instead of “A2.”
The formula will then become =A2/GCD(A2,B2,C2)&”:”&B2/GCD(A2,B2,C2).
Step 5
Of course, before you proceed to the next number, you will need another ratio symbol before the last number, which in this case is the data in Number 3.
To do this, repeat step 3. The formula will then become =A2/GCD(A2,B2,C2)&”:”&B2/GCD(A2,B2,C2)&”:”.
Step 6
Finally, you will have to add the formula for the ratio for the last number.
You will have a complete ratio of three numbers. Just add the same formula with the symbol ‘&’ and a dividend of “C2″.
The whole formula will become: =A2/GCD(A2,B2,C2)&”:”&B2/GCD(A2,B2,C2)&”:”&C2/GCD(A2,B2,C2).
Step 7
To copy the same formula for the following data set, hold the left click to the little box on the cell and drag down until the last data.
There may be many formulas for one equation, but you are just repeating the same procedure repeatedly.
Now that you know the formula for ratio, you can try making many cups of coffee without worrying about how much sugar and creamer to put.
Summary
That’s the end of this tutorial.
We hope this article helps you learn how to calculate Ratio in Excel