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

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

Vertex42 Article: Excel Solver Examples

Enjoy,

Jon

- Excel Solver Problem ... Help Please!
- Excel Solver Issue
- Can excel solver solve in the way of bottom to top?
- Excel Solver Issue
- Using variables in excel solver constraints problem!
- Excel Solver- Ratio Constraint
- How to use excel solver to determine optimal order
- Limitations of the Excel "Solver" feature?
- Excel Solver Help -- complex
- Excel Solver - Optimize sequence
- Problems Using Excel Solver
- Complex roots for cubic using Excel Solver ????
- Excel Solver
- Call to Excel SOLVER gives 424 in a VBA Macro
- COUNTIF - Excel 2007 COUNTIF - Excel COUNTIF Example
- Excell solver!!!!
- Excel Solver - slow
- 1900 Excel VBA Examples & Excel Knowledge Base what diff
- Have anyone use this CD before (1900 Excel VBA Examples)?
- International Bug in Excel Solver
- Excel Solver Results do not show up
- How can I install Excel Solver without the Office 2003 disk?
- International Bug in Excel Solver
- Excel Solver

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!

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

=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.

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

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.

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.

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

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.

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.

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:

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

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:So... after this is done... the code calls solver in this way: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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:The ranges written between quotation marks refere to the ranges named by that string (which is done before in the main code).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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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.

Ä± would really appreciate!!!!

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

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

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.

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

show up on 2 computers. The results do show up on one computer that we have.

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?

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

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

Thanks