Spreadsheet to calculate what can be made based on available resources
January 7, 2020 3:41 PM Subscribe
Google Sheets: how would you program a way to calculate what you can make based on the resources you have available? This is for a game - more details inside.
I've really gotten into My Time In Portia, where the core of the game involves building machines and things using resources from the land. Sometimes these builds are multi-layered: you need to turn ore into bars, then turn bars into parts, then put parts together into a big build. (Not all of them are that complex thankfully)
What I would love to make is a spreadsheet where you can put in what resources you have and it'd tell you what you can make with it. I'd also like it if it could let me select what builds I'm trying to make and tell me what I need. The information I need is pretty well-documented, I just need to put that all in.
I'd imagine that it'd be something like "if X Ore = 3 AND Y Oil = 2 then X Gear = 1". The multi-layered builds would be tricky too: ideally it'd be able to tell me that I have enough A to make the amount of B I need to make C, or that to make C I need some amount of B and I need more A to make that happen.
Some things will cost in-game money, but given that costs fluctuate in game and some things can either be bought or harvested I'm not sure it's worth calculating for that, unless it's a build where one of the "ingredients" is a steady amount of cash or if we work out the base cost. It could be worth putting in information about where each resource can be found for reference.
I've seen similar sheets for other games (a Stardew Valley bundles one and one for the Harry Potter Go game) but I can't seem to find them otherwise I'd use them as research!
I've really gotten into My Time In Portia, where the core of the game involves building machines and things using resources from the land. Sometimes these builds are multi-layered: you need to turn ore into bars, then turn bars into parts, then put parts together into a big build. (Not all of them are that complex thankfully)
What I would love to make is a spreadsheet where you can put in what resources you have and it'd tell you what you can make with it. I'd also like it if it could let me select what builds I'm trying to make and tell me what I need. The information I need is pretty well-documented, I just need to put that all in.
I'd imagine that it'd be something like "if X Ore = 3 AND Y Oil = 2 then X Gear = 1". The multi-layered builds would be tricky too: ideally it'd be able to tell me that I have enough A to make the amount of B I need to make C, or that to make C I need some amount of B and I need more A to make that happen.
Some things will cost in-game money, but given that costs fluctuate in game and some things can either be bought or harvested I'm not sure it's worth calculating for that, unless it's a build where one of the "ingredients" is a steady amount of cash or if we work out the base cost. It could be worth putting in information about where each resource can be found for reference.
I've seen similar sheets for other games (a Stardew Valley bundles one and one for the Harry Potter Go game) but I can't seem to find them otherwise I'd use them as research!
You can then use that number to figure out what ingredients you’re lacking by using IF to figure out if an ingredient is the weakest link, then calculate the ingredient required for another increment, something like:
=IF(ROUNDDOWN(ORE/3)=GEAR),(GEAR+1)*3-ORE,0)
posted by zamboni at 4:57 PM on January 7, 2020 [1 favorite]
=IF(ROUNDDOWN(ORE/3)=GEAR),(GEAR+1)*3-ORE,0)
posted by zamboni at 4:57 PM on January 7, 2020 [1 favorite]
Similar to what zamboni suggests, I threw together a little Google Sheet that maybe starts to get at what you want to do. (You'll have to make your own copy to edit it.)
posted by BrashTech at 5:02 PM on January 7, 2020 [1 favorite]
posted by BrashTech at 5:02 PM on January 7, 2020 [1 favorite]
Here's a Google Sheet for Factorio, a marvelously complex game of automating resource recovery, defense against giant bugs, and a big-ass tech-tree up to rocketry and nuclear power. Instructions on the first tab.
posted by Sunburnt at 5:37 PM on January 7, 2020
posted by Sunburnt at 5:37 PM on January 7, 2020
This thread is closed to new comments.
For the GEAR example (untested):
=MIN(ROUNDDOWN(ORE/3),ROUNDDOWN( OIL/2))
posted by zamboni at 4:07 PM on January 7, 2020 [2 favorites]