Crafting with Multiple materials

[Heimdal]

Hi everyone.

I'm trying to maximize profit.

I have a finite amount of 4 different materials, and a lot of different items that i can craft. I wish to make excel calculate max profit based on marketplace value. But I have a problem thats stops me dead in my track, because some items require multiple different materials, and so I simply cannot se how to make excel solve this.

	Amount
Metal Bars	48
Planks	103
Cloth	50
Leather	19

Item materials requirement	Metal Bars	Planks	Cloth	Leather
TOOL_AXE	2	6
TOOL_FISHINGROD		6	2
TOOL_HAMMER	2	6
TOOL_KNIFE	2	6
TOOL_PICK	2	6
TOOL_SICKLE	2	6
ARMOR_GATHERER_FIBER			8
BACKPACK_GATHERER_FIBER			8
HEAD_GATHERER_FIBER			8
SHOES_GATHERER_FIBER			8
ARMOR_GATHERER_ROCK	8
BACKPACK_GATHERER_ROCK	8
HEAD_GATHERER_ROCK	8
SHOES_GATHERER_ROCK	8
ARMOR_GATHERER_FISH				8
BACKPACK_GATHERER_FISH				8
HEAD_GATHERER_FISH				8
SHOES_GATHERER_FISH				8
ARMOR_GATHERER_HIDE				8
BACKPACK_GATHERER_HIDE				8
HEAD_GATHERER_HIDE				8
SHOES_GATHERER_HIDE				8
ARMOR_GATHERER_ORE	8
BACKPACK_GATHERER_ORE	8
HEAD_GATHERER_ORE	8
SHOES_GATHERER_ORE	8
ARMOR_GATHERER_WOOD				8
BACKPACK_GATHERER_WOOD				8
HEAD_GATHERER_WOOD				8
SHOES_GATHERER_WOOD				8

So based on the above data how do i make a table/sovler or even a VBA that can tell me the optimal number of items that i need to craft in order to maximize profit.

The item price will vary every single day, so i have a linked table that feeds me those number, but for the sake of you guys helping me, you can use these numbers.

TOOL_AXE	9.990​
TOOL_FISHINGROD	4.000​
TOOL_HAMMER	5.160​
TOOL_KNIFE	6.830​
TOOL_PICK	4.891​
TOOL_SICKLE	7.000​

I really appreciate if anyone could give me an example on how to solve this. Thank you very much! Ive used Excel in many years, so just a small example would do, then i could scale it myself. Thanks!

 

[Lee J]

Hi Heimdal,

I have created the file here, I believe it does what you want

Book6.xlsx

drive.google.com

I have assumed that once you have made the 'main' item you can then go and use the remaining materials to make more products.

 

[Heimdal]

Hi Heimdal,

I have created the file here, I believe it does what you want

Book6.xlsx

drive.google.com

I have assumed that once you have made the 'main' item you can then go and use the remaining materials to make more products.

Thanks Lee! I took your initial idea and delveloped it a bit. But thank you very much!

 

[Lee J]

NP