Need Help with Formula!

[Captkraken33]

So I'm not sure if this can be done or not but what I'm trying to do is take a value from one cell and subtract from it until it reaches zero and then start subtracting from another cell. So say I have 2 boxes that hold 50 pounds in B1 and C1. I then In column A have different items varying in weight. I want to check and see if the item in A1 will fit in box 1 in B1 and if it will then deduct the items weight from the box. Then continue going down the list in A1 deducting items until B1 reaches 0. If the item wont fit in B1 then I want it to check and see if it can be deducted from C1 and if it can then do it there. Once B1 is 0 I want C1 to pick up where B1 left off in column A until all items in Column A are accounted for. I hope that makes sense!

Thanks ahead of time!

 

[vw412]

does it need to be a formula or can you use VBA?

 

[Captkraken33]

To be honest I'm not sure what VBA is. What I'm trying to do is use solver to find the best optimal weapon combo for a game that I play. If you look at the attached picture, I'm Using solver to get the MAX DPS (AF3) by changing the values in the S column. Once solver changes these values i then have formulas to fill in the other desired info. I use the following constraints:

1. The HP used (Column AC) has to match the hardpoints available that I set in columns V-AA. Those hard point values come from the S column once solver changes them.
2. The Tonnage used (AF) has to match the Available tonnage (AE). Again once solver changes S it gets multiplied against column H.

The part I'm having issues with is that there is a limited number of slots in each section of the mech and each weapon system takes up slots. Once solver changes the value I can set the total amount of slots constraint but I cant force it to maintain the number of slots per limb. As you can see in row 5 there is a limited number of slots that each section can use. I'm trying to get a formula or something to make it to where those slots are full it moves to the next available section that has slots available.

 

[vw412]

in your original post you referenced A1, B1, and C1. Can you relate those to the screenshot you attached above? I am getting lost in the terminology you are using from the game.

 

[Captkraken33]

So basically B21-S21 is a table of damage for ballistic weapons. In the table on the right U6-AC6 I have a certain amount of ballistic weapons that can be selected. Each of those weapons that become selected by solver have weight and slots that they occupy (Columns G/H). Each section from the table on U6 has a certain amount of slots that can hold these weapons. So A1 would be column G and B1 and C1 would be V5 and AA5. Once all the available slots have been filled in V5 I want the other slots that need to be used to come out of AA5. I have also included a Screenshot of my solver so you can see what my constraints are.

 

[Captkraken33]

What is happening is once something is selected in Column S its taking that slot amount and applying it to both V5 and AA5. I want it to apply it to 1 until its full and then the other instead of the same value being applied to both.

 

[vw412]

I am not sure Solver is the tool to use. If I can work up a VBA macro to do this, what would be the trigger to run this macro? You say "once something is selected in Column S", does that mean you will make a selection of a particular cell in column S to cause the calcs on V5 and AA5 to occur? I notice there are some values in Column S; should the routine use those values to compute V5 and AA5? I think what you want to do is very doable, I just need some guidance how to set it up.

 

[Captkraken33]

Solver is doing the selections in Column S. Basically all of the calculations are based off what is selected in S. So I choose for solver to give me the highest DPS value by changing Column S and then multiplying what it changed to get the DPS value. I then use the constraints for the weight and slots to further specify solver to give me a weapon combination that fits within each section in the table in U6. Basically a mech has 5 areas that a weapon could be in the right arm, right torso, head, center torso, left torso or left arm. Each area has a specified number of hardpoints (weapon slots). Each weapon then has a slot requirement and weight associated with it. I'm using solver to give me a weapon combination that gives the most DPS while still being able to fit within all the constraints.

 

[Captkraken33]

I can send you my workbook file if it would help to see what im doing better

 

[vw412]

I will look over the problem and see if I can help. I am not much of a Solver user so I will need to learn that also. I tend to look to VBA for problems like this. It may take a couple days but I will see what I can do.