I want to have a restriction that the cell that is to be changed (not the target cell) must have a certain precision eg 0.1 or 0.01 cause i wouldnt want a solution where it changes to 73,67,I would rather it changed to 73,70
View 9 Replies
Is there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?
The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.
I 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 have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.
As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.
I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?
I am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.
View 9 Replies View RelatedThis "If" wont work, does anyone have an idea why? I have try hard coded, with "" and without, i tried to assgind the field value to another double patameter and still nothing. This is my last try where i converted it to double type
View 7 Replies View RelatedMy macro uses the solver 100 times. Everything is working well, but it's a little bit slow because of all the cell references.
I was wondering if it's possible to use the solver with variables without cell references?
Is there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.
In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?
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 have a HUGE dataset (more than 1 million rows). I'm trying to find the optimum cell range size - 10 rows, 100 rows, 682 rows, whatever - for a SUM formula. (The formula would be in every row, looking down at the next n rows).
Is it possible to control the SUM cell range specified in a formula in cell (X) with a value in cell (Y)? In other words, the SUM formula in cell (X) would somehow refer to cell (Y) to determine the SUM cell range.
Ideally, I would like to then use Solver to find the optimal value for cell (Y).
Is this possible with just formulas? Or is VBA required?
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 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 have a number of houses & a number of water plants (as in water processing plants, not the green kind!) Each water plant can provide a certain % water coverage to all properties. 'All Properties' is classed as the number of houses + the number of water plants. The 'coverage' as a percentage is given by the formula:
View 5 Replies View RelatedI have a data output from MS Query that outputs full date/ time stamps, that is dd/mm/yyyy hh:mm:ss. I've tried =round(), =left() and setting the "precision as displayed" option in the advanced options for the workbook, but the full value remains.
I've also in the past seen other outputs imported into Excel (though not my own) come through with strange formats that can only be properly changed having used the "text to columns" tool, but this has made no difference either.
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 have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.
The existing code i have is:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText As String
MyText = Environ("username")
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:TAOffshoreTAOffshoreTreasuryRecsGeneralCommit ID''s for control Sheet - Do not move or delete[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"
[code].....
I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value "" Then' code?
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 RelatedI 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
can 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
At work, we have a spreadsheet that uses SOLVER.XLA in a macro. It works fine in Excel '97 but in Excel 2003 it simply does nothing. The code is
s = Application.LibraryPath & "solversolver.XLA"
On Error Resume Next
Workbooks.Open filename:=s
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.RESET()"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OK( PLOT!R46C4,2,0,(PLOT!R13C12:R14C12))"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OPTIONS(100,100,0.000001,FALSE,FALSE,2,1,1,0.05,TRUE)"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OK(PLOT!R46C4,2,0,(PLOT!R13C12:R14C12))"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.SOLVE()"
ActiveSheet. Protect DrawingObjects:=False, Contents:=True, Scenarios _
:=True, password:="sigma"
It gives no error message, it simply does not do the calculations. (I took out the error handling line to make sure that there really were no errors being generated!)
I am trying to get Solver to list a combination of invoices which make exact specified amount of close to...
attached is an example, maybe im doing it completely wrong, or other tools can be used...
Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub
I'm having issues getting solver to use the minimum and maximum for hours and items to be produced. I can't seem to get a balance between the two.
Solver 1.xlsx
I have the following dataset and was wondering how I can run a constrained regression in Excel with the constraint being that the total allocation of assets is 100%:
Total return (y): 12 data points
Asset 1 (x1): 12 data points
Asset 2 (x2): 12 data points
Asset 3 (x3): 12 data points
Asset 4 (x4): 12 data points
Asset 5 (x5): 12 data points
Asset 6 (x6): 12 data points
[Attached is a spreadsheet with the actual dataset]
I know the regression equation I need is R = b1X1 + b2X2 +
.+ (1 - b1 - b2 -
.- b5 )X6 + e
I have a problem with a macro in Excel 2007. It invokes
solver and creates an answer report. Please find the VB
code for macro, below.
