Excel AKICOLJ

[philmophlegm]

How do I do this in Excel?

I want to enter a value in the range 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F (or similar) in one cell, and then in another cell show the entry from a table that corresponds to this value.

For example, I want to enter the value 'B' in the 'Atmosphere' column and then have my spreadsheet show in the next cell that Atmosphere B is 'Corrosive'. If I had put in '5', it would show 'Thin'.

Comments

[foradan.livejournal.com]

Is that a Traveller world generator in a spreadsheet?

[philmophlegm.livejournal.com]

Almost. Actually, there are plenty of those available online.

No, what I'm in the process of doing is designing a cargo / freight / passengers / etc available spreadsheet for Traveller. The idea being that my spreadsheet will have a tab for each world in the campaign that the PCs will visit and after that all I will need to do is enter the UWP (Universal World Profile if you recall) and the spreadsheet will work out a whole range of stuff such as:

Goods produced on that world (down to a fairly detailed level, which some of the newer supplements like this one go into): http://www.mongoosepublishing.com/rpgs/traveller/core-rulebooks-accessories/book-7-merchant-prince.html

Freight available to ship for each type of good

Cargo available to buy for each type of good, with price

Passengers available, with destination(high passage, mid passage, low passage)

Mail available to ship, with destination

Demand for each type of good, with price

Brokers

Ship maintenance facilities at starport

Fuel prices and types of fuel available

Etc.

The urge to do a spreadsheet has been brought on by my not being entirely satisfied with any of the trade systems in the many versions of Traveller. Most fall victim to the 'golden pair' fallacy (find a pair of opposite worlds right next to each other, one low tech and agricultural and one high tech and industrial and just make lots of money buying cheap food on world A and selling it at a big profit on world B where you buy cheap tools and sell them on world B, rinse and repeat). The golden pair shouldn't work in the long run (i.e. it should already have stopped) since people will realise that they can do this and will move in. That increases demand for food on A (increasing the price ceteris paribus) and increases supply of food on B (decreasing the price there ceteris paribus) and therefore squeezing the profit that our plucky free trader crew can hope to achieve.

The only Traveller trading system that doesn't fall for the golden pair fallacy is GURPS Traveller: Far Trader (possibly because that was written by an economist). However, there are other aspects of that which I don't like, hence the desire to write my own. And if I'm writing my own, I don't want to have to make hundreds of dice rolls to determine quantities and prices every time the PCs arrive at a new world. The spreadsheet should remove the dice rolls.

[king-pellinor.livejournal.com]

Can't you set up a smoothing system, such that the characteristics of nearby worlds are brought into account? So a high-tech industrial world next to a low-tech agricultural one will mean that the effective tech level of the lower one will for trading purposes come up quite a bit, as will the agricultural level of industrial world?

That is, the effective demand level for industrial goods of a low-tech world will be relatively low if there's a handy source of high-tech stuff? You might need some sort of relational database for that sort of thing, though.

[king-pellinor.livejournal.com]

Actually, you could fudge it fairly easily. Give every jump route a number, and say which routes each world is on. The effective demand for each good on each world could then factor in the supply of that good from all worlds with the same jump route number, to reflect the fact that demand is almost certainly being met to a large extent.

[philmophlegm.livejournal.com]

That sounds like a very good way to fudge it. Actually, that sounds sensible enough that I wouldn't call it a fudge. Your fundamental point about taking into account the characteristics of nearby systems is a very good one - in fact, it's one of the things I wasn't satisfied with in any of the existing systems. I reckon it could be done with clever Excel formulae and tables rather than having to use anything fancier, regardless of my what my SQL-using wife might think...

[king-pellinor.livejournal.com]

I think that once you start getting into lots of tables, then something that works in more than Excel's two dimensions starts to become increasingly attractive :-)