Use Excel Solver to solve non-linear problem – solution of general neural network
Excel is a powerful application. With additional Add-ons, it can be used to solve very complicated problem. Recently, one of my friends ask me how to solve neural network problem in Excel. Sure it can do the job. I will start with solving a very simple non-linear problem by using Excel Solver and extend the method to solve neural network problem.
There are a number of ways to solve non-linear problem, such as NLIN in SAS, nmle in R, Solver in Excel, Optimization package in MATLAB, and self-developed program adn so on. In Excel, it is Solver, an Excel Add-on, that can help to solve the problem. Excel does not have Solver installed at default installation. If the Excel on your machine does not have Solver add-on, you can use Tools->Add-Ins (Figure 1) to activate this component. Once it is activated, you can find a new menu item called Solver shows up in Tools menu. Click that, Solver dialog window will appear. You can find details about how to use Excel Solver on the Internet.
Figure 1. Excel Add-ins dialog
Now we have the tool to work with. First of all, we can start with seeking solution of a very simple non-linear problem, a system of polunomial equations. Suppose we have the following 3 order polynomial equation:
y = a*x^3 + b * x^2 + c
Enter several pairs of x,y values into Columns A and B, and enter formulas to Columns C, D, and E like the following.
x y predicted Diff Diff^2 1 3 =a_1*A2^3+b_1*A2^2+c_1 =c2-b2 =d2^2 2 38 =a_1*A1^3+b_1*A1^2+c_1 =c3-b3 =d3^2 23 1882 =a_1*A1^3+b_1*A1^2+c_1 =c4-b4 =d4^2
Enter string values to A6-A9, such ad “A_1″, “B_1″, “C_1″ snf “SSE”, and 1 to B6-B8, formula (=sum(E2:E4)) to B9. You can download the sample Excel sheet polyeqsys. Now select A6:B9 as a range, and click Insert -> Name -> Create to create named variables. Click “Ok” button when the “Create Name” dialog shows up. Then you can select B9 and click Tools -> Solver to open the Solver dialog window. The Set target cell box is filled with $B$9 automatically. This is what we want. Select “Min” in “Equal to” row. Then click By changing cells box and select B6-B8. The Solver dialog window looks like Figure 2. Next step is to click “Solve” button on the right top corner. After a few seconds, you will get solution in B6-B8. You can notice the value in B9 become a small number.
Figure 2. Excel Solver dialog window
Now you learn how to use Excel Solver to solve simple non-linear problem. The strategy we use in the simple problem can be applied to solve neural network problem directly. The most important thing to achieve our goal is to design the neural network instead of training it. The following provides a simple feedforward neural network to mimic the output of a sine function.
In Excel spreadsheet, we use sine function to generate a series of value pairs. The feedforward neural network includes three layers: input, hidden, and output. Input layer only include input values of the sine function. The hidden layer includes 5 neurons and each of the neurons includes a transigmoid transforming function. The output layer includes one neuron which takes the outputs of the five neurons in the hidden layer and transforms them through a linear function. Linear function is used as the pure input functions in the hidden and output layers. Figure 3 shows the general layout of the feedforward neural network.
Figure 3. The topology of a simple feedforward neural network model
All formulas should be entered into Excel spreadsheet just like what we do in the first example. Once we complete this step, just setup the Excel Solver and run it to train the neural network. All parameters used in the neural network can be obtained once the training process is completed. Details about how to implement the neural network model in Excel and the trained network parameters can be found in the Excel document – nnexample.
I hope you enjoying the reading and learning. Please post your comments if you have questions, new ideas, and suggestions.
Do not forget to read the related posts:
- Simple way to utilize Excel Solver
- Use VBA script to control Solver
- Solving nonlinear problem in Excel: Using downhill simplex method
Does anyone know where is a good place to learn how to make paper mache scenery for model train layouts?
Very cute
))),