Excel solver examples
This article demonstrates ways to use solver in Excel.
Table of Contents
1. Using Excel Solver to schedule employees
This is a question I found at the bottom of this page Using Solver to schedule your workforce
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:
I created a new formula in C17.
All other formulas are created in the initial setup, see xls file found here: Using Solver to schedule your workforce
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.
Get excel sample file for this article.
bank24_1.xls
(Excel 97-2003 Workbook *.xls)
2. 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.
