How to Calculate Ratio in Excel | Excel Formula

2 Mins read

This article is been written to find calculate the ratio in Excel

Ratio: In simple mathematics, relationship or comparison between two more numbers is known as ratios. Ratios are normally written as “:” to show the connection between two numbers, for instance. You can learn such an advanced excel tutorial in our advanced excel dashboard course.

If there are 2 red balls and 3 blue balls on the table you could write their ratio as [2:3]. Unfortunately, there is no systematic way to calculate a ratio, but there is an easy way around for doing the same, you can try the below guidelines to calculate the ratio in Excel.

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

Method 1: How to Calculate the Ratio in Excel.

Calculate Ratio Formula: To calculate the Ratio in excel, the Shop 1 will be divided by GCD and the Shop 2 will be divided by GCD. You can place a colon between those two numbers. Example: To see the ratio, enter this formula in cell E2 = B2/GCD(B2,C2)&”:”&C2/GCD(B2,C2).

calculate ratios in excel

Calculate the Ratio of Products between Two Shops

Step 1: Create a table the same as the above picture. This table is showing a number of products in the store of two shops. We are going to calculate the ratio of each product between these 2 shops. Column D is for calculating GCD and Column E is for a ratio.

What is GCD?

The GCD Function is used to calculate the greatest common denominator between two or more values. We can use GCD to then create our ratios. The GCD Function is extremely easy to use. Simply type =GCD( followed by the numbers that you wish to find the greatest common divisor of (separated by commas).

Formula: =GCD(Number 1, Number 2)

Once you understand how GCD works you can use the function to find your ratios by dividing each of the two numbers by their GCD and separating the answers with a colon “:”. Hence, now we are ready on how to calculate a ratio.

First, we need to calculate GCD in column D then we will calculate the ratio in column E using GCD.

calculate ratios in excel

Step 2: In cell D2 type this formula to calculate GCD between B2 and C2. =GCD(B2,C2)

calculate ratios in excel

Step 3: Now we have got GCD result 5 for cell B2 and C2. So, now copy this formula into other cells of the column using Fill Handle dragging below to other cells of column D.

calculate ratios in excel

Step 4: Now we have got all GCD values of all rows in column D. Now let’s Calculate ratio.

Step 5: In a cell, E2 type this formula to calculate ratio:

=B2/D2 &”:”& C2/D2

Press ENTER. Now we have got the ratio between 100 and 15 as 20:3 in cell E2.


Step 6: Copy the ratio formula in another cell of column E. Finally, we have got the ratio of the whole table. So, we have successfully learned how to calculate the ratio in excel.

Pivot Tables – Guidebook for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads. Most Popular Tricks are #3, #7 & #12

Method 2: Create the Ratio Formula using Text Function

Another method of calculating Ratio using TEXT and SUBSTITUTE – these work in all versions of Excel.

Step 1: To calculate the ratio for each cell

Enter this formula in cell D2 cell =TEXT(B2/C2,”0/0″)

The result will be 20/3.

Note: To practice with us. We have shared the Excel sheet. You can download it from the link below

Click here…

Step 2: Now drag n drop the cell D2 formula to the below cells as well

Select & hold the bottom right corner of cell D2 then drag it to the bottom

Step 3:Now to convert slash into colon use substitute function

Enter this formula in cell D2 cell =SUBSTITUTE(TEXT(B2/C2,”0/0″),”/”,”:”)

The result will be from 20/3 to 20:3

Final Output

Related posts

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…

1 Comment

Comments are closed.