I was able to find a solution using the excel solver add in but unfortunately it cant take more then 200 constraints and hence gives an error. I am unable to find a different approach to this problem.......
View 9 RepliesI have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?
I want to set constraints as an integer in solver. But I don't know how to do it.
View 5 Replies View RelatedI have a maximization problem. I have to maximize Kitchen Sets given available materials. I have 11 different Kitchen Sets and every set requires different material.I have attached an excel spreadsheet for making the problem more clear.
I have T1;T2;T3;T4;M1;M2;M3;M4;O1;O2;O3;O4;Z1;Z2;Z3;Z4 different materials. Every kitchen sets requires one material from T which is fixed (for example T2), one material from M which is fixed again (for example M3), one material from O which is fixed (for example O1) and one material from Z, which is fixed (for example Z4).To complete a set the sum of all materials used should equal to 4. I have some constrains T1+T2+T3+T4<=2 it is the same for M1+M2+M3+M4<=2, O1+O2+O3+O4<=2 and Z1+Z2+Z3+Z4<=2.
So basically my goal is to say which materials should I keep in stock in order to maximize the Kitchen Sets.
I want to take a shot at this spreadsheet exercise. Setup similar spreadsheet models.
Excel Exercise.xlsx
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
I recorded a macro using 'Solver'. When I try to run it, I get a message"Complie Error: Sub or Function not defined". Can anyone help me?
Heres the
Sub Macro1()
SolverOk SetCell:="$R$4", MaxMinVal:=3, ValueOf:="4275", ByChange:="$S$4"
SolverSolve
End Sub
I also tried this
Sub SolverMacro()
' Example Solver VBA Macro
SolverReset
SolverOk SetCell:="$R$4", MaxMinVal:=3, ValueOf:="4275", ByChange:="$S$4"
SolverSolve userFinish:=True
End Sub
I am using Excel's solver to work out an integer problem. Although the answers are indeed integers, the number in the formula bar is not. For example: although 7 is the answer, the formula bar give 6.99987584. Even if I try to format the cell for 0 decimal places, it does not correct.
This is leading to calculations down the line.
I have a little spreadsheet aplication which ran perfectly in Office 97, but since the move to Windows/Office XP, I get the message:
Solver: An unexpected internal error occurred, or available memory was exhausted.
I'm running a P4 2.26GHz with 512MB RAM.
My code is as follows:
Public Processing_Message As String, Macro_to_Process As String
Public StartTerm As Integer, StopTerm As Integer, StepTerm As Integer
Public ResultsLabelCount As Integer
Public myErrorFound As Boolean
Public rs As Object
Public ctl As Control
I have a macro that does a solver routine. I have another macro that runs this solver, then copies and pastes new inputs, then runs the solver, then copies the results and pastes them somewhere, then copies new inputs, etc. It is a long process and it worked fine a few days ago. Now when I run it, about 90% of the way through and then excel crashes. The error says:
Solver: An expected internal error occurred, or available memory was exhausted.
What can I do?
I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.
I'm using Excel 2007 - Windows.
Here is the code :
Sub Macro1()
Dim i As Integer
Dim j As Integer
j = 4
For i = 1 To 4000
If ActiveSheet.Cell(i, 12).Value = 1 Then
ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)
[code].....
I have to use use the solver to calculate something (a mean-variance framework).
I am using the solver to minimize a cartain cell (variance) by making two cells equal through (expected return) by varying 10 cells( weights of assets), but I have to repeat this for 500+ times (for different expected returns).
Someone told me that I could best use some sort of loop through VBA. But I don't have a clue how that works.
I am trying to maximize fantasy football points based on projected points scored, salary and positional limits.
I have multiple sheets dedicated one per position (i.e. sheet named "QB", sheet named "RB", etc.). The relevant columns are: Name; FPT (projected points); Cost
NOTE: I have combined all the data into "sheet1" if necessary.
I need a VBA program or Solver to diagnose the largest possible combination of projected points based on the following constraints:
Constraint 1: Total value must be less-than or equal to $1,000,000
Constraint 2: Total players used must be equal to 10
Constraint 3-?: Of the 10, 2 must be QB, 2 must be RB, 2 must be WR, 1 must be TE, 1 must be DEF.....the other 2 can be RB, WR or TE
Obviously, each player can only be used once and I'm not sure if Solver can abide by that constraint, nor do I know how to apply the others. I'm thinking VBA could work, but I'm not familiar enough with the language to hammer out the code.
I have a small macro that downloads some data from a website and tweaks it Daily fantasy cheatsheet.zipa little, but I hope that won't affect any computations involving those values.
I need to calculate time period worked between 19h00 and 24h00 in following exemples,
start time 08h00 and End time 20h00, would be 1 hour
start time 20h00 and End time 08h00, would be 4 hours
I can't get IF fonction to work with times..
i've two time constraints with 22:00~6:00 and 6:00~22:00. i'll apply time span to two constraints,calculate time covering on two constraints. it will start at anywhere of 00:00~24:00, time span will be 00:00 ~24:00. i add some formula, a3=start time, b3=end time. time constraints with 22:00~06:00:
=IF(AND(A3<=22/24,B3>=6/24,A3>B3),8/24,IF(AND(A3<6/24,OR(B3>22/24,AND(B3>0,B3<6/24,A3>B3))),IF(B3>=22/24,(B3-22/24)+A3-6/24,(B3+1-22/24)+MOD(6/24-A3,1)),IF(AND(OR(A3>=22/24,A3<B3,A3=0),B3<=6/24),IF(OR(A3=0,A3=1),B3,IF(A3>B3,B3+1-A3,B3-A3)),IF(AND(A3<=22/24,B3<=6/24),MOD(B3-22/24,1),IF(AND(A3>=6/24,B3<=22/24,A3<B3),0,IF(AND(A3>=22/24,B3>=6/24),(1-A3)+6/24,IF(AND(A3<=22/24,B3>22/24),B3-22/24,6/24-A3)))))))
time constraints with 06:00~22:00. :C$3=start time,$B4=end time...............................
I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:
A B
1 1
2 1
2 2
2 3
3 1
3 2
3 3
3 4
3 5
...
I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................
Whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:
X Y
24 0
30 0
10 24
8 38
2L-CVRP, what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution.
I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space - e.g. can it be 1%?.
View 5 Replies View RelatedThis is the code where you click on a cell that has been input from Data Validation, it gets a pop up textbox showing the text input. One drawback I see here is that when the sheet is protected, one needs to tick 'Edit Objects' in 'Allow all users of this worksheet to:' window to get the textbox to pop-up otherwise no textbox is shown.
So this leads to the accessed user to have the ability of deleting and moving the textbox in the protected spreadsheet. Do you have any idea to Protect the sheet, Get the textbox to pop up,
Disallow user to delete/move the textbox.
I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).
For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following
(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company
What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.
So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").
So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).
I want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?
I need to create a spreadsheet that will calculate the difference between two dates and show the result in days. The complicated part is that weekends (Sat and Sun) need to be ignored and any time after 4:30 pm needs to be treated as the next business day. I have been trying to look at similar questions in forum and use the WORKDAYS function with little success.
Example:
A1- 14/08/09 4:40 p.m.
B1- 17/08/09 9:10 a.m.
I need the answer (B1-A1) to show up as 0. But if the time in A1 was changed to 4:20 p.m. than the answer should be 1 day.
I am trying to drive Solver in Excel 2000 with a Visual Basic (version given as 9969) macro. SolverReset is observed, and SolverAdd puts the constraints in. But the SolverOK statement does nothing, so the SetCell and the ByChange windows stay empty.
SolverSolve is trying to do something, because the spreadsheet cell that should receive the return code from Solver gets an error value #VALUE! (The macro then stops, unable to process this entry.)
Solver is checked off ok in the References list in V.Basic.
The same macro, same spreadsheet, in the same Excel file, works fine on the other computer in my office with what I am told is a more recent version of Excel; i.e. this doesn't look like a coding error.
I wanted to see if I could use the solver to Solve Sudoku puzzles. I created the attached workbook. First it colors the numbers that are put in manually, which would be the starting puzzle. I have a generic one put in now. Then it finds which cells that are allowed to change. Then it is supposed to solve. It doesn't work. Run the Sheet1.SolvePuzzle Macro to see it not work. Is solver just not up to the task, or am I missing something?
View 2 Replies View Relatedcan I program a macro to run solver? ...
View 9 Replies View RelatedI need to solve this using solver. I have an idea, but I'm not sure what to in regards to my objective function, and how to properly set up my constraints. I know I need to use sumproduct, but I'm not sure in what way. I'm using Excel 2007, here is the problem:
The Sweet Smell Fertilizer Company Markets bags of Manure is Combination of compost and Sewage Wastes. Each pound of Compost costs sweet smell $0.05 and has a fertilizer rating of 2 units. Each pound of sewage costs $0.004 and has a fertilizer rating of unit. To provide good quality fertilizer, each bag should contain at least 35 pounds of compost, not more than 40 pounds of sewage, and have total fertilizer rating of at least 100 units. Determine the lowest-cost blend of compose and sewage in each bag.
Here is what I have so far: ....
I created a trendline and associated equation for some data plotted against Time on the x-axis displayed in MM/DD/YY format. The date for the data ranges from 3/27/06 thru 11/10/08.
The trendline equation is…
F(x)=-6E-13x^5+1E-07x^4-.0087*x^3+329.1*x^2-6E+06*x+5E+10
The data actually crosses the y=0 line around 11/5/07, which is the data root
When I use Goal Seek, I got a zero root solution of 28154.98. When I convert this to the date format, it converts to a date in 1981 which is not correct. I tried a range of initial guesses but they all converge to the same number.
I am using solver to solve an optimisation problem I have. I need to apply a new solver to every populated row of my sheet. My solver code is as follows:
Sub solverloop()
SolverOk SetCell:="$Z$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$N$2:$O$2"
SolverSolve
End Sub
The code above applies the solver to row 2. My sheet has roughly 500 populated rows. I need to write a macro to loop solver over each row, accept the solution provided by solver, then move to the next row. i would also like to close the loop automatically when the last populated row is reached.
I'm trying to run a macro which will allow me to run solver and keep the solution.. I have managed to get the macro to work. However, does anyone knows how I can ensure that solver's solution can be automatically saved without prompting the user whether to save the solution?
View 2 Replies View RelatedI am having problems with this excercise on my worksheet in my business computing class. I was wondering if someone could guide me through on how to use the solver feature.
Excercise:
Eve Blue is the manager of Southland Furniture store and she is planning a New Years Day sale. The store has only 75 square feet of space available to display and stock this merchandise. During the sale each folding table costs $5, retails for $11 and takes up two square feet of space. Each chair costs $4, retails for $9 and takes up one square foot of space. You must order at least one chair and one table and, of course, they must be whole units. The maximum amount allocated for purchasing the tables and chairs for the sale is $280. Eve cannot sell more than 40 chairs but the demand for tables is unlimited. Eve asked you to help her determine how many tables and chairs she should purchase in order to make the most profit.
Layout: (I marked in x's where I need to solve.)
TablesChairs
Unit Wholesale Cost $5.00 $4.00
Unit Retail Price $11.00 $9.00
Profit per Unit $6.00 $5.00
Square feet per Unit 2 1
Quantity to Order x x
Total Cost $- $-
Total Profit $- $-
Total Square Footage00
TOTAL ORDER COST $-
TOTAL ORDER PROFIT $-
TOTAL SPACE REQUIRED0