XLSTAT: Neural Networks in Excel

 

Background

Neural networks (NN) are powerful machine learning tools used in fields like pattern recognition, data mining, medical diagnosis, and fraud detection. They learn from large datasets to recognize patterns—such as converting speech into text in speech recognition tasks.

A neural network consists of interconnected nodes (neurons) arranged in layers: input, hidden, and output.

In XLSTAT-R, the Neural Network function uses the neuralnet function from the R package neuralnet (by Stefan Fritsch).

 

Dataset for Example

The attached data correspond to the  Boston dataset in the MASS package. It contains information on the housing values in the suburbs of Boston such as the per capita crime rate by town, the average number of rooms per dwelling and the median value of owner-occupied homes. It was originally published by Harrison, D. and Rubinfeld, D.L. `Hedonic prices and the demand for clean air', J. Environ. Economics & Management, vol.5, 81-102, 1978.

We will use the dataset to predict the median value of owner-occupied homes using all the other variables available. For the purpose of this tutorial, the initial data has been rescaled and randomly split it into a training and a test data set.

 

Setting Up

• Open XLSTAT.

• Click on the XLSTAT-R / neuralnet menu and select the Neural networks feature.

 

• The next dialog box opens.

In the General tab:

• Select the range N1:N381 in the Dependent variables field as well as the range A1:M381 in the  Explanatory Quantitative variables. The selected data corresponds to the train data.

 

In the Options tab:

• Enter 5,3 in the Neurons per layer field in order to define the number of neurons in the hidden layers.

• The algorithm RProp+ refers to the resilient backpropagation with weight backtracking.

 

In the Predictions tab:

• Select the range A383:M509 in the Explanatory Quantitative variables field. These observations come from the test data.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The fitted neural network is represented by the following graph:

The black lines show the linkages between each layer and the weights on each linkage while the blue lines show the bias term added in each step.The results displayed in the graph are also provided in the Weights table of the XLSTAT output.

The predictions made are displayed in the next table for the first few observations of the test data set :

To visualize the performance of the neural network, we can plot the predicted medv versus the actual medv (see below). We observe a strong correlation between the model’s predictions and the actual values. Thus we may suggest that the model predicts well.

Source: https://www.r-bloggers.com/fitting-a-neural-network-in-r-neuralnet-pac