Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel Solver Examples

I just finished an online version of a tutorial that I created to provide a quick overview of the Excel solver using a couple of example problems. The first example is a very simple optimization problem to demonstrate the basic concepts. The second example solves a system of non-linear implicit equations, discussing how choosing different starting values can result in different solutions.

The article also provides a short tutorial for how to use the Solver within a macro.

Vertex42 Article: Excel Solver Examples

Enjoy,
Jon


Post your answer or comment

comments powered by Disqus
Hi,

I'm having huge problems with a uni assignment involving excel solver, and can find no similar example to help me out! No one else in my class seems to be able to find a way to do this either.

We need to use a grid to allocate resources. The name of the person is in th left column, with the resource listed across the top. The grid shows either 1(for resource allocated) or 0 (for unallocated). Below that grid, we have the same grid but with the preferences from 1 to 10 for each person for each resource. One resource must be allocated to each person. The aim of the solver is to minimise the preference scores. I have put an index/match formula at the bottom of each row of the first grid to look up the preference scores, and then created a total cell with a sum formula. When I run the solver, i make this cell the target cell (to be minimized), and have placed binary constraints on the allocations area. I've also included constraints on each row and column so that the sum of each must equal 1. However, when I run the solver (depending on options I choose) it either changes all to allocation cells to 1 and says it has found a solution which satisfies all conditions, or doesn't change anything and runs for about half a second.

There has to be a way to do this, otherwise they wouldn't set it as an assignment, but my class is totally befuddled, and our lecturer's away and won't give us any hints anyway. Does anybody know what I'm doing wrong, or have any ideas?

thanks!

I am trying to use the excel solver and am running into some issues. The
solver is giving me results that I know are wrong and I cannot get it to
give me the correct results. I am using it to calculate a linearity
which is based off of the STDEV and AVERAGE of a group of numbers. The
formula I am using is:

=TRUNC(SQRT(SUM(($B$2-AVERAGE($B$2:$B$6))^2,($B$3-AVERAGE($B$2:$B$6))^2,($B$4-AVERAGE($B$2:$B$6))^2,($B$5-AVERAGE($B$2:$B$6))^2,($B$6-AVERAGE($B$2:$B$6))^2)/4),0)/AVERAGE($B$2:$B$6)

This is basically the longhand version of STDEV/AVERAGE which I have
found gives the actual number I am looking for. For some reason, if I
take out the TRUNC or just use STDEV/AVERAGE, the number is a little
off. What I am using solver for, is to minimize the linearity by
changing the last number in the series.

For example, if all the numbers are the same, solver should give me
that number again. for a linearity of 0. It will not do this. If I try,
for example, all 1500s, solver will give 1639.

Any suggestions would be appreciated.

--
nakedbamboo
------------------------------------------------------------------------
nakedbamboo's Profile: http://www.excelforum.com/member.php...o&userid=28780
View this thread: http://www.excelforum.com/showthread...hreadid=484665

hi, using excel solver and realised that it will solve the change variable cells acording the constraints we set in the way of "Top to Boottom". It mean that when the solver meet all the constarints then it will be the solution. This will cause a heavier "workload" to be assigned to the front row cells (example: r1 will be use more than row 20). Is there any way to solve this problem, so that all row will be assigned a balance workload?

I am trying to use the excel solver and am running into some issues. The solver is giving me results that I know are wrong and I cannot get it to give me the correct results. I am using it to calculate a linearity which is based off of the STDEV and AVERAGE of a group of numbers. The formula I am using is:

=TRUNC(SQRT(SUM(($B$2-AVERAGE($B$2:$B$6))^2,($B$3-AVERAGE($B$2:$B$6))^2,($B$4-AVERAGE($B$2:$B$6))^2,($B$5-AVERAGE($B$2:$B$6))^2,($B$6-AVERAGE($B$2:$B$6))^2)/4),0)/AVERAGE($B$2:$B$6)

This is basically the longhand version of STDEV/AVERAGE which I have found gives the actual number I am looking for. For some reason, if I take out the TRUNC or just use STDEV/AVERAGE, the number is a little off. What I am using solver for, is to minimize the linearity by changing the last number in the series.

For example, if all the numbers are the same, solver should give me that number again. for a linearity of 0. It will not do this. If I try, for example, all 1500s, solver will give 1639.

Any suggestions would be appreciated.

Update:
I just discovered that if I run solver twice, it will give me the correct number. Can someone explain this? Thanks.

Hi

I've a big problem getting the Constraints in excel solver to use variables.
It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....

So the question is. How to use varialbes in the FormulaText argement other than string values as integers?

Private Sub
CommandButton1_Click()
Dim apa As String
apa = 250
    SolverOk SetCell:=[Ctot], MaxMinVal:=1, ValueOf:="0", ByChange:="$B$1,$B$2"
    SolverAdd CellRef:=[Ctot], Relation:=1, FormulaText:=apa
    SolverAdd CellRef:=[Avar], Relation:=1, FormulaText:="10"
    SolverSolve
    SolverReset
End Sub


Having a problem constructing a ratio constraint in Excel Solver.

I need to construct a ratio constraint of at least 1 to every 2.

For example, a facility must have at least one physician for every two extenders.

Can anyone help?

Thanks.

Hi,

I'm having a large document where I want to adjust the order of a planning to minimize one specific value.

Background example case
Just to explain the matter, I consider that there are 3 different programs in a car washing facility: short, medium and long.

The short program 1 takes 5 minutes, the medium program 2 takes 10 minutes to complete and the long program 3 takes 15 minutes to complete.

The current order first executes all short programs, 1-1-1-1-1-1 for example, then the medium programs and finally all long programs (3-3-3-3-3-3). This means the first programs are finished in 30 minutes and the long programs are finished in 90 minutes. It means that the average mean value of programs per hour will be 12 when you only execute short programs and 4 when there are only long programs left at the end. The goal is to balance these differences by trying to get a constant flow for every hour, let's say six programs per hour and to minimize any fluctuations. This is known as an optimal production mix or scheduling problem I hope the main problem is understable, but if I translate this to excel the current order is something like this (in minutes):
5
5
5
5
5
5
10
10
10
10
10
10
15
15
15
15
15 The solution to this problem may be easy to generate by hand, for the best planning possible with an average of 6 programs per hour is listed below. This is simply taking a short, then a medium and finally a long program. (1-2-3-1-2-3-1-2-3... instead of 1-1-1-1-1-2-2-2-2-3-3-3-3-3)
5
10
15
5
10
15 But I want to let the excel solver (or a different function if that saves me) to determine this optimal order of cells. So I don't want to change the values of a column, but just want to regroup them in such a way the excel solver can minimize any fluctuations.

The real case contains over 9000 rows each containing 20 specific cells for the equation to define the mean processing time and fluctuations, so I hope I've simplified the matter a bit.

I've also attached the example case in an excel worksheet with the optimal order next to it.

Hi, I had a quick question that I was curious about. I am trying to use the Excel solver to solve a system of ~60 nonlinear equations at once, but before I get too involved in setting it up does anyone know if solver would be able to handle something like that? I know in the past I have had issues with either "This problem is too large for solver to handle", or solver will run but just never converge on a solution (even if one does definitely exist). Sometimes when dealing with big problems like this I also notice that solver will act really funny...for example I may run it once and it will say "cannot find a solution", and I will run it again with nothing changed and it WILL find a solution. So, does anyone know workarounds for these things to make it work a bit more efficiently? Unfortunately pretty much every topic I've found on solving systems of equations with solver only focuses on 2-3 variables lol. I just want to make sure that I'm able to get consistent results when using it rather than have it work half of the time and give me error messages the other half lol. Thanks for the help!

Hey all! First time poster long time viewer!

I have a new challenge ahead of me for the 2012 year and I think using the Excel Solver will help immensely. As I have yet to construct a formal spreadsheet I was just brainstorming to see if this was at all possible.

1) The goal of this spreadsheet / solver would be to optimize given inventory to fit a certain quantity.

ie) I have a quantity of 10,000,000 and inventory of totaling 100,000,000 (in say 40 different line items)

I would need to find a way to optimize that 100,000,000 of inventory into that quantity for 10,000,000 while have the following constraints.

1) After the solver ran the inventory remaining for any given item could not be less than 25,000.00
2) That 10,000,000 has to conform to the following scenarios
a) Each 1,000,000 piece of the 10,000,000 total cannot have more than 3 items of inventory
b) The minimum size in that 3 items of inventory cannot be less than 25,000.00
c) If the tail on the quantity is odd ie) 10,509,000 -- that 509,000 can only use 2 pieces of inventory to fill it. Rule: If it is between 500,001 and 999,999 it is 2 items...if it is equal or less than 500,000 it is 1 inventory item

Hope someone can help

Regards,
Justin

Hi All,

I cannot figure out how to run Excel Solver to optimize items to be placed in sequence in order to reduce the amount of changes overall.

I have attached a spreadsheet to show my dilemma, the table outlines the amount of changes required from one item to the next. The solver should minimize the amount of changes while ensuring that every item is placed in sequence at least once.

If anyone can help, that would be greatly appreciated.

The constraints of the solver are: you must use each item number only once, but at least once. Determine the best sequence to place the numbers in that will reduce the total number of changes. The number of changes between any two items are in the table.

If anyone could help, I would very much appreciate it!

Thanks.

Hello,

I'm having trouble using Excel's Solver Add-in. I went ahead and added it into my spreadsheet, but I am not sure exactly how to use it.

I must find the formula for density given the dimensions of a bullet such as Mass(kg), Diameter (m), and Length (m).
The values for each of the dimensions are as follows,

Mass = 0.0021 which is cell R3C2
Diameter = 0.0056 which is cell R4C2
Length = 0.0254 which is cell R5C2

Also, I need to enter the correct final result for density of this bullet into cell R3C7.

I am well aware that the correct formula for density is D= mass/volume. With excel, I go ahead and enter this in the formula bar at the top of the spreadsheet. also D= mass/Pi*r^(2)*h

The formula I have entered in the formula bar is =RC[-5]/(PI()*(((1/2)*R[1]C[-5])^2)*R[2]C[-5])
and so far it keeps returning the result of 3356.755 (I have rounded the answer to three decimal places)
however, My prompt for this project is telling me that the proper result for the density of this bullet should be 1000 kg/m^(3).

So, I assume that I need to use Excel Solver to enter this formula in to get the right answer????
And if so, I am not completely sure on how to use Solver.

I will go ahead and attach my spreadsheet to illustrate everything that I have stated above.

Engr1412Project2.xlsx

Help would be very much appreciated, thanks for taking the time to read this if you have.

Hi all I have a cubic equation which is 0.044x^3 + 0.54x^2 -+ 13.26x + 36.78 = 0

I've graphed it from -500 to +500 and I've set up the excel solver to find the real root where it crosses the "x" axis. What I want now is to find the other two complex roots but I don't know if solver works for complex numbers. If it does then I'm not sure how it requires them to be input. At present when I try to use a complex number seed/start guess for "x" I get the error "#VALUE" Any information or suggestions would be much appreciated.uzzled:

Hello,

I am trying to do a project in Excel that requires the use of the excel solver. Originally, I had a problem with the number of constraints I was entering (you can only have 200 variables by default without purchasing an expanded solver from a third-party company). I overcame the constraints by using logic in other cells. Now, after getting all of that done, I am having some problems.

The solver isn't getting the correct answer for me because the "Solver could not find a feasible solution." I know that there is a feasible solution because it can be manually found.

So, I am asking to see if anybody has some time to possibly help me look at this solver issue? The worksheet is fairly complicated and I would be willing to help anybody figure out what is going on within it if they can help me figure out why the solver isn't working as it should.

If you have some time to help, please either post your e-mail address or Private Message me with it and I will send you the 375kb excel file. I can be available on any of the major chat programs (AIM, MSN, etc) to help figure out the excel file.

Thanks a lot in advance.

Mark

Hi there... I´m new to this.. but I´ve been bothered by this problem for so long that I´m coming here for help.

I´m implementing an optimization algorithm using Excel Solver in the code.

The algorithm constructs the cells with the Objective Function, the Restrictions, and the Variables. In each iteration it constructs a different Linear Program, but allways in the same sheet. Part of the code re-defines the ranges to be given to Solver as Restrictions. I added a procedure that loads the reference to Solver.xla, which I quote here:


	VB:
	
 InstallSolver() 
    Dim wb As Workbook 
     
    Sheets("LP").Select 
     
    On Error Resume Next 
     ' Set a Reference to the workbook that will hold Solver
    Set wb = ActiveWorkbook 
     
    With wb.VBProject.References 
        .Remove .Item("SOLVER") 
    End With 
     
    With AddIns("Solver Add-In") 
        .Installed = False 
        .Installed = True 
        wb.VBProject.References.AddFromFile .FullName 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So... after this is done... the code calls solver in this way:


	VB:
	
 PLRobusto() 
    Worksheets("LP").Activate 
    SOLVER.SolvReset 
    SOLVER.SolvOk SetCell:="ey", MaxMinVal:=2, ByChange:= _ 
    "vector_w, t, vector_y_a_" & CStr(iteracion) & ",vector_y_b_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_wXa_t_" & CStr(iteracion), Relation:=3, FormulaText:="vector_m_y_a_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_wXb_t_" & CStr(iteracion), Relation:=3, FormulaText:="vector_m_y_b_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_y_a_" & CStr(iteracion), Relation:=3, FormulaText:="0" 
    SOLVER.SolvAdd CellRef:="vector_y_b_" & CStr(iteracion), Relation:=3, FormulaText:="0" 
    SOLVER.SolvOptions MaxTime:=100, Iterations:=100, AssumeLinear _ 
    :=True 
    SOLVER.SolvSolve userFinish:=True 
    SOLVER.SolvFinish KeepFinal:=1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The ranges written between quotation marks refere to the ranges named by that string (which is done before in the main code).

In my PC, this runs perfectly. However, I´ve to work in my office, from where I´m writting right now... which has the same version of Excel, Windows 2000, etc... but it gets stuck in the first line that uses Solver.Solv*, giving a 424 error.

How can I resolve this? I´m desperate to finish this algorithm...

Ozono

COUNTIF - Excel 2007 COUNTIF - Excel COUNTIF Example

The COUNTIF function, one of Excel's COUNT functions, is used to count up the number of cells in a selected range that meet certain criteria.

I need ımmedıate help about excell solver. i have to solve a problem usıng excell solver but i dont have any idea how to use it . i have 45 minutes to solve a simple problem. is there any one who can help me?
ı would really appreciate!!!!

Hi,

I have an excel file that utilizes the excel solver (using a macro). It appears that the time it takes to get a solution depends on the number of formulas in the sheet (even though they are not being used by the solver). Is this a fair statement?

So let's say the solver only works with cells A1:A10, B1:B10, C1:C10. If I add formulas to the file in cells K1:K1000, M1:M1000 (that have nothing to do with the solver), the solver's time to find a solution increases.

Is there a way to increase the speed of the solver without impacting the accuracy of the results?

thanks,
hilss

hi,
have anyone use both the CDs(1900 Excel VBA Examples & Excel Knowledge base)? is their any different between them?

it is safe for me to assume Excel Knowledge base is better since it have 12000 + question while 1900 Excel VBA Examples only have 1900?

pls advice since i and a poor student can only afford to buy one CDs

have anyone use this CD before (1900 Excel VBA Examples)? I am thinking of purchasing it support my self-study in VBA.

Cheer,
memo

PS. to mr excel i was going to purhcase it off yourself but i don't get logic of it the CD is produce by u guys but is more expensive to purchase the CD here and cheaper in amazon.

I'm using the Excel Solver in an application that we have developed.
However, there appears to be a bug in the solver handling the international
settings in Excel (2003).

If you change the decimal and thousands separator in Tools->Options
(International) to something other than the system separators, the solver
will fail with the error "Error in model. Please verify that all cells and
constraints are valid.".

I first noticed this on a non-English WinXP system running English Excel
2003. The user had changed the settings in Excel to display floating point
numbers as they would in English. However, the same problem can occur in
English systems if you change the decimal separator to "," and the thousands
separator to "." as in many non-English locales.

Is there any way around this problem without forcing the user to use
international settings he does not desire to make?

Sincerely,

Michael

When using a Macro & Excel's Add In "Solver" the Excel Solver Results do not
show up on 2 computers. The results do show up on one computer that we have.

Please help! I need to install Excel Solver, but my Office 2003 disk is on a
ship from Australia to New York and won't arrive for 2 months. Does anyone
know how to install Excel Solver w/ out the Office Installation disk?

I'm using the Excel Solver in an application that we have developed.
However, there appears to be a bug in the solver handling the international
settings in Excel (2003).

If you change the decimal and thousands separator in Tools->Options
(International) to something other than the system separators, the solver
will fail with the error "Error in model. Please verify that all cells and
constraints are valid.".

I first noticed this on a non-English WinXP system running English Excel
2003. The user had changed the settings in Excel to display floating point
numbers as they would in English. However, the same problem can occur in
English systems if you change the decimal separator to "," and the thousands
separator to "." as in many non-English locales.

Is there any way around this problem without forcing the user to use
international settings he does not desire to make?

Sincerely,

Michael

I am trying to optimize my profit using excel solver. I can get it to run but I am wondering how to add the following constraint.

For my changing cells, I want them to either be 0,500, or 1000. How can I make this constraint?

Thanks


No luck finding an answer? You could always try Google.