XLSTAT: Discretizing a Continuous Variable in Excel

 

Dataset for Example

The (attached) data used in this example corresponds to a list of patients with information relating to their heights, masses and BMI.

Patients are devided into different groups according to their BMI.

 

Background

There are several options with which to group the samples:

• Constant range: Choose this method to create classes that have the same range. Then enter the value of the range. You can optionally specify the "minimum" that corresponds to the lower bound of the first interval if needed.

• Intervals: Use this method to create a given number of intervals with the same range.

• Equal frequencies: Choose this method so that all the classes contains as many of the same number of observations as possible.

• Automatic (Fisher): Use this method to create the classes using Fisher’s algorithm.

• Automatic (k-means): Choose this method to create classes (or intervals) using the k-means algorithm.

• Intervals (user defined): Choose this option to select a column containing the lower bound of the first interval, and the upper bound of all the intervals in increasing order.

• 80-20: Use this method to create two classes, the first containing the first 80% of the series, the second containing the remaining 20% with the data sorted in increasing order.

• 20-80: Use this method to create two classes, the first containing the first 20 % of the series, the second containing the remaining 80% with the data sorted in increasing order.

• 80-15-5 (ABC): Use this method to create two classes, the first containing the 80 first % of the series, the second containing the next 15%, and the third containing the remaining 5% with the data sorted in increasing order. This method is sometimes referred to as "ABC classification".

• 5-15-80: Use this method to create two classes, the first containing the first 5 % of the series, the second containing the next 15%, and the third containing the remaining 80% with the data sorted in increasing order.

 

Setting Up

• Open XLSTAT.

• Click on the Preparing data menu and select the Discretization feature.

In the General tab:

• Select the data on the Excel sheet corresponding to the BMI (column D).

• Tick Column labels for the variable name "BMI".

• Tick Row labels and select the name of the patients (column A).

• Choose the Method (see the Backgroud section above).

• Select the Intervals (user defined) option and use the bounds defined for XLSTAT in the Excel sheet.

In the Outputs tab:

• Select which results to calculate.

• Select Centroids, Results by class and  Results by object.

In the Charts tab:

• Select  histograms as bars with frequency.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The results are displayed on the new sheet named "Discretization".

The first result is the histogram.

Notice that the size of the bar corresponding to the size of the class.

Next is the Table of the Descriptive Statistics for the intervals.

Most of the patients (10) are in the third class which correspond to a normal weight.

Then the class centroids are displayed.  The values may or may not match the center of each class.

Finally you have the dispersion of the patients into their BMI class.