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

[wellinghall.livejournal.com]

A vlookup table?

[wellinghall.livejournal.com]

Like the one I've just sent you

[skordh.livejournal.com]

glad you did that as I can never remember how to do them

[wellinghall.livejournal.com]

I use them a lot at work.

[skordh.livejournal.com]

I use Excel a lot but VLookups only very occasionally. Which means that every time I use them I need to check how to do so!

[skordh.livejournal.com]

what he said

[andrewducker]

Yeah, a VLookup. If you have your values from A1->A10 and the things you want to lookup in B1->B10 and the location of the value to lookup is in D1 then you want:
=VLOOKUP(D1,A1:B10,2,FALSE)

The "2" is the column of the answer, and the "FALSE" says "Don't look for an approximate match, give me an exact one".

[king-pellinor.livejournal.com]

The 2 is inclusive counting, remember - you count the column you start with, so this value should always be at least 2.

You can also do an HLookup, with values in A1-M1 and targets in A2-M2, but these are less common. But handy if you have lots of tables, which I know you might... ;-)

[bunn.livejournal.com]

He has a lot of tables. I offered to show him how to do them in MySQL, but I was scorned. SCORNED!

[king-pellinor.livejournal.com]

You sound furious :-)

[philmophlegm.livejournal.com]

Excellent - thanks all.

[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 :-)