Excel solver examples

This article demonstrates ways to use solver in Excel.

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.

1. Objective function: Solver can optimize a target cell (the objective function) by changing the values of other cells (the decision variables).
2. 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.
3. 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.
4. 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:

1. Open the Excel Options menu by going to File > Options.
2. In the Options window navigate to the Add-Ins tab.
   
3. In the Manage drop-down select "Excel Add-ins" and press with left mouse button on the "Go" button.
   
4. 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

1. Press with left mouse button on Data on the Ribbon
2. Press with left mouse button on Solver
   
3. Press with left mouse button on "Start Target Cell" button and select C17
4. Press with left mouse button on Min in "Equal To:"
5. Press with left mouse button on "By Changing Cells:" button and select A5:A11
6. Press with left mouse button on Add button
7. Press with left mouse button on "Cell Reference:" button and select A3
8. Press with left mouse button on triangle and select "="
9. Type 22 in "Constraint:" window
10. Press with left mouse button on Add
11. Press with left mouse button on "Cell Reference:" button and select A5:A11
12. Press with left mouse button on triangle and select "integer"
13. Press with left mouse button on Add
14. Press with left mouse button on "Cell Reference:" button and select C12:I12
15. Press with left mouse button on triangle and select ">="
16. Press with left mouse button on "Constraint:" button and select C14:I14
17. Press with left mouse button on OK!
18. 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

Answer:

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

1. Press with left mouse button on Office button
2. Press with left mouse button on Excel Options
3. Press with left mouse button on add-ins
4. Install Solver
5. Press with left mouse button on OK

How to use this template

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

Change constraint in excel solver

1. Press with left mouse button on $F$15 in "Subject to the constraints:" window
2. Press with left mouse button on Change button
3. Change value to your specific sum
4. 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

Get the Excel file

cash-drawer-bill-extractor.xls

Solver category

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

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

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

Excel categories

Leave a Comment