UWP data spreadsheet question?

[rogermccarthy]

Am playing around with some UWP data in an excel spreadsheet and amongst other things want to work out some trade volumes based on distance, starport, Pop, TL etc.

(yes I know this has been done using GURPS Far Trader but I am working in CT and given the different UWP's can't see any easy way of porting it over even if I wanted to).

Can anyone less mathematically challenged than myself suggest an excel formula that allows you to calculate jump distances based on the 4 digit hex codes?

Obviously it's clear looking at Yres in 1802 that Kinorb in 2202 is four hexes away because there is a +4 difference betwen the first two digits of each code.

However how does one (or indeed can one)calculate that Louzy 1604 is three hexes away from Yres 1802 and 6 hexes from Kinorb 2202 without looking at the map?

Assuming that such a formula can be generated how would I go about turning a simple list of world names and location codes into a travel distance matrix?

I am fairly happy playing with excel formulae, but am somewhat rusty on pivot tables etc

 

[Keklas Rekobah]

Ngi! Trigonometry!

Simplest answer:

If the two locations are in the same subsector, then take the square of the difference in the horizontal distance and add it to the square of the difference in the vertical distance, and then take the square root of the sum.

Distances are in parsecs or "hexes".

For instance, if Kvetch is in hex 0101, and P36-020 is in hex 0507 of the same subsector, then the distance would be:

([(4^2) + (6^2)]^(1/2)) =
[(16 + 36)^(1/2)] =
[(52)^(1/2)] =

7.21 parsecs

Or in refspeak: "At least 2 jumps away."

Your results may vary...

-KR-

 

[tjoneslo]

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">=MAX(ABS((TRUNC(Start/100)+MOD(Start,100))-(TRUNC(End/100)+MOD(End,100))),ABS(TRUNC(Start/100)-TRUNC(End/100)),ABS(((TRUNC(Start/100)/2))-(TRUNC(End/100)/2)))</pre>[/QUOTE]Try this. The tricky part is hexes are not aligned vertically so as you move across rows, the distances get strange. This returns distances in hexes, not parsecs.

 

[rogermccarthy]

Thanks - that's one impressive looking formula!

So if I set up a big 'distance between' table with all my world names and location codes along the top and sides and the cell containing the Yres code is A2, the cell with the Kinorb code is B1, and the cell in which I want the distance in hexes to appear is to be B2, what will the formula in B2 be?

i.e. where exactly do the A2 and B1 locations appear in your formula?

 

[Fritz_Brown]

alte, "Start" would be A2 in your example, and "End" would be B1. The formula would go in B2.

 

[tjoneslo]

Fritz88 is correct. I used start and end labels because I haven't done any Excel programming in a while and I was converting the formula from Java.

 

[Fritz_Brown]

I'm still trying to figure out how it works, though, tjoneslo. Maybe I'll have time later today.

 

[robject]

Here's the Perl I use to determine parsec/hex distances.

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;"> sub _distance
{
my ($row1, $col1, $row2, $col2) = @_;

my $a1 = ($row1 + int($col1/2));
my $a2 = ($row2 + int($col2/2));

my $d1 = abs( $a1 - $a2 );
my $d2 = abs( $col1 - $col2 );
my $d3 = abs( ($a1 - $col1) - ( $a2 - $col2 ) );

# return max( $d1, $d2, $d3):
return (sort ( $d1, $d2, $d3 ))[2];
}</pre>[/QUOTE]

 

[Fritz_Brown]

Here's my formula:
=IF(SIGN(MOD(A3,100)-MOD(A2,100))=SIGN(TRUNC(A3/100)-TRUNC(A2/100)),
MAX(ABS(TRUNC(A3/100)-TRUNC(A2/100)),ABS(MOD(A3,100)-MOD(A2,100))),
ABS(TRUNC(A3/100)-TRUNC(A2/100))+ABS(MOD(A3,100)-MOD(A2,100)))

(I think I see where you're coming from now, tjoneslo.

)

 

[inexorabletash]

Perl's sort() function sorts as strings not as numbers, so that won't work once the distances get to > 9 parsecs.

You'll want to do:
</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">return (sort {$a<=>$b} ( $d1, $d2, $d3 ))[2]; </pre>[/QUOTE]or use a max() function.

For kicks, I played around with this in Excel. First with my own formula, then with Robert's tidier one.

Here's how to get started:

1. Fire up Excel
2. Alt+F11 to launch VBE
3. Select Insert > Module
4. Insert the following code:

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Function HexDistance(hex1, hex2)

col1 = Int(hex1 / 100)
row1 = hex1 Mod 100
col2 = Int(hex2 / 100)
row2 = hex2 Mod 100

a1 = (row1 + Int(col1 / 2))
a2 = (row2 + Int(col2 / 2))

d1 = Abs(a1 - a2)
d2 = Abs(col1 - col2)
d3 = Abs((a1 - col1) - (a2 - col2))

HexDistance = Max(d1, d2, d3)

End Function

Function Max(a, b, c)

If a >= b And a >= c Then
Max = a
ElseIf b >= a And b >= c Then
Max = b
Else
Max = c
End If

End Function</pre>[/QUOTE]You can now use HexDistance(XXYY, XXYY) in your formulas.

6. Enter some test data:
- In B1 put 2202, C1 put 1802, D1 put 2202
- In A2 put 1802, A3 put 1604

7. In B2, type the formula:

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">=HexDistance($A2,B$1)</pre>[/QUOTE]8. Copy/paste into cells B2...D3

This should give you the answers you're expecting - populate row 1 and column a with the various hexes, and you'll get a travel-times grid.

 

[Fritz_Brown]

VBE!? VBE?! I don't need no stinkin' VBE! Just type the above formula into the Excel cell where you want the result. A2 is the start point, and A3 is the end point.

(Course, that's assuming my formula works for all coords....)
Seriously, you don't need a macro, just the willingness to watch the parentheses and make sure you get them all in the right places.

 

[inexorabletash]

I think the point is that he wanted to use the formula in multiple cells - a whole grid of them, to be precise. And while you can copy the formula around, you first need to get the $'s right, and if you change the structure of the spreadsheet you need to grovel through the formula to update the references.

It comes down to whether you want the simplicity of no macros, or the simplicity of re-usable code. There's no right answer.

 

[rogermccarthy]

Used Tjoneslo's formula which seems to work fine for near worlds but breaks down over longer distances (first number after the world name is the distance the formula returns from Emape 0133, second is the distance I measured out using the Megatraveller map):

0133 Emape 0 0
0139 Raweh 6 6
0332 Gothe 2 2
0333 Mirriam 2 2
0433 Jone 3 3
0533 Penelope 4 4
0534 Karin 5 4
0538 Wonstar 9 7
0539 Froin 10 8
0632 Gohature 5 5
0637 Quhaiathat 9 7
0638 Lakou 10 8
0732 Iderati 6 6
0808 Quar 18 28
0930 Flammarion 8 8
1005 Ruby 19 22
1006 Emerald 18 21
1018 Choleosti 9 19
1020 Margesi 9 17
1106 Jewell 17 22

Of course distances beyond Jump-6 aren't that much use anyway so maybe there is not that much value in fixing it - even if it's possible.

Interesting exercise though - hexes are weird things.

 

[Fritz_Brown]

Hmmm, and my formula screws up some of those.... Back to the drawing board.

 

[robject]

Originally posted by Joshua Bell:
Perl's sort() function sorts as strings not as numbers, so that won't work once the distances get to > 9 parsecs.

You'll want to do:
</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">return (sort {$a<=>$b} ( $d1, $d2, $d3 ))[2]; </pre>

or use a max() function.

[/quote]Ah, that's why I had the { $a <=> $b } there. I took it out today. Brain rot.

 

[inexorabletash]

You might want to check on Ruby, Emerald and Jewell again - I'm getting 32, 31, and 32 respectively, both from the formula and the map.

Must be something about gemstones.

 

[rogermccarthy]

Hmmm may have miscounted on the map.

The formula for the Emerald cell that returns 18 is:

=MAX(ABS((TRUNC($A20/100)+MOD($A20,100))-(TRUNC(C$2/100)+MOD(C$2,100))),ABS(TRUNC($A20/100)-TRUNC(C$2/100)),ABS(((TRUNC($A20/100)/2))-(TRUNC(C$2/100)/2)))

Where $A20 is Emerald's value of 1006 and C$2 is Emape's 0133.

But as I said I am mathematically challenged - I can cope with basic excel formula but anything with this many brackets goes right over my head.

 

[inexorabletash]

I meant robject's formula; Tjoneslo's is busted.

Reducing it to a cell...

=MAX(ABS((MOD($A2,100) + TRUNC(TRUNC($A2 / 100) / 2)) - (MOD(B$1,100) + TRUNC(TRUNC(B$1 / 100) / 2))),,ABS(TRUNC($A2 / 100) - TRUNC(B$1 / 100)),ABS(((MOD($A2,100) + TRUNC(TRUNC($A2 / 100) / 2)) - TRUNC($A2 / 100)) - ((MOD(B$1,100) + TRUNC(TRUNC(B$1 / 100) / 2)) - TRUNC(B$1 / 100))))

(Note that you can safely remove the inner TRUNC in the TRUNC(TRUNC(xx/100)/2) calls and make other simplifications.)

Put source hexes in A2....A10 and destination hexes in B1...J1, paste this into B2 and then fill it into the rest of the grid.

 

[Gruffty]

OK then, people, what about formulae for getting Excel to spit out trade codes?

I need to be able to punch in a UWP, say (for example) X000000=0, and get Trade Codes of As, Ba.

Here's what I've managed all on my lonesome so far (assuming A1 is starport, B1 is size, etc):

=IF(B1=0,"As","") for asteroid belts

=IF(E1+F1+G1=0,"Ba","") for Barren worlds

=IF(D1>=2,"De","") for Desert worlds

=IF(E1>8,"Hi","") for High Population

=IF(C1=0,"Va","") for vacuum worlds

=IF(D1>=9,"Wa","") for Water worlds

Two problems have arisen so far:

Asteroid worlds automatically get a Va code, even though the MT rules (which I'm using for the Trade Codes) state that asteroids don't require the Va code.

I can't work out how to use letters in a UWP (like Hydro "A") without screwing up the other formulas.

Then I need formulae for Ag, Fl, Ic, In, Lo, Na, Ni, Po and Ri.

Any help would be greatly appreciated

 

[Flynn]

For both De and Va, you'll want to put in an additional check to make sure that the world's size is greater than zero. That way, you only output that code if the world is not an asteroid but otherwise meets your requirements. (I ran into the same problem myself two years ago, and that's how I got around it.)

Hope this helps,
Flynn