share a spreadsheet

lfnfan · Post by **lfnfan** » Wed Sep 02, 2009 4:46 pm

I've adapted a copy of the Galaxy Generator spreadsheet (from the Wiki) to calculate time taken to get to a certain system along a certain route (to help figuring out if a cargo contract is do-able). I also adapted the Star Chart in that spreadsheet to highlight some potentially profitable trading combo's and other info (poor agri:rich ind systems / high TL / dangerous govts).

Thought people (traders particularly) might be interested in having a look at it.

After I've QA'd it, would someone mind holding it on their server / file sharing website for people to download?

Thanks

PhantorGorth · Post by **PhantorGorth** » Wed Sep 02, 2009 5:16 pm

I hold the original on my dropbox account so if you want I will host it for you.

The question then becomes where it goes in the Wiki.

Phantor Gorth

Wiki pages · Post by **Thargoid** » Wed Sep 02, 2009 5:40 pm

Why not just upload it to the wiki?

PhantorGorth · Post by **PhantorGorth** » Wed Sep 02, 2009 6:57 pm

Yes that works I just did that with my original spreadsheet. Please note to get it to work I had to make it into a zip file as an ods file is a type the wiki doesn't recognise and so won't accept it for upload.

Phantor Gorth

Hemlock · Post by **Hemlock** » Wed Sep 02, 2009 11:04 pm

Can I have a linky thingy please?

zevans · Post by **zevans** » Thu Sep 03, 2009 12:21 am

OK -
Wiki page
Zip now hosted on Wiki

Ivan · Post by **Ivan** » Thu Sep 03, 2009 8:03 am

I know it's a heinous idea, but can we have a native Excel version as well? Personally I haven't got anything that can handle ODS files and would rather not install a big application just for the odd thing.

lfnfan · Post by **lfnfan** » Thu Sep 03, 2009 10:02 am

No time for anything but a cursory QA of some G2 interplanetary distance calculations last night, and now I have no Oolite to hand. Could someone shoot me a few planet names and the distances between them, per Oolite, for other galaxies, so I can validate the spreadsheet calcs before I upload.

The spreadsheet is in .xls format. And needs the Analysis Toolpak installed. Not sure how to upload to the Wiki, but if it's OK to just go ahead and do so, I will give it a try....

Thanks

PhantorGorth · Post by **PhantorGorth** » Thu Sep 03, 2009 11:59 am

Ivan wrote:
I know it's a heinous idea, but can we have a native Excel version as well? Personally I haven't got anything that can handle ODS files and would rather not install a big application just for the odd thing.

Yes, you are evil person for suggesting it

. But I will convert it if you wish but it will have to be very late tonight. Please note I have done this before with this spreadsheet and after conversion Excel doesn't show the names of the worlds on the chart which is due to an Excel limitation. all the calculations work though.

lfnfan wrote:
No time for anything but a cursory QA of some G2 interplanetary distance calculations last night, and now I have no Oolite to hand. Could someone shoot me a few planet names and the distances between them, per Oolite, for other galaxies, so I can validate the spreadsheet calcs before I upload.

If your calculation is sqrt((x2-x1)^2 + ((y2-y1)/2)^2)) * 0.4 then it should be right. This is assuming that both x and y co-ords go from 0 to 255 (hence the divide by 2 on the y part).

I do have a version of Oolite Galaxy.ods with all the distance calculations between the worlds. I took it out before posting due the fact that I couldn't get the lines to plot (I didn't think it was sensible to do several thousand separate two point graphs on the one chart!) and also all the calclulations slowed the spreadsheet down significantly.

Phantor Gorth

lfnfan · Post by **lfnfan** » Thu Sep 03, 2009 12:44 pm

@PhantorGorth - the Oolite Galaxies spreadsheet totally rocks - much respect. It's fascinating to see how all the 8 Galaxies are derived from not much data, but lots of 'brains'.

The formula you quote is the one I have used. Mine looks like this:

=IF(ISBLANK(B3),"",ROUND(0.4*(INT(SQRT(((VLOOKUP(A3,Sys_Data,4,FALSE)-VLOOKUP(B3,Sys_Data,4,FALSE))^2)+((VLOOKUP(A3,Sys_Data,6,FALSE)-VLOOKUP(B3,Sys_Data,6,FALSE))/2)^2))),1))

Where A3 is the 'From' system, B3 is the 'To' system, and Sys_Data field 4 is X co-ord and field 6 is 2Y co-ord.

I previously made a 'brute force' spreadsheet for G1 which set out every combination jump from planet to planet (255 x 255) and then used VLookups based on that, but your work enables a much more elegant solution

Using the new approach, the resulting file size is smaller by a factor of 50 as well!

I was going to ask about naming the planets on the Star Chart - that's one thing I'd like help with getting implemented, but sounds like you already tried and drew a blank. Hmm.

@Ivan - you can do some testing on the Excel version I've put together, if you like!

I think I will just go ahead and try and upload it - If there are errors, I'm sure people will shout. I now have an account on the Wiki. Any suggestions where to put the link? I was thinking Planet List page.

Ivan · Post by **Ivan** » Thu Sep 03, 2009 3:22 pm

lfnfan · Post by **lfnfan** » Thu Sep 03, 2009 4:35 pm

Well, I think I figured out the Wiki. In the end I put the spreadsheet in the FAQ under the 'How do i make decent profits' question: http://wiki.alioth.net/index.php/Oolite_FAQ. Seemed appropriate. I added some suitable verbiage.

If anyone tries it - let me know how it goes. I will be doing some further validation as RL permits, on the route time-calculator and on the star chart.

Edit: Hey, I'm now 'Dangerous'

zevans · Post by **zevans** » Thu Sep 03, 2009 7:08 pm

Added a link for it to the "Oolite planet list" page too.

PhantorGorth · Post by **PhantorGorth** » Thu Sep 03, 2009 11:24 pm

Ivan wrote:
I know it's a heinous idea, but can we have a native Excel version as well? Personally I haven't got anything that can handle ODS files and would rather not install a big application just for the odd thing.

Here you go:Oolite galaxies.xls. Mind you could just use Ifnfan's as it is my spreadsheet with extras.

Phantor Gorth

PhantorGorth · Post by **PhantorGorth** » Thu Sep 03, 2009 11:46 pm

lfnfan wrote:
@PhantorGorth - the Oolite Galaxies spreadsheet totally rocks - much respect. It's fascinating to see how all the 8 Galaxies are derived from not much data, but lots of 'brains'.

The formula you quote is the one I have used. Mine looks like this:

=IF(ISBLANK(B3),"",ROUND(0.4*(INT(SQRT(((VLOOKUP(A3,Sys_Data,4,FALSE)-VLOOKUP(B3,Sys_Data,4,FALSE))^2)+((VLOOKUP(A3,Sys_Data,6,FALSE)-VLOOKUP(B3,Sys_Data,6,FALSE))/2)^2))),1))

Where A3 is the 'From' system, B3 is the 'To' system, and Sys_Data field 4 is X co-ord and field 6 is 2Y co-ord.

I previously made a 'brute force' spreadsheet for G1 which set out every combination jump from planet to planet (255 x 255) and then used VLookups based on that, but your work enables a much more elegant solution Using the new approach, the resulting file size is smaller by a factor of 50 as well!

I was going to ask about naming the planets on the Star Chart - that's one thing I'd like help with getting implemented, but sounds like you already tried and drew a blank. Hmm.

@Ivan - you can do some testing on the Excel version I've put together, if you like!

I think I will just go ahead and try and upload it - If there are errors, I'm sure people will shout. I now have an account on the Wiki. Any suggestions where to put the link? I was thinking Planet List page.

Real respect should go to Christian Pinder for reverse engineering the algorithms in the first place.

That formula looks right (I haven't counted brackets though, so I can be 100% sure). The only question I have about the distance calculation is that they maybe some rounding done before multiplying by 0.4. I have some vague recollection along those lines but I could be wrong.

Yes I did a 256 x 256 / 2 in my original version and created a list of links for each system. It was way too slow. (The divide by two is because you don't need to test system A to B and then test B to A so I only looked at systems where B is greater than A. (The pedants might actual spot that you don't test A to B where B equals A so it should be a divide by a number slightly larger than two.

)) The reason I was doing this was to look at giving each system a z co-ordinate that didn't break the links by putting them further than 7 light years apart, but that's another story.

Phantor Gorth