YouTubersLoveExcel#21: Solver for Linear Algebra

I need to solve a number of non linear equations using VBA (specifically this is for estimation of the equilibrium composition of an gas mixture using Gibbs free energy minimization which will have at least 10 non lin. equations to solve). The problem is that the code I have (from "EXCEL FOR SCIENTISTS & ENGINEERS by E.Joseph Billo) using gaussian jordan elimination/matrix pivoting, it produces a zero pivot & thus an error when the matrix elements are divided by the pivot.

Can anyone suggest how I augment the code to overcome this problem (division by zero pivot term) or has anyone else a better code for non linear equations solver. CODE BELOW

''''''''''''''''''''CODE'''''''''''''''''''''''''''''''
Option Explicit
Option Base 1

Const ConvergenceTolerance = 0.00000001
Const IncermentNumericalDifferentiation = 0.000000001
Const Iterations = 50
Const expon = 2.718281828



Function SimultEqNL(equations, Variables, constants)
' Newton iteration method to find roots of nonlinear simultaneous equations
' Example:
' w^3 + 2w^2 + 3w + 4 = +12.828
' w.x + x.y + y.z = -3.919
' w^2 + 2w.x + x^2 = +1
' w + x + y - z = -3.663
'
' WHE constants = [12.828,-3.919,1,-3.663];

'On Error Resume Next

Dim i As Integer, j As Integer, k As Integer, N As Integer
Dim Nlterations As Integer
Dim R As Integer, C As Integer
Dim VarAddr() As String, FormulaString() As String
Dim con() As Double, A() As Double, B() As Double
Dim V() As Double
Dim Y1 As Double, Y2 As Double
Dim tolerance As Double, incr As Double

N = equations.Rows.count
k = Variables.Rows.count

If k = 1 Then k = Variables.Columns.count
If k <> N Then SimultEqNL = CVErr(xlErrRef): Exit Function
' Use the CVErr function to create user-defined errors in user-created procedures.
' For example, if you create a function that accepts several arguments and normally returns a string,
' you can have your function evaluate the input arguments to ensure they are within acceptable range.
' If they are not, it is likely your function will not return what you expect.
' In this event, CVErr allows you to return an error number that tells you what action to take.


ReDim VarAddr(N), FormulaString(N), V(N), con(N)
ReDim A(N, N + 1), B(N, N + 1)

tolerance = ConvergenceTolerance 'Convergence criterion.
incr = IncermentNumericalDifferentiation 'Increment for numerical differentiation.
Nlterations = Iterations

For i = 1 To N
VarAddr(i) = Variables(i).Address
' i.e. VarAddr(1) = $A$11
Next i

' Initial values
For i = 1 To N
con(i) = constants(i).Value
' Put the initial guesses into vector V()
V(i) = Variables(i).Value: If V(i) = 0 Then V(i) = 1
Next i

For j = 1 To Nlterations
' Create N x N matrix of partial derivatives.
For R = 1 To N ' n = equations.Rows.count
For C = 1 To N
' Formulastring is formula in which all but one variable in each equation is replaced by current values.
FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)
' xlA1 = Use xlA1 to return an A1-style reference. xlR1C1 = Use xlR1C1 to return an R1C1-style reference
' xlAbsolute = Convert to absolute row and column style

' ConvertFormula method used to convert cell references from A1 reference style to R1C1 reference style
For i = 1 To N
' Debug.Print FormulaString(R)
If i <> C Then FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(i), V(i))
' Substitutes new_text for old_text in a text string.
' FormulaString(R) = the reference to a cell containing text for which you want to substitute characters.
' Replace the address reference with the actual variable value i.e. $B$5^3 + 2*$B$5^2 + 3*$B$5 + 4-$R$5+3-1^3
' i = C means its on the diagonal of the matrix
Next i
' V() = vector of current variable values
If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"
If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"

' Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) + incr)))
' ' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)
' Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) - incr)))
' A(R, C) = (Y2 - Y1) / (2 * incr)
Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))
' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)
Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))
A(R, C) = (Y2 - Y1) / (2 * incr * V(C))
'
' Derivatives i.e Taylor Series approx (numerical differentiation) central difference:
' y' = (y,i+1 - y,i-1) / 2h where y' = derivative of y, h = step size
' F(x+h) = F(x) + hF'(x) thus F'(x) = [ F(x+h)-F(x) ] / h
Next C
Next R

'Augment matrix of derivatives with vector of constants.
For R = 1 To N
FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)
For C = 1 To N
FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(C), V(C))
Next C
If IsError(Evaluate(FormulaString(R))) Then MsgBox "ERROR IN FORMULA FO Augment matrix of derivatives"
A(R, N + 1) = con(R) - Evaluate(FormulaString(R))
Next R

For i = 1 To N
If Abs((A(i, N + 1)) / V(i)) > tolerance Then GoTo Refine
Next i

SimultEqNL = Application.Transpose(V)
Exit Function

Refine: Call GaussJordan3(N, A, B)
'Update V values
For i = 1 To N
V(i) = V(i) + A(i, N + 1)
Next i
'Debug.Print j, "", V(1), V(2), V(3), V(4) ', V(5), V(6), V(7), V(8), V(9)
Next j
' Exit here if no convergence after a specified number of iteration
SimultEqNL = CVErr(xlErrNA)

End Function
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Sub GaussJordan3(N, AugMatrix, TempMatrix)
Dim i As Integer, j As Integer, k As Integer, L As Integer, LL As Integer, P As Integer, M As Integer, MM As Integer, MMM As Integer, MMMM As Integer
Dim pivot As Double, temp As Double, arr1() As Variant, arr2() As Variant, x As Integer, y As Integer
Dim determine As Integer, xx As Integer, yy As Integer, cntr As Integer, fudgefactor As Double
determine = 0
x = UBound(AugMatrix, 1): xx = UBound(TempMatrix, 1) ' =Number of ROWS
y = UBound(AugMatrix, 2): yy = UBound(TempMatrix, 2) ' =Number of COLUMNS' =Number of COLUMNS
' Debug.Print x, y, xx, yy
ReDim arr1(1 To 1, 1 To y): ReDim arr2(1 To 1, 1 To y)

For k = 1 To N
' Locate largest matrix element, use as pivot.
pivot = AugMatrix(k, k): P = k

For L = k + 1 To N ' loop each row

If Abs(AugMatrix(L, k)) < Abs(pivot) Then GoTo EndOfLoop

pivot = AugMatrix(L, k)

P = L
EndOfLoop: Next L ' next row
'Debug.Print pivot
' Swap rows
For j = 1 To N + 1
temp = AugMatrix(k, j)
AugMatrix(k, j) = AugMatrix(P, j)
AugMatrix(P, j) = temp
Next j

' Normalize pivot row
For j = 1 To (N + 1)
'If pivot = 0 Then MsgBox "PIVOT is ZERO"
If pivot = 0 Then Debug.Print "****",
TempMatrix(k, j) = AugMatrix(k, j) / pivot
Next j

' Do the Gauss elimination.
For i = 1 To N
If i = k Then GoTo EndOfLoop2
For j = 1 To N + 1
TempMatrix(i, j) = AugMatrix(i, j) - AugMatrix(i, k) * TempMatrix(k, j)
Next j
EndOfLoop2: Next i
For i = 1 To N
For j = 1 To N + 1
AugMatrix(i, j) = TempMatrix(i, j)
Next j
Next i
Debug.Print k, pivot
Next k
End Sub

Hi

I'm looking through this article from MS, about programming solver , instead of using the dialog box.

Anyway, one thing which occured to me is I'm unsure how I would toggle on non linear. As by default if you use the solver dialog box, go to options you will see default is linear.

I referred to this article.. but it makes no mention about this that i can see

http://support.microsoft.com/kb/843304


Hoping anyone an shed light of how i could do this?

cheers

Hi,

Please assist in a macro for the following problem,that includes XL solver functionality.
If an alternative solution can be calculated, that would be appreciated as well.
I would like a dynamic solution.

Any assistance would be appreciated.

Thank you


Sheet5

* G H I J K L M N O P Q R S 3 * A B C D * * * * * * * * 4 Number Solver calculates 2 2 3 3 * * * * * * * * 5 * * * * * * * * * * * * * 6 Maximum A, B, C, D can be 5 10 8 20 * * * * * * * * 7 * * * * * * * * * * * * * 8 * * * * * * * * * * * * * 9 Probability of Options occurring 14% 11% 25% 50% 100% * * * * * * * 10 Sales 3 4 5 6 * * * * * * * * 11 * * * * * * * * * * * * * 12 * * * * * * * * * * * * * 13 * * * * * * * * * * * * * 14 Subject to the following constraints * * * * * * * * * * * * 15 1 Because D has the highest probability (50%), it should have precedence over other options, when solver solves. * * * * * * * * * * * 16 2 Similiarly, then C must be calculated next, since that is more likely to occur, etc etc for the rest - with B been calculated last. * * * * * * * * * * * 17 3 If I change the probability of the options occuring, it must automatically start with the new precedence options. For example, if the likilihood of B increases to 60%, then solver must give that 1st preference followed by the next one on the precedence list 18 * * * * * * * * * * * * * 19 * * * * * * * * * * * * * 20 Objective Function * 50 = 50 * * * * * * * *
Spreadsheet Formulas Cell Formula L9 =SUM(H9:K9) I20 =SUMPRODUCT(H4:K4,H10:K10)

Excel tables to the web >> Excel Jeanie HTML 4

I am trying to determine the minimum costs. Below are my cells
A1 - Number of Widgets-A from Vendor X
B1 - Number of Widgets-B from Vendor X
C1 - Number of Widgets-A from Vendor Z
D1 - Number of Widgets-B from Vendor Z
E1 - Bundle of free Widgets - A from Vendor X
F1 - Bundle of free Widgets - A from Vendor Z

I then have costs below each of those variables, with a zero cost for the bundles. I am then doing a sum product of the two rows to determine a minimum cost

I need to build the constraints though, that says if 10,000 widgets A from Vendor A, and 10,000 Widget Bs from Vendor A, then I get free 5000 Widget A's. Does anyone know how to do this? I try to use a matrix below so that I can add 1's, negatives, or really other number that also sumproducts with row 1, and then an inequality to make sure requirement is met. I'd like to attach the file, but don't believe that's possible. Any help is appreciated.

A company produces three types of solvent A,B and C. The profits per thousand gallons for the three solvents are A $300, B $500 and C $800. The production process requires blending and purification. It also requires additional labor handling for finished products. The company has 260 hours of blending, 300 hours of purification and 160 hours of labor hours available. The requirements for each solvent a

Blending (hours/1000 gallons)
A = 2
B = 2
C = 3

Purification(hour/1000 gallons)
A = 1
B = 2
C = 4

Labor (hours/1000 gallons)
A = 1
B = 2
C = 2

Using Excel solver I need to formulate a linear program that can be used to determine the optimal product mix for the chemical company. Specifying the decision variables, the objective function and the relevant constraints. Also using solver I need to perform sensitivity analysis to determine how much should the manager be willing to pay for
1. An additional hour of blending
2. An additional hour of purification
3. An additional hour of labor

I came up with this but not sure if it right:

Decision variable:
X1 = units of CS-1 to be produced
X2 = units of CS-2 to be produced
X3 = units of CS-3 to be produced

Result variable: Total profit = Z

Objective is to maximize total profit: Z = 300X1 + 500X2 + 800X3

Uncontrollable variables:

Blending constraint: 2X1+ 2X2 + 3X3 ≤ 260

Purification constraint: 1X1 + 2X2 + 4X3 ≤ 300

Labor constraint: 1X1 + 2X2 + 2X3 ≤ 160


Please any help will be appreciate it
Thank you

Is there anyone who could help me with a linear programming problem that I am having. It's for an MBA class and I am so frustrated. Something is wrong and I don't know what. Can I email a spreadsheet to someone to help me figure this out? PLEASE?

Hi,
I am trying to use Excel solver for the stock market portfolio where I have past history of a trader's trades and can either take profit or hit a stop loss per trade.

What I'd like to do is analyse past trades where I have the following information.
- profit
- drawdown (lowest point the price got before profit was taken)

So the data looks like:
1: (5, -10)
2: (200, -30)
3: (-3, -15)

I would like to use the excel solver to run over the data that I have and to optimise a stop loss for me. Looking at the data above a stop loss that would have allowed -30 is optimal as that resulted in a $200 profit.

So the rules a
maximise the profit
constrained by the smallest drawdown (IF the drawdown is greater than x, the stop loss is hit) then use the stop loss else use the profit).

total profit =SUM_OF_ALL_TRADES( IF(drawdown < profitLoss THEN profitLoss ELSE profit) )

So in the data points above with a stop loss of -20 the calculation would be:
PROFT = 5 + (-20) + (-3)
5 because no stop loss hit so profit was selected
(-20) because the stop loss of -20 was hit as the price was on it's way to -30
(-3) because the stop loss was not hit so profit was selected

I am trying to get an excel solver running over this and am having issues with the IF statements that are required. I can use Excel 2010's Evolutionary solver but after playing with it a bit I don't like it that much and it spits out crazy scientific numbers.

I'd like to convert the if's into binary numbers but don't know how.
Does anyone have any suggestions on how to do this?

Much Appreciated

Hi i am having extreme problems with solver. It is for an assignment for uni and for the life of me i cant work out where i have gone wrong.

The whole point of the assignment is that the problems are liner.. therefore any answer without selecting the box "Assume linear model" is deemed not the best option (although it may be correct)

Although this problem is not hard compared to others, to explain it all here would be quite difficult for me. So i have included the Question sheet (It is question number 3) and i have also included the Worksheet that i have compiled.

Im not asking for the answers to this, i simply want to know why something is not LINEAR in my setup... or whats causing this..this has been 5 days of trying to work this out!!

Any help would be greatly appreciated.
Cheers
Andy

Hi,

I'm trying to solve a rather easy mixed integer linear program using the
Excel Solver and have an unusual constraint problem.

I have have 17 decision variables and I need to add the constraint where
each of the variables have a unique integer value. However, I cannot
seem to specify this using the Solver - my choices are limited to Int,
Bin, =, >= and <=.

I was wondering if anyone had any suggestions.

Thanks,
Schiz

I have a business related problem where I am trying to find the most optimal solution (minimum cost) given a set of constraints. I am wondering if the problem is too complex for Solver and if not what the basic equation would look like. I have been trying to figure the equation out for awhile but I can't seem to figure it out.


The problem is essentiall this: 500 Part #'s 3 Vendors (each with a different cost for each Part #) Last years sales data (unit volume), by part # Constraints: Vendor 1 = 40% of totalunit volume (or close to 40%) Vendor 2 = 40% of total unit volume (or colse to 40% Vendor 3 = 20% of total unit volume (or colse to 20%) What I need to do is determine which vendor to allocate each part number to (only 1 vendor per part number).

How do I go about expressing this problem in a way I can use Excel Solver?