XLSTAT: Compare k Proportions in Excel

 

Background

A proportion is a way to measure on a [0, 1] scale, how many observations belong to a given category, compared with the total population size. It is computed as the ratio between the number of observations that belong to the category of interest and the total population size.

To compare k proportions, the statistical methods require that you know the population sizes. So the inputs can either be the proportions and the corresponding population sizes, or the number of observations that belong to the category of interest, and the corresponding population sizes.

 

Dataset for Example

The data correspond to 6 different series of screws used on cars. The number of screws that pass the quality tests correspond to “Success” and the other to “Failed”.

Our goal is to determine if the quality of the 6 series can be considered as different or not, and if there is a difference, we want to determine if there is one or more series responsible for the difference, in order to make the necessary adjustments for the next productions.

 

Setting Up

• Open XLSTAT.

• Click on the Parametric tests menu and select the k proportions test feature.

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

• In the Frequencies field, select the Success variable (column B).

• In the Sample sizes field, select the Total variable (column D), which represent the total number of screws produced in a given series.

• Select the Sample labels (column A).

• In Data format, select Frequencies.

• Activate all the tests.

• Enable the Column labels option as the first row of the selected data contains a column header.

 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first results that are being displayed correspond to the Chi-square test. The Chi-square test is used on contingency tables to test if columns and rows are independent. In this particular case where we are studying a binary event (success/failure), independence of rows and columns is equivalent to no difference between the proportions of successes across the series. The contingency table is automatically reconstructed by XLSTAT from the input data.

The Chi-square test concludes that there that there is at least one series that differs from the others. However, we notice that the p-value is very close to the significance level.

We know that the Chi-square test is an asymptotical test that should better not be used when too many cells of the contingency table have low values. As this is the case here, it is recommended to run the simulations-based test (Monte Carlo test). The principle is to generate many random contingency tables that have the same marginal sums and to compute the chi-square distances on these tables. Then, we determine how many tables give lower chi-square values so that we can see how “extreme” our table is.

We see that the Monte Carlo test with 5000 simulations gives almost the same result as the Chi-square test and confirms the fact that at least one series is different from the other.

In order to identify which series differs, we use the Marascuilo procedure. The results are displayed below.

We see that the two most different series are S2 and S3. We conclude that the important distance between these two series is responsible for the rejection of the H0 hypothesis of the k proportions test. The reasons why the S3 production had a better quality should be further investigated.

Related Article

Statistical Test Guide