XLSTAT: Probability Plots in Excel

 

Background

This article defines how to create and interpret a probability plot in Excel using the XLSTAT software.

Probability plots is an old method (Hazen, 1914), that has been extensively used, especially through the use of printed probability paper. It is useful to visually control whether a sample follows a given distribution.

 

Dataset for Example

The (attached) data represent the final math scores of 1000 students collected in different schools. We would like to know the percentage of students who obtained a score higher than 17. For this purpose, we will use the XLSTAT function Probability plot in order to a) evaluate the distribution of the sample and b) detect the desired percentile.

 

Setting Up

• Open XLSTAT.

• Click on the Visualizing data menu and select the Probability plots feature.

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

In the General  tab:

• Select the first column from the data sheet in the Data field.

• Select the Sample labels option to take into account the header of the column.

In the Options tab:

• Choose the Automatic distribution so that the distribution that fits best can be automatically chosen by XLSTAT.

Note: There are different methods for estimating the parameters of the distribution and the plotting positions. For more details, click on the Help button.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first result displayed is the fit summary. The distribution that fits best the data is the one with the highest p-value (=0,736) thus the Normal distribution. The p-value is greater than the significance level alpha=0,05 implies so one cannot reject the null hypothesis that the sample follows a Normal distribution

The next table contains the estimated parameters of the fitted normal distribution (mu = 11,8, variance = 1,9).

Based on the probability plot, we observe that the score 18 is at approximately the 99th percentile. Thus, we may suggest that 99% of the students obtained a score equal or less than 17, and only 1% of the students obtained a score higher than 17.