With ratios, we can visualize the relative size between two or more variables or numbers, In Excel, we also have to deal with ratios a lot. How can we calculate the ratio between two numbers in Excel and determine the percentage after that will discuss it here with adequate explanations.
What Is Ratio?
Two or maybe more numbers can be compared by using a ratio to express their relative values. The ratio actually achieves this by division. here the number that is going to be divided is called the antecedent and the other one is called the consequent.
The expression of ratios generally is a:b, where a and b could be integers decimals, or even fractions. Ratio values give us a comparatively easier understanding of the comparison between two variables or numbers. Although they are basically the same thing as division mathematically. But the expression is different.
How to Calculate Ratio Percentage in Excel: 4 Suitable Ways
We are going to use the below dataset to demonstrate how we are going to determine the Ratio of Number 1 to Number 2 in the Ratio column and the percentage of Number 1 with respect to the Number 2 column in the Percentage column.
1. Using GCD Function
The GCD function will determine the greatest common divisor between the two numbers directly. And then using that number we are going to calculate the ratio between number and percentage thereafter.
Steps
- In the beginning, select the cell D5 and enter the following formula:
=B5/GCD(B5,C5)&":"&C5/GCD(B5,C5)- After entering this formula you will notice that the ratio between cell B5 and C5 is now present in cell D5.
- Now drag the Fill Handle to cell D7.
- Then the range of cells D5:D7 is now filled with the ratio of numbers in the range of cell B5:B7 and the range of cells C5:C7.
- Now, select the cell E5 and enter the following formula:
=B5/C5
- Drag the Fill Handle to cell E7, you will see the range of cells E5:E7 now filled with the quotient value of Number 1 column by Number 2 columns.
- Now select the range of cells E5 to E7, and from the Home tab, click on the % (Percentage) sign on the Number group.
- Then the range of cells E5 to E7 now shows the percentage value of Number 1 of Number 2.
This is how we can calculate ratio percentages in Excel using the GCD function.
Breakdown of the Formula
- GCD(B5,C5): This function will return the greatest common divisor between the values in cells B5 and C5
- B5/GCD(B5, C5) and C5/GCD(B5, C5): They will return the quotient of the division of values in cells B5 and C5 by the value returned by the GCD function above.
- B5/GCD(B5,C5)&”:”&C5/GCD(B5,C5): Basically this function will return the values returned above with the ratio sign “:”.
Read More: How to Convert Percentage to Ratio in Excel
2. Combining SUBSTITUTE and TEXT Functions
Using the SUBSTITUTE function with the combination of the TEXT function we will calculate the ratio between two numbers and calculate the percentage.
Steps
- In the beginning, select the cell D5 and enter the following formula
=SUBSTITUTE(TEXT(B5/C5,"######/######"),"/",":")
- After entering this formula you will notice that the ratio between cell B5 and C5 is now present in cell D5.
- Now drag the Fill Handle to cell D7.
- Then the range of cells D5:D7 is filled with the ratio of number in the range of cells B5:B7 and range of cells C5:C7.
- Now select the cell E5 and enter the following formula:
=B5/C5
- Drag the Fill Handle to cell E7, you will see the range of cells E5:E7 now filled with the quotient value of Number 1 column by Number 2 columns.
- Now, select the range of cells E5 to E7. From the Home tab, click on the percentage sign in the Number group.
- After clicking on the percentage sign, the range of cells E5:E7 is now filled with the percentages of Number 1 with respect to Number 2.
This is how we can calculate ratio percentages in Excel using the SUBSTITUTE function.
Breakdown of the Formula
- TEXT(B5/C5,”######/######”): This function will return the quotient of the division of the cell B5 by C5 and format it as a fraction.
- SUBSTITUTE(TEXT(B5/C5,”######/######”),”/”,”:”): This formula will substitute the “/” with the “:” in the fraction.
Read More: How to Convert Ratio to Decimal in Excel
3. Applying Simple Division Method
We can simply divide numbers in the first columns by numbers in the second columns to get the ratio. But the output might not be an integer, like other methods.
Steps
- In the beginning, select the cell D5 and enter the following formula
=(B5/C5)&":"&"1"- After entering this formula you will notice that the ratio between cell B5 and C5 is now present in cell D5 with respect to 1.
- Now drag the Fill Handle to cell D7.
- Then the range of cells D5:D7 is filled with the ratio of number in the range of cell B5:B7 and range of cells C5:C7 with respect to 1.
- Now select the cell E5 and enter the following formula:
=B5/C5
- Drag the Fill Handle to cell E7. You will see the range of cells E5:E7 now filled with the quotient value of Number 1 column by Number 2 columns.
- Then select the range of cells D5:D7. And click on the percentage icon from the Home tab in the Numbers group.
- After clicking on the percentage icon, the range of cells E5:E7 is now filled with the percentages of Number 1 with respect to Number 2.
This is how we can calculate ratio percentages in Excel using simple division.
Read More: How to Do Ratio Analysis in Excel Sheet Format
4. Using Combined Formula
The combination of ROUND and TEXT functions will help us to calculate the ratio of two numbers in the Number 1 and Number 2 columns. We also use the LEFT, ROUND, SEARCH, and TEXT functions in this method.
Steps
- In the beginning, select the cell D5 and enter the following formula:
=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)&":"&MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)
- After entering this formula you will notice that the ratio between cell B5 and C5 is now present in cell D5.
- Now drag the Fill Handle to cell D7.
- Then the range of cells D5:D7 is now filled with the ratio of numbers in the range of cell B5:B7 and the range of cells C5:C7.
- In the beginning, select the cell E5 and enter the following formula
=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)/MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)
- After entering this formula you will notice that the percentages of cell B5 with respect to C5 are now present in cell E5.
- Now drag the Fill Handle to cell E7.
- Now, select the range of cells E5 to E7. From the Home tab, click on the percentage icon in the Numbers group.
- Then the range of cells E5:E7 is filled with the percentages of the numbers in the range of cells D5:D7. With respect to numbers in the range of cells C5:C7.
This is how we calculate ratio percentages in Excel by using combined formulas.
Breakdown of the Formula
- ROUND(B5/C5,5): This function will return the quotient of the division of values in cells B5 and C5. And round them up to 5 decimal digits.
- TEXT(ROUND(B5/C5,5),”###/###”): Then this function will format the values as fraction.
- SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”)): This formula will return the location of a particular character inside of a given text. Starting from the left side.
- LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1): It will extract the portion of the text from the left side of the text up to a specified location.In this case it will extract up to the location of “/”.
- MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3): This formula will extract a specified section length from a particular position. How many characters will be removed is specified. In this case, the three characters after the “/” will be extracted.
- LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1)&”:”&MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3): This section the extracted part by LEFT function and MID function is now assembled with keeping “:” in the middle.
- LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1)/MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3): In this function, the extracted part in LEFT function is now divided by the outcome of the MID function.
Read More: How to Graph Ratios in Excel
Download Practice Workbook
Conclusion
To sum it up, the question “How to calculate Ratio Percentage in Excel’ is answered here in 4 different ways. Starting from using the GCD function, then using the substitute with TEXT, ROUND, LEFT, MID, SEARCH, ROUND functions, etc., and by simple division. Among all of the methods used here, using the division and the GCD function is comparatively easier to use. Download the workbook to practice these methods. Please feel free to ask any questions or feedback through the comment section.
Related Articles
<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
You have an extra 0 in your formula in section 1:
=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C50)
it should read:
=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C5)
Thanks a lot for providing the correction. Both the Image and the Formula has been updated.