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 RepliesI've been assigned a task of finding a combination of three or four machines. For example AD,AJ,AQ, and AB would equal 7 therefore it would be the best combination of workstations for that cell. However, I'm having an issue that if AD, AJ, AQ, and AB are being selected more than once.
My question is, how can I analyze all the data and determine the best combination given the relationships for each row given the column.
3 = Absolutely Necessary
2 = Extremely Necessary
1 = Necessary
0 = Do not associate
I want to take a shot at this spreadsheet exercise. Setup similar spreadsheet models.
Excel Exercise.xlsx
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.
The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.
The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.
can buy stock glass in several standard sizes. Specifically:
12x16
16x20
20x24
22x28
24x30
24x36
26x32
32x40
36x48
40x60
I use these stock sizes - to cut custom sized pieces. Example - if I have a piece that calls for a 15x18 - I will take a 16x20 piece and cut it down. I have all different kinds of size combinations that come up and am looking for a way for Excel to calculate the most efficient stock size to start with. Trying to do so using a series of Vlookups and if then's - but seems to be overly complicated and not always accurate.
I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.
How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.
Snippet attached:
Capture.PNG
Is it possible to calculate the current optimal fantasy football team from the attached data and constraints listed on the 'solver' tab?
View 2 Replies View RelatedI want to set constraints as an integer in solver. But I don't know how to do it.
View 5 Replies View RelatedI 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 Replies View RelatedI have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
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 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 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.
This 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'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 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'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?
I have this code attached, but only works if I have the word Phaser or WorkCentre.
I want to create a button on this spreadsheet - the button will be called z4 in the "Yr 9 ICT" sheet
it will include the following
rows Jane Bloggs 2
rows Jane Bloggs 12
rows Jane Bloggs 22
rows Jane Bloggs 32
rows Jane Bloggs 33
rows Jane Bloggs 42
rows Jane Bloggs 44
rows Jane Bloggs 66
What do I have to do to create this.
I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months)
115
224
Below are Company B's sales records for the past 36 months.
MonthSalesMonthSalesMonthSales...............................
I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies View RelatedSuppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.
And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.
Now one of the two:.....
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
What would be the best way to put together a cancel curve on an annual membership model?
View 5 Replies View RelatedI have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?