Ship Design Spreadsheet 2009

[sargon0]

wycca has asked for a working ship design spreadsheet here. Rather than continue that old post I decided to open a new one with my effort. I designed this some years ago but I have now added some missing items (double hull, 5-rack MB's) and changed miniaturisation formula to reject selecting items beyond your selected tech levels (gives #N/A). I use it occasionally to plan LR and other ship designs and have done some checks on the updates but let me know if you notice any errors.

Ship Design Spreadsheet 2009:
[EDIT ] V2 spreadsheet to correct miniaturisation limit to 50 levels for all items (not just weapons) as spotted by Cyneheard
[EDIT2] V3 spreadsheet to correct Special item miniaturisation to use correct field
[EDIT3] V4 spreadsheet fixing reserve fuel tanks & AMR on mediums getting #N/A at low tech levels
[EDIT4] V5 spreadsheet removing tech level check per Refsteel below but warns if using higher than tech level, plus minor fixes
[EDIT5 30Mar11] V6 spreadsheet fix missile cost & space plus melee weapon space to use correct miniaturisation table
[EDIT6 08Apr11] V7 add bio weapons, correct game manual stats for tech levels (Fusion bomb, Hyper-X) and for some missilex5 costs & size
[10Apr11] Minor update for Propulsion tech level 1 showing #N/A
[12Jul13] Re-add V7 as attachment since earlier link broken, redo 28Jul13 & 8Aug13 & 11Aug13. If attached file goes missing try following link.
http://realmsbeyond.net/forums/attachment.php?aid=628

[wycca]

That's pretty neat, what a staggering amount of work went into that, so many options! Thanks!

It works fine in OpenOffice btw.

[Cyneheard]

I'm impressed, this is incredible.

Minor tidbit, could lead to funny rounding artifacts: I see in the bottom, you've got the TL difference, and miniaturization ratios, for weapons and non-weapons. These figures are rounded to 2 decimal places. Is that how the code actually works? Especially for, say, the Death Ray, more digits would be more accurate...assuming that's how the game is designed, which I don't know.

Tidbit number 2: maximum miniaturization bonuses. For example, late late game (like TL99), colony pods only get so small. At TL99, your table has standard pods shrinking down to 42 space (10.50 cost sounds right), and I am pretty sure that there was not a large size difference with, say, radiated colony pods (70 space, 15 cost from the table). My saved game from the Silicoid Imperium here is at TL99: http://realmsbeyond.net/forums/showpost....stcount=13 I don't have the game anymore to check (willful choice. I am not allowing myself to contract a chronic case of OneMoreTurnitis).

[sargon0]

Cyneheard:
Minor tidbit: Yes this is how the game is designed. This miniaturisation table is straight from the OSG. You will see in some values I do calculate exactly without the table using POWER but then have to ROUND results to avoid being too accurate.

Tidbit number2: Good catch. I knew there was a limit of 50 levels of miniaturisation and had set this for weapons but not other items. I am not usually high enough to worry about this one! I have posted a V2 spreadsheet on original post with correct limit implemented for all items. Now your Colony bases are all size 168 at TL99 as in game. Thanks for the savegame.

Anything else let me know.

[sargon0]

Sorry about this but the Special item miniaturisation is using the wrong tech field in many cases. Problem of returning to a spreadsheet you created years ago and forgetting which formula can be dragged and which cannot. I have updated original post with V3 spreadsheet correcting this. Sorry to those who already downloaded.

Any issues noticed, let me know.

[wycca]

Thanks for the update!

[sargon0]

Found missing absolutes in one set of cells referring to miniaturisation table for specials on medium ships only. Results in reserve fuel tanks at tech level 1 or AMR before tech level 18 on mediums getting #N/A even though you can use them. V4 spreadsheet attached to opening post corrects this.

Well at least the errors are becoming more specific!

[RefSteel]

Thanks for building this (and continuing to support it!) - it's a great resource! Quick question: Can Artifacts finds (and tech thefts, etc.) sometimes fool it by skipping a tier? For instance, if I pull Controlled Tundra from an ART world before self-researching any tech in Planetology, I'm only at PLAN tech level 5 (6 for Tundra times 0.8 = 4.8, FLOORed to 4, +1 for starting tech = 5) but can still build ships with Tundra bases. Right?

[sargon0]

Absolutely correct! If you jump a tier by any means leaving you with a tech higher than your tech level, the lookup formulae give #N/A and the POWER formulae give negative miniaturisation! It's the opposite problem to Cyneheard's maximum 50 levels of miniaturisation, I need a minimum 0 levels of miniaturisation.

I will look to implement this but there are a few formulae to update! It does mean that you will lose the check that you are not choosing an item beyond your proposed tech level which normally would be an oversight. It will come down to the user to check their proposed design is using valid items for their proposed tech levels, although you could choose to use V4 spreadsheet normally and the new version when in this unusual situation.

[sargon0]

Well I thought about leaving 2 spreadsheets, one checking items were not above tech levels and one not, but that seemed to leave more to change if (when!) future fixes/enhancements are needed. In any case I did spot that engine size for specials (only on small for variable size specials) was not limiting to maximum 50 levels miniaturisation so needed fixing. I have updated original post with V5 spreadsheet which fixes this bug plus sets minimum miniaturisation of 0 thus removing tech level checks. It adds WARNING message if choosing item above tech level and shows INVALID if setting Maneuver too high for engine speed. You can heed the warning if you accidentally selected wrong item or tech level, or ignore the warning if you are sure you have set correct values ie. after tech tier jump.

Let me know if you find any issues.