XLSTAT: CHAID classification tree in Excel

 

Dataset for Example

The attached data, from Fisher (1936), describe 150 Iris flowers with four variables (sepal length, sepal width, petal length, petal width) and their species (setosa, versicolor, virginica).

This example tests whether these variables can accurately predict species and identify classification rules based on them.

 

Setting Up

• Open XLSTAT.

• Click on the Machine Learning menu and select the Classification and regression trees feature.

In the General tab:

• Select Species as the Qualitative dependent variable.

• Select the four descriptive variables as the Quantitative Explanatory variables.

• Choose to use the CHAID algorithm to build the tree.

• Select the Variable labels to consider the variables names provided in the first row of the data set.

In the Options tab:

• Set the maximum tree depth to 3 to avoid obtaining a too complex tree. Several technical options allow to better control the way the tree is built.

• In Charts tab, select the Bar charts option to display the distribution of the species at each node.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

Summary statistics and the correlation matrix are displayed first, followed by the confusion matrix showing reclassification results.

The confusion matrix reveals a 98.67% accuracy rate, indicating the percentage of correctly classified observations.

 
We can note that here only 2 observations are misclassified.

The next result displayed is the node frequencies, that is, the distribution of species at each node of the tree and the related probabilities.

 
The tree structure details each node’s object count and percentage, test statistic, p-value for splitting, degrees of freedom, purity (% of dominant category), parent and child nodes, split variable with values or intervals, and predicted class.

 
The Rules table lists the algorithm’s rules in natural language. Each node shows the rule for the predicted class and the percentage of observations in that node belonging to the predicted category.

 
If PETAL WIDTH ≤ 4, then SPECIES = Setosa” applies to node 2, covering 45 flowers (32% of the dataset) with 100% purity, meaning all these observations are correctly classified. The next output shows part of the classification tree.

This diagram visualizes the CHAID algorithm’s step-by-step process in identifying variables that best split the dependent variable’s categories. Information at each node is explained below.

 

The algorithm stops when no more rules can be found or user-set limits are reached (e.g., minimum node size, max tree depth, p-value threshold).

XLSTAT also offers pie chart visualization for classification trees, which is easier to read with many nodes and categories. The inner circle shows category frequencies within the node; the outer ring shows the parent node’s category distribution.

 
The leaf-node rules enable prediction for each observation, with probabilities based on category distribution at the leaf. These results appear in the "Results by object" table.