To Use Solver To Determine An Input Parameter Of The BINOMDIST()
Apr 2, 2009
I'm trying to use Solver to determine an input parameter of the BINOMDIST() formula that provides the exact answer I want. It works for some inputs and outputs but not for all.
For example, I want 4 successes out of 7 trials to yield a 95% answer, meaning I need to itterate on the "P" input in the formula [=BINOMDIST(4,7,"P",TRUE) should result in .95]. Using solver it iterates successfully to .3413.
Now, change 4 to 6, and solver drives "P" to an answer that causes a #NUM result (P= 4.023), however I can manually input 0.6518 and I get the right answer.
I've tried adding constraints that won't let the input go outside the bounds of 0 and 1.0 but it does anyway.
View 9 Replies
ADVERTISEMENT
Aug 21, 2013
I need to make a fork in my code based on the type of data received from an input box launched from a right click and passed via the actioncontrol parameter.
The input box box is a range selector.
Dim seriesIdArray As Variant
seriesIdArray = Range(CommandBars.ActionControl.Parameter)
Generally, the user will have selected multiple cells as their range and I loop through using:
For j = 0 to ubound(seriesIdArray, 2)
However, if they only select one cell, I am getting back the value of that string in seriedIdArray, and that gives me a type mismatch error. I'll need to handle this a little differently, and I know how to do that part, I just don't know when I need to do this.
How can I tell whether they have selected one cell or multiple cells based on the value of the actioncontrol parameter?
I considered trapping the error type (13) and branching based on that, but then I end up with spaghetti code and I'm trying to avoid that.
I think I may need to create another more specific variable to take the action control parameter, test it, and then decide whether I should use an array or a range, but that's just a suspicion.
View 7 Replies
View Related
Apr 26, 2008
How do I adjust the VB code below so when the user clicks the command button they are prompted to set a limit.
i.e the code only runs til it hits the limit and stop (instead of running until it there are no more x in the columns)
Row 2, Col 9 stores the column numbers (listed as 1, 2,.....100) so thought this could be used in some way.
1. how do i set up the code so a user can enter a parameter value e.g 50 (which is the limit but also includes the values in column number "50")
2. how do i adjust the code so it take the parameter value and stop when it reaches column number "51"...
View 9 Replies
View Related
Dec 30, 2006
i have some data in array format and want to use that data in the solver. However, as per my understanding solver takes in only ranges. How can i use arrays directly? this is wht i am trying, but not working
entities = 1
spread = 3 + entities
ans = 5
SolverOk SetCell:=spread, MaxMinVal:="3", ValueOf:=ans, ByChange:=entities
View 5 Replies
View Related
Apr 3, 2014
I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
View 2 Replies
View Related
Mar 21, 2014
I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10
View 2 Replies
View Related
Dec 4, 2009
I have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;
View 3 Replies
View Related
Jul 31, 2013
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
View 2 Replies
View Related
Jan 28, 2013
I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.
I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.
I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.
View 7 Replies
View Related
Sep 18, 2009
I need to determine if the input date (Yearx) in a userform is a leap year. I tried doing this: Leap = Evaluate("MOD(Yearx, 4)") IF Leap = 0 then (show 29 days on my planner).
But no matter what date I put in, it generates "0" as the value for Leap, so indicates that February has 29 days. Obviously I'm not doing this right.
View 9 Replies
View Related
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
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
Sep 16, 2007
Here it goes.
My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.
Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...
If possible, i just want a command button that calls the parameter prompt.
View 9 Replies
View Related
Sep 15, 2009
I have a function which has to contain the name of a combo and use it with some given parameters, here's my example: ...
View 8 Replies
View Related
May 26, 2012
add parameter to this macro and at the end the process to index the highest result at the end, is that possible?
Here is the macro;
Code:
Sub Macro()
Range(Selection, Selection.End(xlDown)).Select
Calculate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range
[Code]..
to run 5000 times
View 8 Replies
View Related
Mar 2, 2009
Looking for a formula that gives the 1st - 5th day of a month, using the day name, e.g. "First Monday of March 2009" returns "2". Even better is if it could accomodate previous and following months, e.g. "First Monday of Next Month" returns "6". 3
View 9 Replies
View Related
Mar 20, 2009
I can run my query from within MSQuery something like
like %RESISTOR%
and it works fine.
However I cannot use %RESISTOR% on the excel sheet as a parameter.
is it possible to update a query using a parameter in this way? Maybe getting VBA to actually update the query manully.
View 9 Replies
View Related
Mar 30, 2007
I want know how to pass a parameter through a cell for date in the following url
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=10
View 9 Replies
View Related
Mar 6, 2014
I have a a table at the top of my worksheet which is a breakdown of performance. (Table 1), i would like Table 2 to roll up performance using a =SUMIFS calculation. However number of rows in table 1 may vary.
View 2 Replies
View Related
Jan 8, 2009
is there anyones know what's the meaning of 1 in this formula
MATCH(1,('Data Issues'!$A2=Sheet1!$A$2:$A$68)*('Data Issues'!B2=Sheet1!$B$2:$B$68),0)
View 2 Replies
View Related
Mar 12, 2014
Last time i got macro from this forum how to import files automatically. I am importing the data from the specific folder.In code itself we are hardcoding that file names.But in that folder i have lot of files is there any option to pass any parameter value.That means each time it will asks for filename(Prompt).Once you give file name it will automatically load the data.
[Code] .....
View 6 Replies
View Related
Oct 31, 2008
I have a macro in which I will trigger it to run automatically, under windows scheduler. Eg:
"D:DataMacro.xls".
I am expecting to pass parameter in such way, which I did for my other exe application.
"D:DataMacro.xls" parameter_1.
I want the macro to read the parameter_1 in such way.
View 9 Replies
View Related
Mar 16, 2009
I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?
View 3 Replies
View Related
Mar 20, 2009
How can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies
View Related
Mar 26, 2009
I want to pass the name of the routine as a parameter.
View 6 Replies
View Related
Jan 18, 2010
explain or provide a link to info about the .NumberFormat parameter?
I am using:
View 4 Replies
View Related
May 6, 2012
What is the best way for my UDF to return an error to the calling worksheet if it detects an invalid parameter?
In the past, I have usually set a breakpoint so I could check the the values and the logic. Other times, I return an invalid result, like 0 or -1.
I am working on a UDF now that is called hundreds of times. The workbook is a work in progress so I am constantly making changes to the UDF and the calling cells. Periodically, I screw up and do something that causes every call to get an error (like divide by zero).
View 3 Replies
View Related
Aug 21, 2008
I've written a function to delete the charts on a worksheet: ....
View 9 Replies
View Related
Feb 1, 2010
From Macro1, I want to pass a reference to a sheet. In Macro 2, I want to select that sheet. Here's what i have so far but I'm getting a "subscript out of range" error
Sub Macro1()
Macro2 "Sheet1"
End Sub
Sub Macro2(sheet As String)
Worksheets(sheet).Select
End Sub
View 9 Replies
View Related
