XLSTAT: Quantiles or percentiles computation in Excel

 

Background

XLSTAT offers a comprehensive tool for computing quantiles or percentiles, along with their confidence intervals and visualizations.

Quantiles are straightforward statistical measures:

• The 0.5-quantile (or median) splits the data in half.

• A percentile is a quantile scaled from 0 to 100.

• For example, the 0.95-quantile (or 95th percentile) means 95% of the data falls below that value.

 

Dataset for Example

The attached dataset has been obtained from [Lewis T. and Taylor L.R. (1967). Introduction to Experimental Ecology, New York: Academic Press, Inc]. It concerns 237 children, described by their gender and height in centimeters (1 cm = 0.4 inch).

 

Setting Up

• Open XLSTAT.

• Click on the Describing data menu and select the Quantile estimation feature.

• Once you've clicked on the button, the Quantile  dialog box will appear. 

In the General tab:

• Select Height in the Data field. Data should be quantitative.

• As the column header was selected for the variables, the Variable labels option needs to be activated.

• Select the default method for estimation ( weighted mean at x(Np) ) and both type of  confidence intervals with a 95 % confidence.

In the Charts tab:

• Select all charts and we are interested by the 67-percentile (two third of the children are smaller and one third is taller).

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first table displays some descriptive statistics on the height variable.

The second table displays quantiles and their associated confidence interval for different commonly used values. For example, the median is 159.9 cm. The 95-percentile shows that 95 % of the children are smaller than 174.98 cm.

The value of the 67-percentile is then displayed. Two third of the children are smaller than 164.42 cm

The first chart (see below) allows us to visualize the empirical cumulative distribution function with the value of the 67-percentile.

The second and third charts are a box plot and the scattergram. The 67-percentile is displayed using a blue line.

  

You can also use sub-samples, for example gender can be used as a group variable. Weights associated to the observations can also be included.