Posts Tagged Solver

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.

add-ins
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.
solvershot
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.

nn
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:

Share

Tags: , , , , , ,

Use VBA script to control Solver

We usually use Excel to store a lot of data. To process these data, especially
fit nonlinear model, is complicated. Excel provides a optimization tool called
Solver that can be utilized to solve nonlinear problem numerically. In the past,
we have experiences to solve neural network in Excel. If you run the Excel Solver
from the Tools menu, you can only solve one problem at a time. Sometimes we have
a lot of data grouped into many groups. We need solve a nonlinear equation for
each group separately. You can manully do it by clicking the Excel menu items. It
will be a tedious and error-prone process. Is it possible to use VBA subroutine to
call the solver automatically and loop through a given data set? Answer is yes.
I provide a simple VBA script code here. The sample spreadsheet is a right start
point if you’d like to apply this VBA script to your problems.

' Source code 
' This macro is used to optimize nonlinear euqations in Excel
' It call Excel Solver to do the optimization
' In order to use the solver, you have to add the Excel Solver to 
' the VBA project that will use this macro
'
' Write by Zhanshan Dong, Sept 2007
'
' Data should be in current active sheet
' You have to organize your data in the format similar to the sample sheet
' Please download the sample sheet and modify it to adapt to your particular 
' situation
'
' In this example, the problem we try to solve has 4 parameters
' Data 
'
Dim initParams(4) As Double
Dim maxA As Double
Dim optimParams(5) As Double
Dim mySheet As Worksheet

Sub optim()
    Set mySheet = ActiveSheet
    For j = 5 To 16 Step 4
        mySheet.Cells(7, 20).Value = j
        maxA = WorksheetFunction.Max(mySheet.Range("t8:ab11"))
        initParams(1) = 65
        initParams(2) = 35
        OneRun
        myrow = j
        mySheet.Cells(myrow, 13).Value = optimParams(1)
        mySheet.Cells(myrow, 14).Value = optimParams(2)
        mySheet.Cells(myrow, 15).Value = optimParams(3)
        mySheet.Cells(myrow, 16).Value = optimParams(4)
        mySheet.Cells(myrow, 17).Value = optimParams(5)
    Next j
End Sub

Private Sub OneRun()
    Dim myinit(4)
    Randomize (Timer)
    optimParams(5) = -1
    For r = 1 To 5
        myinit(1) = initParams(1) + 10 * Rnd() - 5
        myinit(2) = initParams(2) + Rnd() * 10 - 5
        For i = 6 To 13
            mySheet.Cells(5, 19).Value = myinit(1)
            mySheet.Cells(5, 20).Value = myinit(2)
            mySheet.Cells(5, 21).Value = i
            mySheet.Cells(5, 22).Value = maxA
    
            ' Solve the nonlinear equation
            
            solverReset
            SolverOk SetCell:="$W$5", MaxMinVal:=2, ValueOf:="0", ByChange:="$S$5:$V$5"
            SolverAdd CellRef:="$T$5", Relation:=3, FormulaText:="20"
            SolverAdd CellRef:="$S$5", Relation:=1, FormulaText:="80"
            SolverAdd CellRef:="$V$5", Relation:=1, FormulaText:=CStr(maxA)
            SolverSolve UserFinish:=True
            SolverFinish KeepFinal:=1
    
            If optimParams(5) = -1 Then
                   optimParams(1) = mySheet.Cells(5, 19).Value
                   optimParams(2) = mySheet.Cells(5, 20).Value
                   optimParams(3) = mySheet.Cells(5, 21).Value
                   optimParams(4) = mySheet.Cells(5, 22).Value
                   optimParams(5) = mySheet.Cells(5, 23).Value
            ElseIf optimParams(5) > mySheet.Cells(5, 23).Value Then
                   optimParams(1) = mySheet.Cells(5, 19).Value
                   optimParams(2) = mySheet.Cells(5, 20).Value
                   optimParams(3) = mySheet.Cells(5, 21).Value
                   optimParams(4) = mySheet.Cells(5, 22).Value
                   optimParams(5) = mySheet.Cells(5, 23).Value
            End If
        Next i
    Next r
End Sub
Share

Tags: , ,

Simple way to utilize Excel Solver

Excel Solver is a powerful tool to solve any nonlinear problem in Excel. Excel provide a interface to allow use to select options and fill target and parameters. If you only work on one set of data and explore multiple non-linear function, it is fine to use the Excel Solver interface. More often you are in a situation that you need fit a same equation for multiple data set. Assume one row represents one data set and you have 1000 rows. How can you use Excel Solver to fit all 1000 rows one by one. It will be pretty tough to use Excel Solver interface to do that.
There solution is simple because you can use VBA code to call and control Excel Solver. The following is a very simple example to demonstrate how we can use the Excel Solver.

1: For I = 2 To 1001
2:    TARGET = "$DU$" & I
3:    PARAM = "$DR$" & I & ":$DT$" & I
4:    SolverOk SetCell:=TARGET, MaxMinVal:=2, ValueOf:="0", ByChange:=PARAM
5:    SolverSolve USERFINISH:=True
6:    SolverFinish KEEPFINAL:=1
7: Next

Line 1: provide the first and last row information in the for-loop.
Line 2: provide a cell that holds target, in this situation, it is SSE in DU2…
Line 3: provide a range reference to model parameters
Line 4: Set solver parameters and options
Line 5: Set USERFINISH to true allow program run multiple dataset automatically
Line 6: Set KEEPFINAL to 1 allow write the solution to spreadsheet
Line 7: Close the loop.
When you use this code clip, please change the target and param cell or range references.

Share

Tags: , , , ,