VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
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 ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Multiple Solver Constraints In Solver
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 Replies!
View Related
Solver With Constraints For Inventory Levels
I 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.
View Replies!
View Related
Solver Solver To Calculate Through VBA
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 Replies!
View Related
Add-in 'Solver' To Automatically Find A Value In Vba
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 Replies!
View Related
Adding Constraints To Averaging Formulas Over Multiple Cells
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).
View Replies!
View Related
Use Solver To Find A Value, Which Works
I have a proyect evaluation. I use solver to find a value, which works. I recorded a macro for this situation (which repeats all the time), but when I run it, it does not work. The problem is not the answer, it is that the macro does not run Sub TIR() SolverOk SetCell:="$M$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$44" SolverSolve Range("E44").Select End Sub
View Replies!
View Related
VBA Code - Color Changing Cells
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other". I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
View Replies!
View Related
Find Last Cell In Range, And Changing Another Cell Based On Offset Text
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F" and also t = time()
View Replies!
View Related
Error While Using Solver In VBA
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 Replies!
View Related
Run Solver With VBA
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 Replies!
View Related
Solver Automation VBA Code
I've done quite a bit of research in the past week or so on the topic of solver automation using loops; while there are a few good posts scattered around, i haven't quite found the solution to my problem. i'm trying to run the solver on a few like-sized datasets contained in one worksheet. i've set up a loop to properly reference the 4 datasets. the solver constraints are in one column on the same worksheet. the macro runs with no errors, but it doesn't "solve." the parameter cells retain their original values. i should mention that my macro ran perfectly before i consolidated the 4 separate solver calls into one loop. I got tired of having to type 4 different constraint changes in the code as i was optimizing the solver. on a similar topic, does anyone know how to call a variable in the FormulaText declaration? If i wanted to step through a defined array of constraints, say [1,2,3], how do i get FormulaText:= variable to work? Sub Macrosolver() Dim iteration As Long Dim i As Integer i = 8 'start in row 8 at first dataset
View Replies!
View Related
Solver- When Solver "changes Cells"
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 Replies!
View Related
VBA - Changing The Value Of A Cell From A Formula To A Value
I currently have some code in VBA that for each line creates a vlookup and a second one that takes the left part of a cell, depending on where a "/" is located in the cell as below: For i = 2 To FinalRow Cells(i, FinalCol + 1).Formula = "=VLOOKUP($A" & i & ",LookupList,3,FALSE)" Cells(i, FinalCol + 2).Formula = "=LEFT($F" & i & ",FIND(""/""," & "$F" & i & ",1)-1)" Next i The issue I am finding is that running the 2 formulas for 4000 rows is taking approx 5 mins. The end user doesn't need to see a lookup, the value stored from the result would be fine. is this possible. So that the value of the cell is a result of a formula I have created?
View Replies!
View Related
Changing Cell Parameters? Vba
I am creating a user defined function in excel VBA. What it is supposed to do is predict dates. The sheet has frequency of events per year (max 4) and last event date. I am thinking to have parameters: frequency, last date, q2date, q3date, q4date... so basically i want to type the function name (PREDICT) in the q1date column and have the function generate data in the q2date, q3date and q4date columns by using the cell reference in the parameters.... is that possible? I searched the net for solutions, i came across a 'ByRef' command but with no success to my function
View Replies!
View Related
Changing Cell Colors With Vba
I'm trying to have change if the date on it is passed "now()". I am already using the 3 conditional formatting fields and need this one and another one. Here are 2 problems I seem to be having. First - the code below only works if I change the date on the cell. I want code to either work with Worksheet Active or any other way so the user does not have to redo the date’s everyday. Second - a record might have conditional formatting already. Is it possible for the target cell in this code can show this color while the rest of the row shows the color of the conditional formatting? Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G1:G2000")) Is Nothing Then Select Case Target Case Is <= Now() icolor = 39 End Select Target.Interior.ColorIndex = icolor End If End Sub
View Replies!
View Related
Changing Cell's When Opening - VBA
I have a Excel Rota, which is storing data which is quite old, every time the spreadsheet is open you have to trawl down the page to findthe current date. I would like a piece of code to move down to a cell with todays date when the spreadsheet opens.
View Replies!
View Related
Find A Minimum Query
I have a set of worksheets all of which are identical and covered by first:last sheets What I am trying to do is come to up with a minimum figure for cell G30 With the proviso that it is a minimum of 30 as some of the entries in G30 are less than that figure. =MIN(first:last!G30) is not the answer as it gives an answer of zero.
View Replies!
View Related
Find Minimum Value Corresponding To Maximum Value
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions. So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value). A small sample of what the data set looks like: 2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3 Atlanta771773768761749756 Austin770786784779776776 Baltimore947948949950950953 Boston1,6471,6601,6541,6481,6301,613 Chicago998998996985984986
View Replies!
View Related
Find Minimum Value From A Data Group
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it. Basically the problem is this: In column "A" I have what I call point numbers for all the data entries. In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet). So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc). What I am trying to do is to come up with a table that shows a summary of the following: A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89). Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range. And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.
View Replies!
View Related
Find Minimum With Criteria - MINIF
I have following problem I have some table - 2 columns - in first is text in second is value. example: A 10 B 5 C 3 A 15 C 8 D 7 A 9 I need to use formula, which shows me minimal value for choosen text. I tried matrix formula like {=min(if(text table=choosen text; second column;0}, but it's not working
View Replies!
View Related
Conditional Count (Find The Minimum Value In The Range)
I am trying, without success, to create a formula that will refer to a column of data and do the following in one step: 1. Find the minimum value in the range, and then 2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted. For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)
View Replies!
View Related
Find Minimum/Maximum Excluding Zeros
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View Replies!
View Related
Find 2 Corresponding Values & Multiple The Minimum
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2". Compare the corresponding values in Column B, and multiply the lowest value by .2 I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
View Replies!
View Related
Find Minimum Value In Column Corresponding To Specific Text
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button. Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
View Replies!
View Related
Lookup Functions Find The Address Of The Minimum Value In That Range
I have a range of cells (say A1:D8). I wanna find the address (say $B$2) of the minimum value in that range. This command does it but it only works on a column. it does not accept a range spanning more than one column. =CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0))) this will tell me the address of the minimum value.. but in a column... I need a range of many columns. So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.
View Replies!
View Related
Clear One Cells Content When Changing Another Cell
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell. Cell D2 contains a validated list containing two items (Air, Vacuum). If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank. I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
View Replies!
View Related
Macro To Lock A Number Cells After Changing A Cell
I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list Just to put you in the picture Column B Is PO Number Column C Is Remarks Column D Date Column E Description Column F Supplier Column G Amount I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row. For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15. the macro must NOT be fixed to a particular row (in this case 15)
View Replies!
View Related
Changing Values Of Cells Based On Change Of One Cell Value
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16. Here 's my Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B16" Then If Target.Value = "TRUE" Then Range("B17:B25").FormulaR1C1 = "FALSE" End If End If End Sub This is not working! Nothing happens when I select "TRUE" in cell B16!
View Replies!
View Related
Changing Cells Colors In Relation To Cell Input - NOT Conditional Formatting Ques
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem... Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind... I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
View Replies!
View Related
Conditional Minimum Value Of Various Cells
I have two columns: column C has job functions, say engineer, cook, driver, etc; column D has salaries. I want to analyse the salaries: what's the min/max,median, mode, average values for each of the job functions. (e.g., if data in column C = "driver", then corresponding salary to be included in the data to be analysed.)
View Replies!
View Related
Solver Change Cell Precision
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 Replies!
View Related
VBA To Average Range Starting From Minimum Value & Ending At Maximum Value
I am using Excel to use stock ticker symbols such as FDX "Fedex", to return past volume and price daily performance found in Google Finance that will be manipulated with the current VBA I am working on that will result in a trading pattern. Each trading pattern will happen at a different range in the same column. I will be inputting hundreds of ticker symbols daily so using VBA and not having to enter formulas manually in each is greatly preferred as this will save me lots of time. I am stuck trying to find the average value of a range starting from the minimum to the maximum value. For instance, Range("H10:H20") may have a maximum value at range("H12") and a minimum at Range("H18"). I would like the average of Range("H12:H18"). Sub Fnd() Dim MaxStartPriceRange As Range Dim MinStartPriceRange As Range Dim MaxPriLocation As Double Set MaxStartPriceRange = Range("h11:h21") MaxPriLocation = Application.Max(MaxStartPriceRange) Set MaxStartPriceRange = MaxStartPriceRange.Find(MaxPriLocation) Dim MnPriLocation As Double
View Replies!
View Related
Changing Cell Color Based On Changing Values
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View Replies!
View Related
Determine Minimum Indent Level Of Selected Cells
I need to determine if any of the cells selected by the user have an indent level equal to 1 and if so then have them confirm the action. the below works well if the user only selects one cell but if the select 2 or more and any of the selected cells has an indent level greater than 1 it doesn't show the confirmation.
View Replies!
View Related
|
TRACKER
