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/E5Here, 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.
Read More:Â How to Calculate Average Ratio in Excel
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)/D5Here 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.
Read More:Â How to Calculate Ratio of 3 Numbers in Excel
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.
=COUNTIFS(D5:D14,"Active",E5:E14,"Male")Â Here, D5 and D14 is the first and last cell for the 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:
=COUNTIFS(D5:D14,"Active",E5:E14,"Female")
- Step 03: Similarly, select cell I9 and insert the following formula in the formula bar.
=GCD(I7,I8)Here, 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.
=I7/I9&":"&I8/I9
Download Practice Workbook
Conclusion
So, I hope you found this article helpful. Moreover, please leave a comment below if you have any queries.
