In this article, we will learn about Ratio in Excel. Here, we will explore everything about ratios and formulas for calculating the ratio.
A ratio is used to compare two or more numbers and get a clear idea about the relationship between these values. You can calculate the ratio between 2 numbers and sometimes you will need to calculate the ratio between 3 numbers. The male-female ratio is also important in some cases like data analysis or reports. After reading this article you will have a clear idea about ratios.
Download Practice Workbook
You can download the practice workbook from here.
How to Calculate Ratio in Excel
1. Use Simple Division to Calculate Ratio
- Select the cell where you want to calculate the ratio >> Write the following formula:
=C5/D5&":"&1- Press Enter >> Drag the Fill Handle down to copy the formula in other cells.
2. Calculate the Ratio Using Excel GCD Function
- Select the cell where you want to calculate the ratio >> Write the following formula.
=C5/GCD(C5,D5)&":"&D5/GCD(C5,D5)- Press Enter >> Drag the Fill Handle down to copy the formula in other cells.
🔎 How Does the Formula Work?
- GCD(C5,D5): Here, the GCD function returns the greatest common divisor of the numbers in cells C5 and D5.
- C5/GCD(C5,D5): The value in cell C5 is divided by the GCD of C5 and D5 here.
- D5/GCD(C5,D5): Now, the value in cell D5 is divided by the GCD of C5 and D5.
- C5/GCD(C5,D5)&”:”&D5/GCD(C5,D5): Finally, the Ampersand (&) operator joins these 2 formulas with a Colon (:).
3. Use SUBSTITUTE And TEXT Functions to Calculate Ratio
- Select the cell where you want to calculate the ratio >> Write the following formula.
=SUBSTITUTE(TEXT(C5/D5,"####/####"),"/",":")- Press Enter >> Drag the Fill Handle down to copy the formula in other cells.
🔎 How Does the Formula Work?
- TEXT(C5/D5,”####/####”): Here, the TEXT function returns the result in the desired format.
- SUBSTITUTE(TEXT(C5/D5,”####/####”),”/”,”:”): Now, the SUBSTITUTE function replaces Slash (/) with a Colon (:).
4. Apply the ROUND Function to Calculate the Ratio
- Select the cell where you want to calculate the ratio >> Write the following formula.
=ROUND(C5/D5,2)&":"&1- Press Enter >> Drag the Fill Handle down to copy the formula in other cells.
🔎 How Does the Formula Work?
- ROUND(C5/D5,2): Here, the ROUND function rounds the result to the given number of decimal places, which in this case is 2.
- ROUND(C5/D5,2)&”:”&1: Finally, the Ampersand (&) operator joins the result with a Colon (:) and 1.
How to Calculate Ratio Percentage in Excel
- Select the cell where you want to calculate the ratio percentage >> Write the following formula.
=C5/D5- Press Enter >> Drag the Fill Handle down to copy the formula in other cells.
Things to Remember
- As Excel does not provide a function for calculating ratios, you have to use functions according to your need to calculate Ratio in Excel.
- The second number can not be zero. Otherwise, it will return an error.
Frequently Asked Questions
1. What Is the Formula for the Ratio of 2 Numbers?
If the numbers are considered as x and y then the formula for the ratio will be x/y.
2. How to Calculate the Ratio of 3 Numbers?
You can use the GCD function to calculate the ratio of 3 numbers.
Conclusion
In this article, we learned about Ratio in Excel. We explored different types of ratios like average or percentage. Here, we also calculated ratios for different situations. We hope this article was helpful to you. If you have any questions or queries, you can let us know in the comment section below.
Ratio in Excel: Knowledge Hub
- How to Calculate Ratio of 3 Numbers in Excel
- How to Calculate Average Ratio in Excel
- How to Calculate Male Female Ratio in Excel
- How to Calculate Sortino Ratio in Excel
- How to Calculate Sharpe Ratio in Excel
- How to Calculate Odds Ratio in Excel
- How to Calculate Compa Ratio in Excel
- How to Calculate Current Ratio in Excel
<< Go Back to How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
