15,507,266 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Article
Posted 20 Sep 2011

56.6K views
997 downloads
12 bookmarked

# Excel Solver and Linear Programming

Rate me:
4.93/5 (5 votes)
20 Sep 2011CPOL3 min read
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:

Select Add-In:

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

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

## 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.

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.

### Problem 2

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

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.

### 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.

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.

## 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)

Written By
Software Developer (Senior) ICF International
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

## Comments and Discussions

 First Prev Next
 get max sum from serial of numbers(positive and negative) user378247525-Dec-14 22:00 user3782475 25-Dec-14 22:00
 My vote of 5 Member 390001825-Apr-13 17:38 Member 3900018 25-Apr-13 17:38
 My vote of 4 Member 432084427-Sep-11 8:23 Member 4320844 27-Sep-11 8:23
 My vote of 5 Santx - Santosh20-Sep-11 8:49 Santx - Santosh 20-Sep-11 8:49
 Last Visit: 31-Dec-99 19:00     Last Update: 4-Dec-22 9:46 Refresh 1

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin

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