Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Excel Solver and Linear Programming

, 20 Sep 2011
Rate this:
Please Sign up or sign in to vote.
A simple description of LP and instructions to use Excel Solver for solving problems

Introduction

This article explains the use of Excel Solver for solving problems, specially the ones related to Linear Programming. If you do not know much about Linear programming it's okay, this article will give you a little insight about that as well. However, the focus here is to make you aware of Excel Solver and let you know how to make use of it for solving interesting problems.

What is Solver and What Does It Do

In simple terms, Excel solver is an Add-in that solves problems for you. For a given problem, Excel solver can run various permutations and combinations and find out the best possible solution for you.

Enabling Solver in Excel

Go to options:

1.png

Select Add-In:

2.png

Click on 'Go' button, check Solver Add-in, click OK:

3.png

You should be able to see the Solver button in your Excel Data Ribbon:

4.png

What is Linear Programming

LP is a mathematical method for determining a way to achieve the best outcome (such as maximum profit or lowest cost) in a given mathematical model for some list of requirements represented as linear relationships.

Some Sample Problems that Solver can Solve

Problem 1

Let's say we have a function e.g. f=x2-x+2 and we want to find out the minimum value of x where -1<= x <= 5.

Problem 2

A company manufactures desks and chairs. Each desk uses 4 units of wood, and each chair 3 units of wood. A desk contributes $40 to profit and a chair contributes $25. Marketing restrictions require that the number of chairs produced to be at least twice the number of desk produced. There are 20 units of wood available. What set of production will bring maximum profit.

Problem 3

Here is a more complex problem which is represented in terms of equations and solved through the matrix approach.

Maximize 50x1 + 30x2 + 25x3 + 30x4

Where

2x1 + 2.5x2 + 3x3 + 1.8x4 = 800

1.2x1 + 1x2 + 2x3 + 0.8x4 = 400

1.5x1 + 1.2x2 + 1.5x3 + 0.8x4 = 380

x2 = 50

x3 = 30

x1, x2, x3, x4 = 0

Solving the Problems using Excel Solver

Problem 1

The following image shows the solved example. Cell in green (C15) is the cell which gets the value through solver and is named as x.

Formula for cell C12 is x^2-x+2. By default, put 1 in cell C15 which is the initial value for variable x.

5.png

Objective field is f which is the name of cell C12 which we want to solve through solver. "By changing the variable cells" field has x which is cell C15, our variable. We have also set the constraints using add button x <= 5 and x>=-1. Just click the 'solve' button and it will give the value of x.

6.png

Problem 2

This problem is solved in a traditional way. Unitstoproduce is the range C18:C19. totoalwoodused is the name of cell F20.

7.png

Here in the following figure, you can see the solver setting. Give 1 in green cells as initial values. Solver should be able to give you the correct values for them.

8.png

Problem 3

In real life, LP problems asre not as simple as in Problem 2. Therefore, we need to solve the problem using the matrix. In this problem, you can see many equations which you can relate to some equations in problem 2. To maximize the result of the equation, all we need to do is to Maximize (Transposed C) * x.

9.png

For the sake of uniqueness of the variable name, I named the matrix variable as xn, bn, etc. Values of x is what you want to get through solver. Give 1 as initial values for x1, x2, x3 and x4. When you run the solver, it will update those green cells with the solved values.

10.png

History

  • 20th September, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Santx - Santosh
Software Developer (Senior) ICF International
India India
I am just as good as I get; Otherwise, I am a Software Professional with years of Software Development Experience and my USP is that I believe in bestowing excellence in the software solutions that are in tune with the user requirements and deliver the excellent results to the clients. I present myself as an innovative professional who utilize the unique blend of technical expertise with business acumen.
 
Find more information about me here
http://santxiitr.wix.com/santosh
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 390001825-Apr-13 16:38 
GeneralMy vote of 4 PinmemberMember 432084427-Sep-11 7:23 
GeneralMy vote of 5 PinmemberSantx - Santosh20-Sep-11 7:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 20 Sep 2011
Article Copyright 2011 by Santx - Santosh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid