Solver Root
Nov 17, 2008
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.
View 9 Replies
ADVERTISEMENT
Sep 10, 2006
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?
View 9 Replies
View Related
Jan 12, 2007
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.
View 13 Replies
View Related
Nov 5, 2009
How set up root sum calculations.
A1 has 0
B1 has 0
C1 has 9
on D1 would like to show the root sum of 009 (which is 9). What is the best formula for cell D1?
A1:C1 will have random input numbers between 0-9.
View 9 Replies
View Related
Feb 23, 2014
I have a long url and I need only the root domain in the url.
for example:
[URL] .....
[URL] .....
[URL] .....
[URL] .....
[URL] .....
View 2 Replies
View Related
Oct 2, 2006
i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.
It is basically a 10x5 rectangle and i need to find the diagonal length
which should be 11.18 but it is returning 10
View 5 Replies
View Related
Jul 6, 2009
if the code is 110 add together all amounts attached to that code and sum them in a new cell. Perfect. Now however there is an added level of complexity and i need to figure out how to nest an IF statement into a =sumif() function so that code amounts are summed into their root code, for example the root code of 110, 120, 130 is 100.
So basically i want a function that will sum the specific code (110) and then sum together that amount under the root code (100). Here is an example. The amounts for the lower level codes have been summed but i need the high level codes to be summed together.
View 4 Replies
View Related
Jun 5, 2009
is it possible to type a document name in a cell,then run a code that searches a root dir and sub folders, find the existing document and open it?? for example
cell: "D20"
filename: DYAZES-001
root dir: "I:IsolationDataBaseIsolationProcedures
subfolders: DryMillA, DryMillB, Despatch, Maintenance
View 5 Replies
View Related
Nov 10, 2006
Square Root Spiral Function Graph. i was looking for vba code to graph the Square Root Spiral from Pythagorus in Excel...?
View 8 Replies
View Related
Feb 22, 2007
I have to run a daily report from winxp or win2k and need to change the host files a few times for access.
I tried to use %systemroot% in the file path, but for some reason vba doesn't like it.
Here's the snippet I'm using for copying over the host file based on user choice: (the thing works if I change to the actual system root path (C: etc...)
Private Sub CommandButton1_Click()
Select Case ComboBox1
Case "- Choose -"
MsgBox ("This is not a choice")
Case "BTR"
View 9 Replies
View Related
Dec 7, 2009
I have a formula and in it is a square root, i want the formula inside the square root always to be positive so i get a result.
Example:
(A1)
-1
(A2)
0
(A3)
-2
(C1)
=(A1)^0.25
(C2)
=(A2)^0.25
(C3)
=(A3)^0.25
Now i only get a result from the last two, but i want to use the modulus function so that instead of a maths error i get 1 for the first one, how do i do this?
View 2 Replies
View Related
Apr 25, 2012
I have run into a problem in a project where I know what the folder root of the file I would like to import into excel is, but I do not know the file name.
I would like to open a dialogue open where I can select the file I wish to import from the known root supplied and retain the file name as a string.
All solution I have read assume the full file root is know or file name is known but root is unknown
I am working with Excel 2003.
View 2 Replies
View Related
Jan 21, 2009
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?
View 9 Replies
View Related
Aug 19, 2009
I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing
A1 = γ2
A2 = AND(RIGHT(A1,1)=INT(A1))
True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have γ49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).
View 3 Replies
View Related
Feb 14, 2008
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.
View 4 Replies
View Related
Jun 2, 2009
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 Related
Feb 20, 2010
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
View 6 Replies
View Related
Dec 21, 2006
can I program a macro to run solver? ...
View 9 Replies
View Related
Feb 4, 2008
I 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: ....
View 9 Replies
View Related
Jan 14, 2009
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.
View 9 Replies
View Related
Nov 2, 2006
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 Related
Nov 8, 2006
I 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
View 3 Replies
View Related
Mar 23, 2007
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!)
View 3 Replies
View Related
Apr 24, 2007
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...
View 9 Replies
View Related
Jan 27, 2014
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
View 3 Replies
View Related
Nov 11, 2008
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
View 14 Replies
View Related
Jan 11, 2009
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.
View 7 Replies
View Related
Jan 25, 2010
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes.
I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
View 10 Replies
View Related
Jul 25, 2009
I would like the XL add-in 'Solver' to automatically find a value 'M' in the equation attached, based on 'R' being 30%.
I also would like to be able to manually enter 'M' based if I would like 'R' to be different from 30%.
How can I program this in VBA, and have the equation automatically done in the first instance simply by clicking on cell 'R'.
View 2 Replies
View Related
Jan 11, 2010
See my file. I want to calcuate a % needed (20%) to add to a another material. Should be easy but I can not figure it out.
View 3 Replies
View Related
