LinkBack URL
About LinkBacks
Thank you to everyone that helped!
Thank you to everyone that helped!
Last edited by CooledLead; 08-08-2019 at 11:04 AM.
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
You could use solver with a binary grid.
Bin_Grid.jpg
solver result is not obvious as none of the lowest values are selected (range E4:G4) but the total cost for the sum of cars will be the lowest (see object f. C20).
Alf
Last edited by Alf; 08-01-2019 at 01:22 AM.
I have attached what should be highlighted given this scenario. I just need it to be automated now
I am not sure I understand. I have attached an excel file with the scenario that I've created, however, I now need it to be automated in highlighting the correct cells.
Thank you! I have uploaded a sample excel file if anyone would be willing to help.
Hi JeteMc
Her is the file & setup you asked for.
Alf
I really appreciate the help. However, this solution still requires manual input into the binary grid. I need it to analyze the cost grid and automatically tell what locations on the cost grid give the smallest cost.
There may be less manual input than you are thinking.
I took the liberty of adding a formula to L12:P12 that would get the number of cars needed at each location from the already entered B17:B21.
The formula is: =INDEX($B17:$B21,MATCH(L4,$A17:$A21,0))
So all that the user needs to do is to select cells L5:P9, type the number 1 and press the Ctrl and Enter keys then open the Solver > Select 'Solve' and 'OK' (as Alf has already it set up)
I also added a very simple conditional formatting rule (=L5 light green fill and green font) to the grid B5:F9. I left the original cell highlighted so that you could compare.
One benefit of Alf's method is that the choices it made save $0.24.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Not really, from JeteMc you got the index / match formula that sets up the demand for cars. Then you clears the range L5:P9 and you run solver. That's all there is to it.this solution still requires manual input into the binary grid.
This setup is a general model that could be used if you do have a specific choice in order to find a max, a min or a specific value. This you decide by setting the objective to what you wish to achieve.
Alf
Thank you so much for your response. I think Alf's method works now that I looked at it more, however, now the trouble is if I have for instance 5 cars to pick from but only need to send 4 away, the solver still populates the cells for 5 cars. I guess I need it to exclude the most expensive car. How do I change that? Again thank for your help.
This is where you change the demand line. Solver model is setup to this constraint:now the trouble is if I have for instance 5 cars to pick from but only need to send 4 away
i.e. the number of cars selected should be equal to or greater than the demand. Since the object function is set to minimum solve will pick the smallest number of cars that meets the demand because adding one extra will increase cost.
Please Login or Register to view this content.
So you could of course change this line to
i.e number of cars selected should be equal to the demand and you will get the same solution as before. But occasionally there can be advantages in setting a constraint "equal to and greater than" as this may produce a solution depending when equal to will not make it possible for solver to find a solution.Please Login or Register to view this content.
You specify the location the car should go to in your model and this determines the car cost and solver will give you the cheapest combination to meet the demands.exclude the most expensive car
Alf
Sorry it works like you had it. I entered one of the constraints in solver incorrectly. Thank you Alf and Jetco for your help!
You are welcome and thanks for feedback
Alf
Actually I lied. No I have the problem of column A having a special calculation. I am using an IF statement like IF(Long Equation,0,Long Equation). Problem is that Solver does not recognize or use the returned zero. I don't know if I need to change the data type or put "" around it. Thank you again
The solver simplex LP engine does not work with "IF" statement, try to change Solving method to "GRG Nonlinear" and see if this works instead.I am using an IF statement like IF
If not upload your file with the special calculation so forum members can have a go at finding a solution for you.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks