Need Help with Formula!

Captkraken33

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
does it need to be a formula or can you use VBA?
 

Captkraken33

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Chart.PNG
    Chart.PNG
    217.5 KB · Views: 6

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • Chart2.PNG
    Chart2.PNG
    239 KB · Views: 2

Captkraken33

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I can send you my workbook file if it would help to see what im doing better
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,480
Messages
5,706,638
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top