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

Frequently, you might need to calculate 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.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Methods to Calculate the Male-Female Ratio in Excel

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.

male female ratio dataset


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

male female ratio using GCD function

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

 using GCD function

  • Step 04: Select the F5 cell and in the formula bar, inserts 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.

male female ratio using GCD function

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

male female ratio using GCD function


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,"#/####")

male female ratio using TEXT & Substitute function

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

TEXT & Substitute function

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

male female ratio using TEXT & Substitute function

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

male female ratio using TEXT & Substitute function

Read More: How to Convert Percentage to Ratio in Excel (4 Easy Ways)


Similar Readings


2. When the Male or Female Population % is Given

male female ratio dataset

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

 

male female ratio when male% given

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

when male% given

  • 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 GCD column and C5 and D5 denotes the Male and Female number respectively.

 when male% given

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

male female ratio when male% given

Read More: How to Calculate Ratio Percentage in Excel (4 Easy Methods)


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.

male female ratio using 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.
=COUNTIFS(D5:D14,"Active",E5:E14,"Male") 

Here, D5 and D14 is the first and last cell for Active column and E5 and E14 is the last cell for Gender Column

using COUNTIFS function

  • Step 02: Now, click on the cell I8 and type in the following formula:
=COUNTIFS(D5:D14,"Active",E5:E14,"Female")

male female ratio using COUNTIFS function

  • Step 03: Similarly, select cell I9 and insert the following formula in the formula bar.
=GCD(I7,I8)

Here, the cell, I9, gives us the GCD between total active male and total active female members.

male female ratio using COUNTIFS function

  • Step 04: Finally, click on I10 and put in the formula given below and you will have your male and female ratio.
=I7/I9&":"&I8/I9

using COUNTIFS function

Read More: How to Calculate Ratio Between Two Numbers in Excel (5 Ways)


Conclusion

So, I hope you found this article helpful. Moreover, please leave a comment below if you have any queries. Also, you can visit our website ExcelDemy for other articles.


Related Articles

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo