Not that anybody cares, but...

What I'm doing is assembling a spreadsheet of various data from the game 
X-Tension (the argonopedia website has a few errors and is lacking some 
other particularly relevant info)...you have space stations that require x 
resources to produce y product, and I'm trying to identify the most 
profitable things.

Each item has a minimum, average, and maximum selling price, which you can 
trade between NPC stations to make enough money to buy your own factory...at 
this point, if you buy all your factory's resources from NPCs, there's a 
minimum, average, and maximum cost per unit created (assuming you buy at 
those prices).

I have a set of columns with how much of each resource a station needs to 
produce however many items per production cycle, and was trying to calculate 
how many energy cells are needed per unit (energy cells being the lowest 
common item that everything else needs; a Crystal Fab for a particular race 
needs 16 food, 5 silicon wafers and 120 energy cells per 10 Crystals; the 
food item in turn needs 15 energy cells and 12 raw ingredients, the raw 
ingredient factory needs 5 energy per unit, and the silicon mine needs 24 
energy cells for each wafer, bringing the energy cost of a Crystal to 480). 
This way all the products can be compared vs. how long they take to make to 
see what's the best way to make money.

While I could have referenced everything manually, if I ever wanted to sort 
the sheet in a different way, everything would get mixed up...so (again 
using the Crystal Fab as an example) ended up with 
=(AJ60+W60*(INDEX(Stations,MATCH(W$1,Products,0),12))+AI60*(INDEX(Stations,MATCH(AI$1,Products,0),12)))/M60

as the formula (column AJ is energy cells needed, W is that race's food, 
Products is named list of column E, AI is silicon wafers, and M is number of 
items produced per cycle).  Average cost of a Crystal is 
=((AJ60*LOOKUP(AJ$1,Product,CrAvg))+(AI60*LOOKUP(AI$1,Product,CrAvg))+(W60*LOOKUP(W$1,Product,CrAvg)))/M60

with Product and CrAvg being named lists on another sheet that's just the 
products and their selling prices.

And this is pretty much how I've been wasting my time for a few weeks.  :P

-- 
Tim Cook
http://empyrean.freesitespace.net

Post a reply to this message