How to use Solver in Excel with examples

the tutorial explains how to add and the place to find Solver in distinctive Excel types, from 2016 to 2003. Step-via-step examples display how to use Excel Solver to discover ideal solutions for linear programming and different kinds of issues.

every person knows that Microsoft Excel includes lots of beneficial functions and powerful equipment that may save you hours of calculations. but did you know that it also has a tool that can support you discover most effective options for determination issues?

during this tutorial, we are going to cover all standard aspects of the Excel Solver add-in and supply a step-with the aid of-step guide on a way to use it most without problems.

what is Excel Solver?

Excel Solver belongs to a unique set of commands often called What-if analysis equipment. it's primarily purposed for simulation and optimization of numerous business and engineering fashions.

The Excel Solver add-in is principally beneficial for solving linear programming problems, aka linear optimization complications, and therefore is now and again known as a linear programming solver. apart from that, it might probably handle clean nonlinear and non-easy issues. Please see Excel Solver algorithms for more particulars.

whereas Excel Solver can't crack each possible difficulty, it is a good suggestion when coping with every kind of optimization complications the place you need to make the most excellent determination. as an example, it may possibly support you maximize the return of funding, opt for the top of the line finances for your promoting campaign, make the optimum work agenda for your personnel, lower the birth prices, and the like.

how to add Solver to Excel

The Solver add-in is protected with all versions of Microsoft Excel beginning with 2003, but it surely isn't enabled by using default.

to add Solver to your Excel, function here steps:

In Excel 2010, Excel 2013, and Excel 2016, click File > options.In Excel 2007, click the Microsoft office button, after which click on Excel alternatives.

in the Excel options dialog, click on Add-Ins on the left sidebar, make sure Excel Add-ins is chosen in the control container at the bottom of the window, and click on Go.

within the Add-Ins dialog field, investigate the Solver Add-in box, and click on adequate:

To get Solver on Excel 2003, go to the tools menu, and click Add-Ins. in the Add-Ins obtainable checklist, investigate the Solver Add-in container, and click on good enough.

observe. If Excel shows a message that the Solver Add-in is not currently installed in your desktop, click yes to installation it.

where is Solver in Excel 2016, 2013, 2010 or 2007?

in the up to date models of Excel, the Solver button seems on the facts tab, in the evaluation group:

the place is Solver in Excel 2003?

After the Solver Add-in is loaded to Excel 2003, its command is brought to the tools menu:

Now that you understand the place to locate Solver in Excel, open a new worksheet and let's get began!

be aware. The examples discussed in this tutorial use Solver in Excel 2013. when you've got an additional Excel version, the screenshots may not fit your edition exactly, although the Solver functionality is basically the same.

how to use Solver in Excel

before working the Excel Solver add-in, formulate the mannequin you wish to remedy in a worksheet. in this illustration, let's locate an answer for right here standard optimization issue.

issue. Supposing, you are the owner of a elegance salon and also you are planning on featuring a new carrier to your purchasers. For this, you need to purchase a new gadget that costs $40,000, which may still be paid by way of instalments within 12 months.

goal: Calculate the minimal charge per provider on the way to will let you pay for the new machine in the distinctive timeframe.

For this task, I've created the following model:

And now, let's examine how Excel Solver can locate an answer for this difficulty.

1. Run Excel Solver

On the records tab, in the evaluation group, click the Solver button.

2. define the issue

The Solver Parameters window will open the place you ought to installation the three simple components:

purpose mobilephone

Variable cells

Constraints

exactly what does Excel Solver do with the above parameters? It finds the most reliable price (highest, minimal or exact) for the formulation in the objective phone by way of changing the values within the Variable cells, and field to limitations within the Constraints cells.

goal

The aim cellphone (target mobile in earlier Excel types) is the mobile containing a formulation that represents the purpose, or intention, of the problem. The objective will also be to maximise, minimize, or obtain some target value.

during this instance, the goal cell is B7, which calculates the price time period using the method =B3/(B4*B5) and the outcome of the components should still be equal to 12:

Variable cells

Variable cells (changing cells or Adjustable cells in past models) are cells that include variable records that will also be changed to achieve the purpose. Excel Solver allows specifying as much as 200 variable cells.

during this illustration, we now have a couple of cells whose values can also be changed:

Projected shoppers per month (B4) that should still be less than or equal to 50; and

can charge per carrier (B5) that we desire Excel Solver to calculate.

Tip. If the variable cells or levels on your mannequin are non-adjacent, opt for the first telephone or latitude, and then press and dangle the Ctrl key whereas making a choice on other cells and/or tiers. Or, type the levels manually, separated with commas.

Constraints

The Excel Solver Constrains are restrictions or limits of the viable options to the problem. to position it otherwise, constraints are the conditions that ought to be met.

so as to add a constraint(s), do here:

click on the Add button correct to the "field to the Constraints" container.

in the Constraint window, enter a constraint.

click the Add button so as to add the constraint to the list.

proceed entering different constraints.

upon getting entered the ultimate constraint, click on ok to return to the main Solver Parameters window.

Excel Solver allows for specifying right here relationships between the referenced phone and the constraint.

below or equal to, equal to, and improved than or equal to. you put these relationships by means of picking a mobile within the mobilephone Reference container, choosing considered one of right here signals: <=, =, or >=, and then typing a number, cellphone reference / mobilephone name, or formula within the Constraint field (please see the above screenshot).

Integer. If the referenced mobilephone should be an integer, opt for int, and the observe integer will seem in the Constraint container.

distinctive values. If every phone in the referenced latitude need to include a unique price, choose dif, and the notice AllDifferent will appear within the Constraint field.

Binary. if you need to limit a referenced telephone either to 0 or 1, opt for bin, and the note binary will seem within the Constraint box.

notice. The int, bin, and dif relationships can most effective be used for constraints on Variable cells.

To edit or delete an current constraint do the following:

in the Solver Parameters dialog box, click on the constraint.

To alter the chosen constraint, click alternate and make the adjustments you need.

To delete the constraint, click on the Delete button.

in this illustration, the constraints are:

B3=40000 - cost of the brand new equipment is $forty,000.

B4<=50 - the variety of projected patients per thirty days in under 50.

three. resolve the difficulty

After you will have configured the entire parameters, click on the remedy button on the backside of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in discover the finest solution to your issue.

reckoning on the mannequin complexity, laptop reminiscence and processor pace, it may possibly take a number of seconds, a couple of minutes, or even just a few hours.

When Solver has finished processing, it'll display the Solver results dialog window, where you opt for maintain the Solver solution and click on ok:

The Solver outcome window will shut and the answer will seem on the worksheet appropriate away.

during this example, $66.67 seems in cellphone B5, which is the minimal cost per carrier in an effort to help you pay for the new device in one year, provided there are at least 50 purchasers per 30 days:

information:

If the Excel Solver has been processing a certain difficulty for too long, which you could interrupt the procedure through urgent the Esc key. Excel will recalculate the worksheet with the last values found for the Variable cells.

To get greater particulars concerning the solved difficulty, click on a record type in the reviews box, and then click adequate. The record can be created on a brand new worksheet:

Now that you've acquired the fundamental concept of the way to use Solver in Excel, let's have a closer analyze a pair more examples that might aid you gain more figuring out.

Excel Solver examples

under you will locate two more examples of the use of the Excel Solver addin. First, we are able to locate a solution for a familiar puzzle, after which remedy a true-lifestyles linear programming difficulty.

Excel Solver instance 1 (magic rectangular)

I agree with each person is common with "magic square" puzzles the place you need to put a collection of numbers in a rectangular in order that all rows, columns and diagonals add up to a undeniable quantity.

for example, were you aware an answer for the 3x3 rectangular containing numbers from 1 to 9 the place each row, column and diagonal provides up to 15?

or not it's likely no huge deal to clear up this puzzle via trial and error, however I guess the Solver will discover the solution quicker. Our part of the job is to effectively define the problem.

To begin with, enter the numbers from 1 to 9 in a desk which include three rows and three columns. The Excel Solver doesn't actually need those numbers, however they'll help us visualize the problem. What the Excel Solver add-in in reality needs are the SUM formulas that total every row, column and a pair of diagonals:

With all the formulation are in location, run Solver and deploy right here parameters:

Set purpose. in this illustration, we don't should set any aim, so depart this box empty.

Variable Cells. We are looking to populate numbers in cells B2 to D4, so opt for the range B2:D4.

Constraints. here conditions should still be met:

$B$2:$D$four = AllDifferent - the entire Variable cells may still include distinct values.

$B$2:$D$4 = integer - the entire Variable cells may still be integers.

$B$5:$D$5 = 15 - the sum of values in every column should still equal 15.

$E$2:$E$four = 15 - the sum of values in every row should equal 15.

$B$7:$B$eight = 15 - the sum of both diagonals may still equal 15.

finally, click on the remedy button, and the solution is there!

Excel Solver illustration 2 (linear programming difficulty)

here is an example of an easy transportation optimization problem with a linear aim.  more complex optimization fashions of this form are used via many companies to save hundreds of bucks each and every yr.

difficulty: You wish to minimize the can charge of delivery goods from 2 diverse warehouses to four distinctive shoppers. each and every warehouse has a constrained give and every client has a undeniable demand.

aim: reduce the total transport can charge, no longer exceeding the quantity attainable at every warehouse, and meeting the demand of each client.

source data

right here is how our transportation optimization difficulty feels like:

Formulating the mannequin

To outline our linear programming difficulty for the Excel Solver, let's reply the 3 leading questions:

What are the choices to be made? We are looking to calculate the most reliable amount of items to convey to every consumer from each warehouse. These are Variable cells (B7:E8).

What are the constraints? The components available at each warehouse (I7:I8) can not be handed, and the quantity ordered by using each customer (B10:E10) should be delivered. These are restricted cells.

what's the intention? The minimal complete cost of shipping. And here's our purpose telephone (C12).

The subsequent aspect that you can do is to calculate the overall quantity shipped from every warehouse (G7:G8), and the entire items bought by using every consumer (B9:E9). which you could do that with standard Sum formulas proven in the below screenshot. also, insert the SUMPRODUCT components in C12 to calculate the full charge of shipping:

To make our transportation optimization mannequin simpler to take into account, create the following named degrees:

range identify Cells Solver parameter Products_shipped B7:E8 Variable cells purchasable I7:I8 Constraint Total_shipped G7:G8 Constraint Ordered B10:E10 Constraint Total_received B9:E9 Constraint Shipping_cost C12 objective

The closing thing left that you can do is configure the Excel Solver parameters:

purpose: Shipping_cost set to Min

Variable cells: Products_shipped

Constraints: Total_received = Ordered and Total_shipped <= accessible

Please pay consideration that we now have chosen the Simplex LP fixing formulation during this instance as a result of we are coping with the linear programming issue. in case you don't seem to be bound what sort of issue yours is, you can depart the default GRG Nonlinear solving method. For more suggestions, please see Excel Solver algorithms.

answer

click on the solve button at the bottom of the Solver Parameters window, and you will get your reply. in this illustration, the Excel Solver add-in calculated the finest amount of items to deliver to each and every consumer from every warehouse with the minimal complete charge of transport:

the way to retailer and cargo Excel Solver situations

When fixing a certain mannequin, you can also want to shop your Variable phone values as a situation that you should view or re-use later.

as an example, when calculating the minimal provider charge in the very first example mentioned during this tutorial, you may additionally are looking to are attempting different numbers of projected valued clientele monthly and notice how that affects the provider cost. At that, you may additionally need to retailer the most probably scenario you will have already calculated and restore it at any moment.

Saving an Excel Solver scenario boils down to choosing a number of cells to store the records in. Loading a Solver mannequin is just a be counted of featuring Excel with the range of cells where your mannequin is saved. The detailed steps observe under.

Saving the model

To save the Excel Solver scenario, perform here steps:

Open the worksheet with the calculated model and run the Excel Solver.

in the Solver Parameters window, click the load/keep button.

Excel Solver will let you know what number of cells are necessary to shop your scenario. opt for that many empty cells and click on keep:

Excel will store your existing mannequin, which might also seem to be some thing akin to this:

on the identical time, the Solver Parameters window will exhibit up the place that you could alternate your constraints and try diverse "what if" alternatives.

Loading the saved mannequin

when you decide to fix the saved situation, do the following:

within the Solver Parameters window, click on the weight/save button.

On the worksheet, choose the latitude of cells holding the saved model and click on Load:

in the Load mannequin dialog, click the substitute button:

this can open the main Excel Solver window with the parameters of the in the past saved model. All you should do is to click the resolve button to re-calculate it.

Excel Solver algorithms

When defining an issue for the Excel Solver, which you could choose certainly one of right here methods in the select a solving method dropdown field:

GRG Nonlinear. Generalized decreased Gradient Nonlinear algorithm is used for issues that are smooth nonlinear, i.e. wherein at least one of the crucial constraints is a easy nonlinear function of the resolution variables. more details can also be found here.

LP Simplex. The Simplex LP fixing method is based the Simplex algorithm created via an American mathematical scientist George Dantzig. it is used for solving so called Linear Programming problems - mathematical fashions whose requirements are characterized via linear relationships, i.e. consist of a single goal represented through a linear equation that have to be maximized or minimized. For greater suggestions, please take a look at this page.

Evolutionary. it's used for non-clean issues, which are probably the most problematic type of optimization issues to solve because one of the capabilities are non-smooth or even discontinuous, and therefore or not it's tricky to check the route during which a characteristic is expanding or reducing.  For more suggestions, please see this page.

To change how Solver finds an answer, click on the alternatives button in the Solver Parameters dialog container, and configure any or all alternatives on the GRG Nonlinear, All methods, and Evolutionary tabs.

this is how to use Solver in Excel to discover the most desirable options to your choice issues. And now, you may additionally wish to download the Excel Solver examples discussed during this tutorial and reverse-engineer them for enhanced figuring out. I thank you for studying and hope to see you on our blog subsequent week.

Learn Advance Statistics Training in Excel