12,549,492 members (42,722 online)
alternative version

33.8K views
11 bookmarked
Posted

# Excel Solver and Linear Programming

, 20 Sep 2011 CPOL
 Rate this:
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:

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

## Share

 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.

http://santxiitr.wix.com/santosh

## You may also be interested in...

 Pro

 First Prev Next
 get max sum from serial of numbers(positive and negative) el lazar25-Dec-14 21:00 el lazar 25-Dec-14 21:00
 My vote of 5 Member 390001825-Apr-13 16:38 Member 3900018 25-Apr-13 16:38
 My vote of 4 Member 432084427-Sep-11 7:23 Member 4320844 27-Sep-11 7:23
 My vote of 5 Santx - Santosh20-Sep-11 7:49 Santx - Santosh 20-Sep-11 7:49
 Last Visit: 31-Dec-99 18:00     Last Update: 22-Oct-16 18:11 Refresh 1