# Excel solver examples

This article demonstrates ways to use solver in Excel.

### Table of Contents

## 1. Introduction

**What is the Excel Solver?**

Excel Solver is a built-in optimization tool in Microsoft Excel that allows users to find optimal solutions for complex mathematical problems. It is used to find the values of decision variables that maximize or minimize an objective function while satisfying a set of constraints.

- Objective function: Solver can optimize a target cell (the objective function) by changing the values of other cells (the decision variables).
- Constraints: Solver can apply various constraints to the decision variables, such as upper and lower bounds, equality or inequality constraints, and integer or binary restrictions.
- Solver methods: Solver uses different optimization algorithms, such as the Generalized Reduced Gradient (GRG) method, the Simplex LP method, and the Evolutionary method, to find the optimal solution.
- Sensitivity analysis: Solver can perform sensitivity analysis to understand how changes in the input variables affect the optimal solution.

The Excel Solver add-in is a more basic free optimization tool built into Excel, while the standalone Solver software offers more advanced features and capabilities but at an additional cost. The choice between the two depends on the complexity of the optimization problem and the specific requirements of the user.

**When to use the Excel Solver tool?**

Excel Solver is commonly used in various applications such as:

- Resource allocation and scheduling
- Financial modeling and portfolio optimization
- Production planning and inventory management
- Engineering design and optimization
- Logistics and transportation problems

By using Excel Solver, users can find the best possible solution to their optimization problems, which can lead to improved decision-making, cost savings, and overall efficiency.

**How to enable Solver in Excel 365?**

Follow these steps to enable the Solver in Excel 365:

- Open the Excel Options menu by going to File > Options.
- In the Options window navigate to the Add-Ins tab.

- In the Manage drop-down select "Excel Add-ins" and press with left mouse button on the "Go" button.

- In the Add-Ins available box, check the box next to the "Solver Add-in" and press with left mouse button on "OK".

After loading the Solver Add-in, the Solver command will be available in the Analysis group on the Data tab.

The image above shows the Solver button located on the "Data" tab on the ribbon.

- If the "Add-Ins available box" doesn't contain the Solver Add-in press with left mouse button on the "Browse" button to locate and select the add-in.
- If you get a message that the Solver Add-in is not currently installed then press with left mouse button on "Yes" to install it.

## 2. Using Excel Solver to schedule employees

This is a question I found at the bottom of this page Using Solver to schedule your workforce (Microsoft has now removed this question from their website)

**"Bank 24" processes checks 7 days a week. The number of workers needed each day to process checks is 17 workers are needed on Monday, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, 17 workers are needed on Thursday, 9 workers are needed on Friday, 9 workers are needed on Saturday and 12 workers are needed on Sunday. All bank employees work five consecutive days.**

**Suppose our bank had 22 employees, and that their goal was to schedule employees so that they would have the maximum number of weekend days off. How should the workers be scheduled?**

Here is the setup:

Here is a break-down of the image above:

Cell range A5:A11: This column represents the "Number starting" for each day of the week. It shows how many workers start their shift on each day:

- Monday: 5 workers
- Tuesday: 5 workers
- Wednesday: 0 workers
- Thursday: 0 workers
- Friday: 0 workers
- Saturday: 4 workers
- Sunday: 8 workers

Cell range C5:I11: This is a data table showing the working schedule for each day. A "1" indicates that a worker who started on that row's day is working on that column's day. For example:

- Workers starting on Monday (row 5) work Monday through Friday.
- Workers starting on Tuesday (row 6) work Tuesday through Saturday.
- This pattern continues, with each worker having a 5-day work week.

Objective: To minimize the value in cell $C$17,

By changing variable cells: $A$5:$A$11 (the number of workers starting each day)

Subject to the constraints:

- $A$3 = 22 (total number of workers)
- $A$5:$A$11 = integer (ensuring whole numbers of workers)
- $C$12:$I$12 >= $C$14:$I$14 (ensuring the number of workers each day meets or exceeds the needed number)

I created a new formula in C17. This formula adds the number of workers working on a saturday and on a sunday, it returns a total that the Solver can use to minimize the number of workers working on the weekend.

All other formulas are created in the initial setup, see xls file found here: Using Solver to schedule your workforce (The .xls file is removed from the Microsoft webpage)

### 2.1 How to schedule employees so that they would have the maximum number of weekend days off

- Press with left mouse button on Data on the Ribbon
- Press with left mouse button on Solver

- Press with left mouse button on "Start Target Cell" button and select C17
- Press with left mouse button on Min in "Equal To:"
- Press with left mouse button on "By Changing Cells:" button and select A5:A11
- Press with left mouse button on Add button
- Press with left mouse button on "Cell Reference:" button and select A3
- Press with left mouse button on triangle and select "="
- Type 22 in "Constraint:" window
- Press with left mouse button on Add
- Press with left mouse button on "Cell Reference:" button and select A5:A11
- Press with left mouse button on triangle and select "integer"
- Press with left mouse button on Add
- Press with left mouse button on "Cell Reference:" button and select C12:I12
- Press with left mouse button on triangle and select ">="
- Press with left mouse button on "Constraint:" button and select C14:I14
- Press with left mouse button on OK!
- Press with left mouse button on Solve.

The Solver uses these parameters to adjust the number of workers starting each day (A5:A11) to find the optimal schedule that minimizes the objective while meeting all constraints. It does this using the Simplex LP (Linear Programming) method, which is appropriate for this type of linear optimization problem.

The Solver iteratively tries different combinations of starting workers each day, checking if the constraints are met and evaluating the objective function. It continues this process until it finds the optimal solution that minimizes the objective while satisfying all constraints.

This approach allows for efficient scheduling of workers to meet daily needs while minimizing some cost or inefficiency metric.

Get excel sample file for this article.

bank24_1.xls

(Excel 97-2003 Workbook *.xls)

## 3. Cash drawer bill extractor

**Question:**

I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back in the cash drawer in small bills, change and rolls of change. I need to remove exactly $ 1178.29 in large bills and leave the remaining $150 (from a total of $1328.29 in the register) in small bills and change.

### Answer:

I am using excel solver to find a solution. Here is how to install the Solver add-in in Excel.

- Press with left mouse button on Office button
- Press with left mouse button on Excel Options
- Press with left mouse button on add-ins
- Install Solver
- Press with left mouse button on OK

### How to use this template

- Select max quantities in cell range B3:B13
- Start excel solver,Â it is on tab "Data" on he ribbon.

Change constraint in excel solver

- Press with left mouse button on $F$15 in "Subject to the constraints:" window
- Press with left mouse button on Change button
- Change value to your specific sum
- Press with left mouse button on OK

Press with left mouse button on options button and enable "Assume Linear Model".

Press with left mouse button on OK.

Press with left mouse button on Solve button

### Solver category

Table of Contents Identify numbers in sum using Excel solver Find numbers in sum - UDF Find positive and […]

The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]

This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]

### Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form