Tag Archives: Automatic

How to Use Solver in Excel

Solver is an add-in tool that lets you find solutions to various problems. You can use solver in Excel to run through “what-if” scenarios that might help you determine the best course of action. The “best” answer will be different for each problem, and might include maximizing profits, reducing cost, or producing the best use of resources.

Before we continue, you should check to see that Solver is installed on your version of Excel.

Navigate to the add-ins menu, and make sure that Solver Add-in has a check in the box next to it.

Solver Add-In Menu

You should then be able to find Solver in the Data tab.

Solver Data Tab

If you’re able to see Solver in the Data tab like in the screenshot above, you should be all set to use Solver in Excel.

To understand how to use Solver in Excel, you should know the group of cells that the add-in works with:

  • Decision Variable Cells: The values in the variable cells are adjusted by Solver when solving a problem.
  • Constraint Cell: Provides Solver with the restrictions, or limits on the values that it has to work with in order to produce a result.
  • Objective Cell: Solver provides the optimal value depending on the criteria provided in the Objective Cell. This cell must contain a formula.

Before you start to use Solver, you will want to have a spreadsheet set up with a problem that you want to solve. For demonstration purposes we will work with a spreadsheet that is trying to determine how many of four different types of “Widgets” to manufacture in order to maximize profits.

We will assume that each Widget produces the following profits:

  • Widget A: $25
  • Widget B: $35
  • Widget C: $45
  • Widget D: $55

Assuming there were no constraints, and we could manufacture and sell Widget D just as easily as the other Widgets, we should drop Widgets A-C and just focus on Widget D.

However in real life there are constraints such as time to produce, availability of materials, etc.

Let’s also assume the following constraints for each Widget:

  • Widget A Requires:
    • Part A: 4
    • Part B: 6
    • Time (hours): 0.25 (15 minutes)
  • Widget B Requires:
    • Part A: 6
    • Part B: 5
    • Time (hours): 0.25 (15 minutes)
  • Widget C Requires:
    • Part A: 4
    • Part B: 8
    • Time (hours): 0.5 (30 minutes)
  • Widget D Requires:
    • Part A: 6
    • Part B: 10
    • Time (hours): 0.5 (30 minutes)

We also have a maximum of 17,000 Part A, 50,000 Part B, and 2,000 hours of labor.

What we are looking to do is solve the following equation, where YA is the quantity of Widget A, YB is the quantity of Widget B, and so on.

Maximize: 25 YA + 35 YB + 45 YC + 55 YD

Subject to these constraints:

Part A: 4 YA + 6 YB + 4 YC + 6 YD <= 17,000
Part B: 6 YA + 5 YB + 8 YC + 10 YD <= 50,000
Time: 0.25 YA + 0.25 YB + 0.5 YC + 0.5 YD <= 2,000

Additionally, another constraint is you can’t build a negative number of any of the Widgets, so:

YA + YB + YC + YD >= 0

Set-up Your Spreadsheet With Data

We can then build a spreadsheet with this information:

Solver Spreadsheet Setup
Click for larger image

In the spreadsheet all the numbers, other than the numbers in column G, were manually entered. There are four cells with formulas:

  • G5: =SUMPRODUCT(C3:F3,C4:F4) – Note this is the same as saying =C3*C4+D3*D4+E3*E4+F3*F4.
  • G8: =SUMPRODUCT(C8:F8:$C$4:$F$4) – In this formula, we add the $ signs to keep the reference to cells C4:F4 static when we eventually copy the formula to other cells.
  • G9-G10: Copy the formula from G8 down to G9 and G10. Since we used the $ signs in the formula in G8, the formula in G9 and G10 will retain the references to cells C4:F4 (because this part had the $ signs), but will update itself to reference C9:F9, and C10:F10 (since this part did not have $ signs).

Use Solver to Maximize Profits:

Now, to find out the maximum profit, we are going to let Solver run through the scenario and provide us with an answer. First we need to tell Solver what we are trying to do.

  • On the Data tab, click on Solver.
  • Click in the Set Objective field and type $G$5, or click on cell G5. This tells Solver which cell we want to optimize.
  • The To option should be set to Max. This tells Solver that we want to maximize the value in cell G5.
  • In the By Changing Variable Cells box, type $C$4:$F$4, or highlight the range C4:F4 with your mouse. This tells Solver which cells should change in order to maximize the value in cell G5.
  • Next to the Subject to Constraints box click the Add button. Here we will provide the constraints that we described in the math formula above.
    • We want the resources to be limited by the quantities we have available, so in the Cell Reference box (left side), enter $G$8:$G$10, or select the range G8:G10 with your mouse.
    • The dropdown box (middle) should be set to <=.
    • The Constraint box (right side) should be set to the maximum quantities available, which in our case is $H$8:$H$10.
    • Click OK.
  • If we had additional constraints to enter, we could do so by clicking the Add button. In this case we want to make sure the Units to produce are not negative, we could enter a new constraint of $C$4:$F$4 >= 0.
  • Since all of our variables are constrained, it won’t change the outcome if you check or uncheck the box for Make Unconstrained Variables Non-Negative.
  • Click Solve.
Solver Parameters
Click for larger image

Solver will run through the problem and provide the optimal number of each Widget type to produce in order to maximize profits. You should see a message saying that “Solver found a solution”.

Use Solver in Excel
Click for larger image 

In this scenario, in order to maximize profits we need to produce 3,500 of Widget C, 500 of Widget D, and none of Widgets A or B.

Find Ways to Further Maximize Profits:

If we want to generate an Answer report, click on Answer under the Reports section in the Solver Results window. Solver will create an Answer Report tab to the left of the tab you are working on.

Solver answer
Click for larger image

The Answer Report will show you the original and final values of the objective and variable cells. It will also list out the resources that prevented Solver from further maximizing our profits.

In this case, Part A and Time were both fully used up. In the report their status is “Binding”, with a slack of 0. Part B was the only resource that was not binding, as there were 17,000 units left over.

In the real world we could use this information to try to determine if we could possibly acquire more of Part A, and hire more employees to further maximize the profits.

If this wasn’t a possibility, we could see if there was something else we could make with the 17,000 remaining units of Part B.

Other Ways to Use Solver in Excel:

We just ran through a simple scenario to maximize the profit of a widget manufacturer. However the usage of Solver is not limited to producing widgets.

You can use Solver in Excel to help allocate money to certain investments, purchasing real-estate, or scheduling staff at a multi-location business.

Do you use Solver in Excel? Let’s hear about how you use it in the comments.

10 Excel Tips For Increased Productivity

Microsoft Excel has tons of uses available to its users. But not everyone knows all the features that Excel has to offer. Some Excel tips will help you instantly become more productive, or use Excel more efficiently.

Click through to see our top 10 Excel tips.

How to Recover Lost Excel Passwords

Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to.  What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary.  Simply put, if you don’t already have access to the worksheet, this macro will not help you.

Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with.  By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

  • accidentally (or intentionally) removing formulas, or other contents of locked cells
  • adding or deleting rows and columns
  • changing cell, column or row formats
  • sorting data
  • using AutoFilter or PivotTable reports
  • editing objects or scenarios


Protecting a worksheet will not keep users from

  • editing any unlocked cells in the worksheet
  • viewing all data in the worksheet, regardless of if it is in a locked cell or not

Being able to unlock a password protected worksheet is useful, when

  • you have forgotten the password on your own worksheet
  • a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
  • you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
  • you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data

Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet.  This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet.  For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”.  The opposite is also true (i.e. protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”).  Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.

The macro code below will “unlock” one worksheet at a time, using the method used above.  It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.


Sub PasswordRecovery()
'
'MACROS BY EXCELZOOM.COM
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Goal Seek to Solve A Problem

Microsoft Excel is jam packed with cool useful features that tend to go unused by most users, simply because they don’t know how to use them, or even that they exist.  One of these features is Excel’s Goal Seek tool.

First, some background on what it does.  Goal Seek is essentially the answer to every middle school aged kid’s math test word problem.  It is best used when you know the answer to the problem you’re trying to solve, but don’t know all the inputs.

For example, a simple word problem might be “You have 432 sheets of paper to give out to a class of 36 students.  How many sheets of paper does each student get.”  Well you know that 36 multiplied by something will give you 432, but you don’t know what that number is.  You could divide 432 by 36 to get the answer, but let’s assume that isn’t possible right now.  With Goal Seek, you need several things:

  • An answer expressed as a formula, in this case 432 is equal to 36 times something.  Don’t worry, the result of the formula doesn’t have to equal 432 yet.  That’s the magic of Goal Seek.
  • You need one part of the equation, in this case 36.


To start, type the following into your spreadsheet in the cells indicated.

 

1

 36

2

 <leave blank>

3

 =A1*A2

Next, open Goal Seek (Tools | Goal Seek, or Data tab on the Excel 2007 ribbon | What If Analysis | Goal Seek).

You are now going to be asked for three things. 

  1. Set Cell: this is the reference to the cell that contains your formula, in which you want Excel to produce your answer of 432.  In our example it should be A3.  Note: the Set Cell MUST contain a formula or function.
  2. To Value: this is the value you want in your Set Cell (A3).  The value here should be 432.
  3. By Changing Cell: Since we know that the formula in cell A3 is correct, and the value of 36 in cell A1 is correct, we want Excel to produce the answer of 432 by changing cell A2.  Type A2 in this box.  Note: the Changing Cell MUST contain a value.
  4. Click OK.

After clicking OK, Excel will attempt to find a solution to the equation.  Once it does, it will enter the missing number in cell A2 and your formula should now equal 432.

Obviously this is a basic example, and doesn’t use Goal Seek’s capabilities to their fullest extent.  Let’s take another example that looks at a company’s sales by product to get their desired level of revenue.

XYZ Company makes four different products, Product A, Product B, Product C, and Product D.  The company can produce 1,000, 750, 500, and 100 of each product respectively.  The products also sell for $15, $20, $35, and $50 respectively.  Their maximum revenue for products produced that month, therefore is as follows:

 

A

B

C

D

1

Product Production Sales Price Net Revenue

2

Product A 1,000 $15 $15,000

3

Product B 750 $20 $15,000

4

Product C 500 $35 $17,500

5

Product D 100 $50 $5,000

6

Total     $52,500

Now say the company feels that their underdog, Product D, could be producing much more.  They also want their revenues to be 60,000.  How many more of Product D will they need to produce?

To figure this out, run Goal Seek just like before, except this time, the “Set Cell” is cell D6, “To Value” is 60,000, and the “By Changing Cell” is cell B5.  After clicking OK, you’ll notice that Excel has changed the value in cell B5 to 250, indicating that you will need to increase production of Product D to 250 units from 100 in order to have revenues of $60,000, assuming everything else remains the same.

Create and Name a New Worksheet

Say you have a spreadsheet that keeps track of weekly sales information, which you want displayed in a new sheet for each month. You don’t want to copy an “original” worksheet each week, so a macro can help to automate this process.

The macro code below will copy the worksheet named “Original”, and place it after the last worksheet in your file. It will then prompt you to enter in the date that corresponds with that week. It will continue to do so until you enter a valid worksheet name (i.e. valid format and a name that isn’t already used somewhere else in the workbook. Make sure your workbook already has a file called “Original”, and it is formatted the way you want all your weekly reports to be formatted. (Note if your “Original” worksheet isn’t called “Original”, you can change the name in the macro code below to whatever you want).

Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub CopyNewSheet()
'
'MACROS BY EXCELZOOM.COM
'
Dim shname As String
Dim wrksh As Worksheet
Worksheets("Original").Copy after:=Sheets(Worksheets.Count)
Set wrksh = ActiveSheet
Do While shname <> wrksh.Name
shname = Application.InputBox _
(Prompt:="Enter This Week's Date")
On Error Resume Next
wrksh.Name = shname
On Error GoTo 0
Loop
Set wrksh = Nothing
End Sub

Run Macro At a Specified Time

Using the Application.OnTime Method will allow you to schedule a macro to be run at a specified time. The specified time can be either after certain amount of time has passed, or at a certain time of day. This is especially useful if you would like a daily or weekly report to be printed, without having to be around to print it. If the report is long, it might take a while to print, so it might be useful for the macro to run after business hours or on the weekend.

The only drawback to this macro is that the file must be open in order for it to run. You can have this automated for you by using the Windows Scheduler found in the Control Panel to open the file at a certain time, and have the code below run automatically when opened.

Copy all the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “my_macro” with whatever name you have given your macro.

Need help? Use our nifty guide to help figure out how to install and use your macros.

The code below will allow you to run a macro called “my_macro” 30 seconds from now. You can change the time to whatever time interval you would like.

Sub Run_After_30_Sec()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime Now + TimeValue("00:00:30"), "my_macro"
End Sub

Use the following if you wish to run the macro at a certain time of day. The code below will automatically run my_macro at 5pm (written in 24 hour time as 17:00:00).

Sub Run_At_5PM()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime TimeValue("17:00:00"), "my_macro"
End Sub

Run Macro Automatically on Open

Insert your macro code into a module, start with Sub Auto_Open(). Enter whatever commands you wish to have executed on opening the file and end with End Sub.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “Msgbox “Hello” ” with whatever macro commands you wish to have run whenever your file is opened.

Need help? Use our nifty guide to help figure out how to install and use your macros.


Sub Auto_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub

Use the following if you wish to put the macro in your workbook as opposed to in a module. Again replace “Msgbox “Hello”” with your macro command:

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub