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

Frequently, you might need to calculate the male-female ratio, gender diversity ratio, or sex ratio for study or any other purpose in Excel. The data types given may be different in many cases. In this article, I have shown how to calculate the male-female ratio from different sets of data steps with easy and quick steps.

How to Calculate the Male-Female Ratio in Excel: 3 Methods

In this section, I’ll show you 3 easy methods to calculate the male-female ratio in Excel.

1. When the Number of Population Is Provided

Suppose you have a dataset where the number of Male and Female populations are provided. Now, you need to find out the Male and Female ratio. I am showing you two ways to do this.

1.1. Applying the GCD Function to Calculate Male-Female Ratio

With the help of the GCD function, you can find out the ratio with very few quick steps.

Step 01: First, create a new column for GCD (Greatest Common Divisor).
Step 02: Click on cell E5 and insert the following formula in the formula bar.

=GCD(C5,D5)

Here, E5 is the starting cell for finding out the GCD value

Step 03: Now, drag the Fill Handle icon to copy the formula to other cells.

Step 04: Select the F5 cell and in the formula bar, insert the following formula:

=C5/E5&":"&D5/E5

Here, C5 and D5 refer to the number of Males and Females respectively. Besides, the E5 is the starting cell of the GCD.

Step 05: Similarly as in step 03, drag the Fill Handle for the rest of the cells in the column which will result in finding out your desired ratios.

1.2. Using TEXT and SUBSTITUTE Functions to Calculate Male-Female Ratio

You can also use the TEXT function and the SUBSTITUTE function to find out the male and female ratio.

Step 01: Create a new column for TEXT.
Step 02: Next, click on cell E5 and insert the following formula in the formula bar.

=TEXT(C5/D5,"#/####")

Step 03: Now, drag the Fill Handle for the remaining cells of the column.

Step 04: At this point, select cell F5 and type in the following formula in the formula bar.

=SUBSTITUTE(E5,"/",":")

Here, F5 is the starting cell for finding out the male-to-female ratio. E5 cell denotes the first cell of the Text column.

Step 05: Finally, drag the Fill Handle for the rest of the cells and you get your required ratios.

2. When the Male or Female Population % is Given

When you have the male or female population given and you need to find out the ratio of male to female from that, you can follow the following steps to do it efficiently in Excel.

Step 01: Create a new column for GCD.
Step 02: Now, select cell D5 and write down the following formula in the formula bar.

=GCD(C5,(100-C5))

Step 03: At this point, drag the Fill Handle for the remaining cell in the column.

Step 04: After that, select the E5 cell and insert the following formula in the bar.

=C5/D5&":"&(100-C5)/D5

Here E5 cell denotes the first value in the GCD column and C5 and D5 denote the Male and Female number respectively.

Step 05: At last, drag the Fill Handle for the rest of the cells of column E.

3. Using COUNTIFS Function to Calculate Male-Female Ratio

Suppose, you have a list of people consisting of genders male and female. In that list some of the male or female members are active and some are inactive. If you wish to find the male-female ratio from the list, you can follow the next quick easy steps with the help of the COUNTIFS function.

Step 01: Create new cells for ‘Total Active Male’, ‘ Total Active Female’ , ‘GCD’, and ‘Ratio’ at G7, G8, G9, and G10 respectively. Click on I7 and insert the following formula.