It is not necessary to use trial and error. This solution has a total profit of 19000. With this formulation, it becomes easy to analyze any trial solution.įor example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. Total Profit equals the sumproduct of UnitProfit and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Insert the following three SUMPRODUCT functions.Įxplanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. To make the model easier to understand, create the following named ranges. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.Ģ. For example, each bicycle uses 300 units of capital and 0.5 unit of storage.Ĭ.
What are the constraints on these decisions? The constrains here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. What are the decisions to be made? For this problem, we need Excel to find out how much to order of each product (bicycles, mopeds and child seats).ī. To formulate this linear programming model, answer the following three questions.Ī. The model we are going to solve looks as follows in Excel.ġ. You can find the Solver on the Data tab, in the Analyze group. Under Add-ins, select Solver Add-in and click on the Go button.Ĥ. VbCrLf & "Try again with different parameters.2. Range("a10").Value = "Error of Approximation:" Range("a9").Value = "Approximate Root at:" Loop Until (Abs(error_val) <= precision Or iter = maxiter)
If deriv = 0 Then deriv = deriv + deriv_zero ' guard against division by zero below On Error GoTo err_handler: 'put this before the error can occur! 'or until maxiter iterations have occurred. 'Loop until error of approximation within precision MsgBox ("Number of iterations is not a positive integer" & vbCrLf & _ MsgBox ("At least one parameter is not a number" & vbCrLf & _ If Not (IsNumeric(b5) And IsNumeric(b6) And IsNumeric(b7)) Then 'This sub uses the method of Newton to find the root of f(x)ĭim x_n As Double, x_nplus1 As Double, error_val As Doubleĭim b7 As Double, b5 As Double, b6 As Integer 'Otherwise uncomment the last line and put the derivative in it.įdash = Evaluate(Replace(fdashx, "x", x)) 'Enter this in cell b4 if the derivative is a polynomial Public Function fdash(x As Double) As Double 'If equation is not a polynomial, enter it on the last line 'Change cell b3 if you want to use another polynomial 'Send bugs to Tom Function f(x As Double) As Double
I'd like to know how to extend this to systems of non-linear equations. It works in Excel 2010, but I haven't tried it in other versions. I can send anyone an Excel workbook as an example.
Note: If your equation is not a polynomial you should enter it and its derivative in the code for f and fdash. Create a button and assign the sub Newton to it. Hit Alt-F11 to go to the Visual Basic edit and paste the code below.ħ. the difference between successive approximations in cell b7)Ħ. Enter the maximum number of iterations for the procedure in cell b6.ĥ.
Create an Excel workbook with the equation/function in cell b3. The equation is defined in the public function f and its derivative in the public function fdash. Below is a sub that uses Newton's method to find the root of an equation in x.