Method 1 – When the Number of the Population Is Provided
1.1. Using the GCD Function
 Create a New Column for GCD (Greatest Common Divisor): Add a new column in your dataset to calculate the GCD.
 Calculate the GCD Value:
 Click on cell E5 (or any other empty cell in the GCD column).
 Enter the following formula in the formula bar:
=GCD(C5,D5)
Here, C5 represents the number of males, and D5 represents the number of females.

 Drag the Fill Handle icon down to copy the formula to other cells in the GCD column.
 Compute the MaleFemale Ratio:
 Select cell F5 (or any other empty cell in the ratio column).
 Enter the following formula in the formula bar:
=C5/E5&":"&D5/E5
This formula divides the male and female counts by the GCD value to obtain the desired ratio.

 Drag the Fill Handle to apply the formula to the remaining cells in the ratio column.
1.2. Using TEXT and SUBSTITUTE Functions
 Create a New Column for TEXT:
 Add another column in your dataset to calculate the maletofemale ratio using the TEXT function.
 Calculate the Ratio Using TEXT:
 Click on cell E5 (or any other empty cell in the TEXT column).
 Enter the following formula in the formula bar:
=TEXT(C5/D5,"#/####")
This formula calculates the ratio and formats it as a fraction.

 Drag the Fill Handle to apply the formula to other cells in the TEXT column.
 Convert Fraction to Colon Format:
 Select cell F5 (or any other empty cell in the final ratio column).
 Enter the following formula in the formula bar:
=SUBSTITUTE(E5,"/",":")
This formula (the SUBSTITUTE function) replaces the slash (“/”) with a colon (“:”) to represent the maletofemale ratio.

 Drag the Fill Handle to fill in the remaining cells with the converted ratios.
Read More: How to Calculate Average Ratio in Excel
Method 2 – When the Male or Female Population % is Given
 Create a New Column for GCD (Greatest Common Divisor): Add a new column in your Excel sheet to calculate the GCD.
 Calculate the GCD Value:
 Select cell D5 (or any other empty cell in the GCD column).
 Enter the following formula in the formula bar:
=GCD(C5,(100C5))
Here, C5 represents the percentage of males (e.g., 40%).

 Drag the Fill Handle down to copy the formula to other cells in the GCD column.
 Compute the MaleFemale Ratio:
 Select cell E5 (or any other empty cell in the ratio column).
 Enter the following formula in the formula bar:
=C5/D5&":"&(100C5)/D5
This formula divides the male percentage by the GCD value and calculates the corresponding female percentage.

 Drag the Fill Handle to apply the formula to the remaining cells in the ratio column.
Read More: How to Calculate Ratio of 3 Numbers in Excel
Method 3 – Using COUNTIFS Function
 Create New Cells for Relevant Metrics:
 Add new cells for the following metrics:
 Total Active Male (cell G7)
 Total Active Female (cell G8)
 Greatest Common Divisor (GCD) (cell G9)
 Ratio (cell G10)
 Add new cells for the following metrics:
 Calculate the Total Active Male Count:
 Click on cell I7 (or any other empty cell).
 Enter the following formula in the formula bar:
=COUNTIFS(D5:D14,"Active",E5:E14,"Male")
Here, D5:D14 represents the range of cells containing the activity status (Active or Inactive), and E5:E14 represents the range of cells containing the gender (Male or Female).
 Calculate the Total Active Female Count:
 Click on cell I8 (or any other empty cell).
 Enter the following formula:
=COUNTIFS(D5:D14,"Active",E5:E14,"Female")
 Compute the Greatest Common Divisor (GCD):
 Click on cell I9 (or any other empty cell).
 Enter the following formula:
=GCD(I7,I8)
This formula calculates the GCD between the total active male count and the total active female count.
 Calculate the MaleFemale Ratio:
 Click on cell I10 (or any other empty cell).
 Enter the following formula:
=I7/I9&":"&I8/I9
This formula divides the total active male count and total active female count by the GCD to obtain the desired ratio.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
 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
<< Go Back to Ratio in Excel Calculate in Excel  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!