XLSTAT: Compare two proportions in Excel

 

Dataset for Example

We are going to compare the proportion of male and female that are public relation managers per earning category in the US (Source: US Census Bureau, Census 2000 special tabulation).

As can be seen in the dataset, the distribution is very different between male and female.

The earning strata that seems to be the most similar between gender is a yearly salary of $50,000 to $74,999: 29.32% of the male and 25.00% of the female PR manager are earning this salary.

We are now going to test the following null hypothesis H0: There is no gender difference in the proportion of people earning $50,000 to $74,999. The alternative hypothesis will be Ha: There is a bigger proportion of male managers earning $50,000 to $74,999 than female managers.

 

Setting Up

• Open XLSTAT.

• Click on the Parametric tests menu and select the Tests for two proportions feature:

• Once you've clicked on the button, the dialog box appears.

In the General tab:

• Enter the folowing numbers:

  • Frequency 1: 8990 (Female PR managers earning $50,000 to $74,999)

  • Sample size 1: 35955 (Total Female PR managers)

  • Frequency 2: 6619 (Male PR managers earning $50,000 to $74,999)

  • Sample size 2: 27340 (Total Male PR managers)

• Select Frequencies in the Data format field.

• Select a z test rather than a Monte Carlo method as we do not need to use Monte Carlo simulation.

In the Options tab:

• For the Alternative hypothesis set up the option Proportion 1 –Proportion 2 < D where D is 0, as the proportion of female PR manager earning $50,000 to $74,999 is inferior to the same proportion for male.

• Keep the significance level to 5%.

• There are two different formula to compute the variance we will use the first one.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

In the result sheet look at the value of the p-value. It is inferior to the limit of 0.05 therefore we should reject the hypothesis and conclude that the proportion of female PR manager earning $50,000 to $74,999 is significantly less than the proportion of male PR manager.

 

Related Article

Not sure this is the statistical test you are looking for? Check out this guide.