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.

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.

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, inserts the following formula:

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.

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

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.

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

Similar Readings

• How to Calculate Sharpe Ratio in Excel (2 Common Cases)
• Debt Service Coverage Ratio Formula in Excel
• Calculate Sortino Ratio in Excel (2 Methods)
• How to Calculate Odds Ratio in Excel
• Convert Ratio to Decimal in Excel (3 Handy Methods)

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.

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

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

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

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.

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

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

• Step 02: Now, click on the cell I8 and type in the following formula:

• Step 03: Similarly, select cell I9 and insert the following formula in the formula bar.

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

• Step 04: Finally, click on I10 and put in the formula given below and you will have your male and female ratio.

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

• How to Calculate Current Ratio in Excel (2 Suitable Examples)
• Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
• How to Calculate Compa Ratio in Excel (3 Suitable Examples)
• Calculate Average Ratio in Excel (2 Simple Ways)
• Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
• How to Graph Ratios in Excel (2 Quick Methods)