Click here to Skip to main content
15,501,036 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Posted 20 Sep 2011

Tagged as


12 bookmarked

Excel Solver and Linear Programming

Rate me:
Please Sign up or sign in to vote.
4.93/5 (5 votes)
20 Sep 2011CPOL3 min read
A simple description of LP and instructions to use Excel Solver for solving problems


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


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.



  • 20th September, 2011: Initial post


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

Comments and Discussions

Questionget max sum from serial of numbers(positive and negative) Pin
user378247525-Dec-14 22:00
Memberuser378247525-Dec-14 22:00 
GeneralMy vote of 5 Pin
Member 390001825-Apr-13 17:38
MemberMember 390001825-Apr-13 17:38 
GeneralMy vote of 4 Pin
Member 432084427-Sep-11 8:23
MemberMember 432084427-Sep-11 8:23 
GeneralMy vote of 5 Pin
Santx - Santosh20-Sep-11 8:49
MemberSantx - Santosh20-Sep-11 8:49 

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

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