UWP data spreadsheet question?

[Fritz_Brown]

Gruffty, you will need to install one of the tools. This will give you the DEC2HEX and HEX2DEC functions. Then, you can type in "A" in your UWP, and just use HEX2DEC(wherever) everytime you need to run a calc. Of course, you have to use HEX2DEC(wherever) everytime you run a calc....

 

[Gruffty]

Thanks for the help,guys!

 

[Gruffty]

OK, I've managed to work out the codes

. I'm still having problems with empty cells though. For example:

If the cell A2 (where the mainworld's size code is entered) is left blank, the formula below returns "As". If you enter 0 in cell A2, "As" is returned. Enter 1 or higher and the cell for the As Trade Code goes blank.

=IF(HEX2DEC(A2)=0,"As","")

It looks like Excel is treating an empty cell as having a value of 0.

Any ideas how I can get the "As" Trade Code to appear only when a size code of 0 is entered?

I'm having the same problem with the "Ba" Trade Code formula as well:

=IF(HEX2DEC(Q3)>=1,"",IF(HEX2DEC(R3)>=1,"",IF(HEX2DEC(S3)>=1,"","Ba")))

All of the other Trade Code formulae work fine, and leave empty Trade Code cells when there are no UWP codes in the UWP cells. As you enter or change the UWP, the Trade Codes appear and disappear according to the rules of p. 50, MT Referee's Manual.

I just need to debug the above two formulas then it's sorted.

 

[Fritz_Brown]

Insert IF(ISBLANK(A2),"", right at the beginning. (And, add a ")" at the end.)

Should work for both formulas. Excel does a few goofy things every once in a while (like assuming an empty cell is 0).

Edit: BTW, if you calculate a cell using the "" to make the cell 'blank', it won't work. "" actually counts as a character when all's said and done. end edit

 

[Gruffty]

Thanks for the help and advice, Fritz88 and Flynn. My spreadsheet is ready to R & R

 

[Gruffty]

My spreadsheet is up, running and working fine, thanks to you guys

I've tried putting some Spinward Marches, Solomani Rim and some other UWPs, and the Trade Codes all come out exactly as they should be.

There is one little ol' thing I'd like to tweak, though...

I'm using the following formula to calculate each mainworld's population:

=(10^E1)*A15

where E1 is the Population code in the UWP and A15 is the population multiplier from the PBG section of the system data.

This works great, i.e. I'm getting all the right numbers, as per p. 22 of MT Referee's Manual.

However:

=(10^6)*1

will always return a value of 1,000,000. That's as per the rules, but the numbers are a bit "bland".

I thought about using the RAND funtion, but can't get my head round how to link the RAND function into the population formula above, without screwing the entire calculation up.

I'd like the popultion numbers to look more like:

1,437,869 instead of 1,000,000 each time.

Has anyone got any suggestions as to how I can go about giving the population numbers a random/natural/organic feel, without screwing up the calculation? I also don't want the numbers to keep changing every time I fiddle with the spreadsheet.

 

[Fritz_Brown]

use:
=(10^E1)*(A15+RAND())

That should give you a number between A15 and A15+1. Of course, you might only want to go to a couple of significant digits, as no world is going to be able to realistically give out numbers below 4 digits. To do this, use:
=(10^E1)*(A15+TRUNC(RAND(),3))

 

[aramis]

Actually, worlds with 0 legal emmigration/immigration and pops under 10K will be able to snapshot pops to a person routinely, especially if TL 8+

You can also apply the trunc() at the other end
=trunc((10^E1)*(A15+RAND()),Max(4-E1, 0)) and thus truncate to
get 4 significant places.