WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Solver in Excel

Solver in Excel

What is Solver in Excel?

Solver is an analysis tool in excel which can be enabled by the addins section of excel, solver tool is used to find out the best solution for problems which can linear or non linear, there are three methods to solve the data in the solver, solver is used to either maximize or minimize any output based on certain impacting variables.

The solver can solve

  • Optimization (maximize and minimize) problems like maximizing profit, minimizing the cost.
  • Arithmetic equations.
  • Linear and non-linear problems.

How to ADD Solver Add-In in Excel?

The solver is the Add-In in the Excel, which is not by default found in the tabs on the ribbon. To activate this command, we have two ways:

You can download this Solver Excel Template here – Solver Excel Template
  • First click on File Menu

Solver in Excel - step 1

  • Select Options

Solver in Excel - step 2

  • Click on Add-ins, then Select Solver Add-in and Click on Go

Solver in Excel - step 3

  • Tick for ‘Solver Add-in’ and click on OK.

Solver in Excel - step 4

  • On Developers tab->In ‘Add-ins’ group, click on ‘Excel Add-ins’->Tick on ‘Solver Add-in’->Click on ‘OK’.

Solver in Excel - step 5

After you install the ‘Solver Add-in,’ we will be finding the ‘Solver’ command on the ‘Data‘ tab in the ‘Analyze’ group.

Solver in Excel - step 6

In the ‘Solver Parameters’ Dialog box, this is the terminology used:

  • Objective Cell: It is the cell where the formula is placed based on the decision and constrained cells, and we want this value to be minimized, maximized, or equal to the value as specified. The objective cell must be a single cell and must contain a formula.
  • Constraints Cells: These are the cells for the factors, which are the limiting factors to solve the problem.
  • Decision Variable Cells: These are the cells where we want the required values to be put by the solver to get our objective cell maximized, minimized, or equal to the specified value as required.

Solver Model: Solver Model consists of constraint and decision variable cells, and the formulas interrelating these all.

Solver in Excel - step 7

We have conditions in our life where we want to maximize the value considering the limits of various factors like. In business, we want to maximize the profits with our limited available stock, manpower, and tools, and sometimes we need to minimize the value (i.e., cost) for getting the required output.

We have some problems in our day-to-day life where we can formulate arithmetic equations, and we want to find the value of the decision variables.  Then we can use the ‘Solver.’ In Excel, we can find out the value of up to 200 variables using the solver.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Various Type of Solving Methods

#1 – GRG Nonlinear

Solver in Excel - step 8

It stands for Generalized Reduced Gradient Nonlinear. It is the best method when we need to solve a nonlinear problem. A nonlinear problem may have more than one feasible region or set of similar values for the decision variables, where all of the constraints are satisfied.

#2 – Simplex LP

Solver in Excel - step 9

LP here stands for linear problems. This method is faster than the GRG Nonlinear method and is used for linear problems.

#3 – Evolutionary

Solver in Excel - step10

We use this method for problems that are non-smooth while we use the above two methods for smooth problems.

How to Use Solver in Excel? (with Examples)

The solver is a Microsoft Excel add-in program that we can use for what-if analysis in excel. Solver finds an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells, which are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Below are some of the examples to understand this tool in a better manner.

You can download this Solver Excel Template here – Solver Excel Template

Example #1

Suppose we have an arithmetic equation to solve. The equation is

56=2X-5+Y2

We want to find the value of X and Y in the equation.

First, we need to formulate the same on the excel sheet as follows and then use the ‘Solver’ command.

Solver in Excel - step11

Solver in Excel - step12

step13

step14

Example #2

In this example, we want to maximize the profit by using the limited row material (Chassis, Picture Tube, Speaker Cone, Power Supply, Electronics) for making various products (TV Set, Stereo, Speaker) which yield different profits. We want the total profit to be maximized.

step15

step16

Clicking on the ‘Solver’ command in the ‘Analyze’ group placed on the ‘Data’ tab. We get the ‘Solver Parameters’ dialogue box, and we specify the reference of Objective Cell to C18, Decision Variable Cells to C6:E6, and two constraints conditions that ‘Inventory left’ (G10:G14) must be greater than or equal to 0 and Quantity of Various products, i.e., TV Set, Stereo, Speaker (C6:E6) (Decision Variable Cells) must be greater than or equal to 0.

step17

step18

We found the solution that we must produce 250 TV Sets, 50 Stereo, and 50 Speakers. Then we can get a Profit of Rs. 28000 and can use our resources efficiently.

step19

Recommended Articles

This has been a step by step guide to Solver in Excel. Here we discuss how to activate Solver Add-In in Excel and use it to solve problems in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Refresh Pivot Table
  • Sum by Color in Excel
  • Delete Pivot Table Excel
  • Equations in Excel
9 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Solver Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More