Home>Blogs>Excel Tips and Tricks>Calculate Ratio in Excel
Ratio Calculation in Excel
Excel Tips and Tricks VBA

Calculate Ratio in Excel

In this article, we have explained how to calculate the RATIO between two number in Excel. We have used the Excel formula as well as the User defined function in VBA to calculate the RATIO in Excel

RATIO is used to compare two values with each other. In the Excel we don’t have the single function to calculate the ratio.

We have used GCD function of excel to get the Ratio between two numbers. GCD returns the greatest common divisor between two or more numbers.

Below is the formula which we have used to calculate the Ratio in Excel:

=B2/GCD(B2:C2) & " : " & C2/GCD(B2:C2)
Ratio Calculation in Excel
Ratio Calculation in Excel

We have also created a User defined function in VBA to calculate the Ratio in Excel. Below is the code-

Function RATIO(first_number As Long, second_number As Long) As String


Dim gcd As Long

gcd = Application.WorksheetFunction.gcd(first_number, second_number)

RATIO = first_number / gcd & " : " & second_number / gcd

End Function

After putting this UDF in the module of Visual Basic Editor, you can put the RATIO function in Excel-


Ratio Function created in VBA
Ratio Function created in VBA


Click here to download the practice file.

Watch the step by step video tutorial:

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!