How to Calculate Male Female Ratio in Excel (3 Methods)

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): Start by adding 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 Male-Female 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 male-to-female 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 male-to-female ratio.

- Drag the Fill Handle to fill in the remaining cells with the converted ratios.

Method 2 – When the Male or Female Population % is Given

Create a New Column for GCD (Greatest Common Divisor): Start by adding 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,(100-C5))

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 Male-Female Ratio:
- Select cell E5 (or any other empty cell in the ratio column).
- Enter the following formula in the formula bar:

=C5/D5&":"&(100-C5)/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.

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)
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).