Calculate Which Items Needed to Maximize Returns

[SBoothe]

Now that I have begun doing research at my university, I decided it was finally time to practice with the powerful tools provided by the Office Student package. To do this, I create spreadsheets for a silly mobile game I play. So far, I have learned many useful things that have helped me create automated spreadsheets, though there is one problem I can't seem to crack.

In my specific case, I have 8 possible enemies I could fight.

>Each enemy is listed as "Level 1, Level 2... Level 8."

>Each enemy has a health value that increases at a nonlinear rate: 400m, 1b, 2.4b, 4.2b, 6.4b, 9b, 12b, 16.8b where m = million and b = billion. These are entered as the pure number (1000000000, not 1b).

>Each enemy costs a certain amount of Wealth: 200, 400, 800, 1200, 1600, 2000, 2400, 2800

>Each enemy awards an amount of Experience: 100, 200, 400, 600, 800, 1000, 1200, 1400 (1/2 wealth for the respective level)

>The damage output depends on a group of people. Both the number of people involved and the amount of damage they can do changes, but this is all calculated and stored into a SINGLE cell as "damage."

--

While I am able to manually calculate which enemies to attack to earn the highest amount of EXP (either overall, or for the lowest amount of Wealth), this is quite tedious. I would like to create a way to automatically calculate this.

I have tried using the Solver, though I have very little practice with it. I have had a very large amount of practice with using excel formula (including very ugly, nested ones), and am moderately well-versed in VBA.

Any possible solution that could automate this would be greatly appreciated. If it involves changing the variables in the cells, that's no problem - I can create an ActiveX button to reset it. As long as the values can be automatically calculated, I can fix any damage that occurs along the way.

--

I currently also have the KuTools extension installed, if there is a tool in the package I am unaware of.

Thank you.

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[SBoothe]

Thank you for such a quick reply! I have attached a sample with the data mocked-up. No additional plug-ins were used to create this spreadsheet, but if there are any problems please let me know, so I may fix them.

[Bo_Ry]

Not sure if I get this right or if this work with other Total ATK.

Please try at
M20 drag to the right

Please Login or Register  to view this content.

M21 Drag down
=SUMPRODUCT(SUMIFS(INDEX($J$5:$L$12,,ROWS(M$21:M21)),$I$5:$I$12,$M$20:$S$20))