Calculating the combination of items considering item statistics

Hello World! I'm brand new to this community so please let me know if solutions to problems of this nature should be sought elsewhere. I have not done much reading through other topics but I get the impression that many of the people seeking help here are looking to help for professional or real world problems. I warn you ahead of time, this is not my case. I am trying to calculate the best combination of items in a game based on a few variable factors while accounting for the items statistics. I imagine this will require a matrix of some sort, but I have no idea how to set that kind of thing up. Here is what I would like to use excel to calculate: There are 3 classes of items: weapons, protection, and vehicles. The pertinent statistics I would like to account for each item are upkeep, attack, and defense. The rules of the game dictate that each member in your mob is assigned one weapon, one protection, and one vehicle item. To be able to afford these items, you must have a certain level of income to cover the upkeep of the items. I would like to use excel to determine, based on a mob of size X and an income of size Y what the optimal combination of items is. I would like to calculate the best combination of items based on the total value of attack + defense. To clarify, if I had a mob of 100 with an income of $1000, I would need a total 100 weapons, 100 protections, and 100 vehicles and I want my excel calculator to tell me how many of each item i should buy. I would greatly appreciation any direction in setting up the solution for this equation. I know it is a pretty trivial request considering the real world lack of importance, but would really appreciation some help. Thank you very much. Also, I tried to describe all of the pertinent information but if I have left out anything important, please let me know and I will be happy to add an update. Thank you.

Here is a visual representation of what I'm looking for..


WEAPONS
Name Upkeep Attack Defense
weapon a 200 15 12
weapon b 100 14 13

PROTECTION
Name Upkeep Attack Defense
item a 150 10 15
item b 125 8 12

VEHICLES
Name Upkeep Attack Defense
vehicle a 400 20 18
vehicle b 250 16 14


Mob Size: X (lets say 50 for this example)
Income: Y (lets say 10,000 for this example)

I want the calculator to tell me...

32 weapon a and 18 weapon b
25 protection a and 25 protection b
12 vehicle a and 38 vehicle b

I'm looking to account for several items in each category. THANK YOU VERY MUCH TO ALL WHO ARE WILLING TO HELP!

Hi, how could determine what is best when it comes to a vehicle or a weapon?

something like this?

Henry thank you for your prompt response. This is close but not quite. What I need is for the total number of items in each category to be equal to the mob size. In your file if i put in a mob of 100 and a very high income, it just tells me to buy 100 of every item (100 weapon a, 100 weapon b, 100 item a, 100 item b, etc.). For a mob of 100 with an overly high income this calculator should always tell me to only buy 100 of the best items from each category (100 weapon a, 100 item a, 100 vehicle a). What I really need to calculator to account for is a scenario with a mob of 100 with an income at a level that requires the calculator to make decisions on how many of each items to purchase (20 weapon a, 80 weapon b, 30 item a, 70, item b, 50 vehicle a, 50 vehicle b). To answer your question about comparing weapons to vehicles for example, the priority should elect the item which supplies the highest value of attack + defense while making sure that enough items in each category are purchased to cover the total mob size, while maximizing the total attack+defense value for all items combined. Thanks again

Yea, I know these are just random item statistics i made up in two seconds, in actuality none actual items in a like category will have the same total attack + defense

Is there any way you can make this work for a much wider range of variables? Say up to 10,000 mob and up to 50,000,000 income?

And sorry to not consolidate my thoughts into one post, but I am not seeing where i can select tools>solver>solve

Okay, once again, sorry for multi posting, the solver add in is not a default feature in excel 2007, i figured out how to add it on and it seems to be working correctly from what i can tell, thank you so much for this, the last question I have is with respect to inputting the actual item with their statistics. I am looking to add between 5 and 10 for each category, how can i go about this while maintaining the integrity of the calculations