To perform almost all mathematical and statistical calculations you need to know how to calculate ratios. They are the most important part of any statistical and mathematical calculations. Ratios describe the relationship between two values. When you are trying to compare any two numbers the easiest way is to find out the ratio of these numbers. In our day-to-day life many a times while preparing recipes, measuring distances, calculating mileage of car, etc we need to calculate the ratio.
This article will guide you in calculating ratio manually and using formula in Excel sheet.
Calculating ratio manually
The method which I will be explaining here follows the most basic calculations of mathematics. You must have learnt it in your school. If you don’t remember your mathematics lessons to calculate ratios from your school, here I am to explain them to you. All you need is a pencil and paper and if the comparing figures are bigger then you may need a calculator.
Step 1: Identify the numbers whose ratios are to be calculated.
Step 2: You also need to identify which number should come first. If you want to find the ratio of first number with second then write the first number above the line that divides two numbers or before the colon symbol and then the second number and vice versa.
Step 3: Write the number in the ratio form. If the first number is “10” and the second one is “15” then we write it in the form of ratio as,
10:15
OR
OR
10/15
Step 4: Divide the numbers such that you get a single number after the division. This is the result of the ratio.
Example:
Suppose you want to compute the miles per gallon of your car that uses 10 gallons to run over a distance of 300 miles.
Here, the numbers to be taken in ratio are 300 and 10 and we want to find out the miles per gallon of the car i.e. mileage when the car covers a distance of 300 miles for 10 gallons.
Therefore,
Mileage of the car = 300 miles/10 gallons
Mileage of the car = 30 miles per gallon
Calculating ratio using Excel sheet
Using Excel sheet you can find out the ratio between a set of numbers within no time. Although there is no direct method to display the ratio between two numbers in Excel sheet, you can do this using formulae.
There are two formulae for calculating ratio in Excel sheet, one formula is given by Douglas J. Roach and the other one is by Jerry Meng. Both these formulae reduce the number automatically into ratio.
Suppose you have got a list of numbers in two columns of your Excel sheet as,
| Column A | Column B |
| 10 | 12 |
| 20 | 50 |
| 30 | 10 |
Formula by Douglas J. Roach
= (LEFT (TEXT (A1/B1,”####/####”), FIND (”/”, TEXT (A1/B1,”####/####”))-1) &”:”
&RIGHT (TEXT (A1/B1,”####/####”), LEN (TEXT (A1/B1,”####/####”))
-FIND (”/”, TEXT (A1/B1,”####/####”))))
This formula has a character limit of 4.
Formula by Jerry Meng
Formula given by Jerry Meng is much simpler than the previous formula. The only drawback is that it does not have a four-character limit.
To use this formula, there should be Analysis Toolpak Add In installed in your system. The formula is given as
=A1/GCD (A1, B1) &":"&B1/GCD (A1, B1)
These formulae will help you in finding ratio of these sets of numbers in an Excel Sheet instantly.Related Tags: how to calculate ratio, how to calculate ratios, calculate ratio, calculate ratios, calculating ratio, how to calculate a ratio, calculating ratios, calculate ratio between two numbers
Related Content:
Happy to see my formula posted! It does bear mentioning, however, that by changing the number of # signs, one can increase or decrease the complexity of the result.
Using a similar example. if A1 contains 1,527, and B1 contains 31,822, you’ll get 1527:31822 with #####.
As written, the result is 268:5585 (####).
With ## you get 25:521, and with a single # in each instance you get a more elegant 1:21.
Hope this helps your blog’s readers!
DJR