Excel Ship Designer

[Arkathan]

I am testing out a Ship Designer in MS Excel for personal, non-commercial use.
If anyone wants to point out errors - politely - feel free to take a look.


New version is here. July 2021 - July 8 update: Numerous changes up to the Sensors tab. Will be working on the Weapons tab next.
July 26: New Weapons Tab - .1 fixed Record sheet issue; .2 Fixed an issue with HP02 and HP03 Cost calculations
July 28: Bug fixes
July 29: Updated Screens Tab, bug fix
October 8: requested changes to how the crew calculates broke the monthly salary calculation when added a column. That's fixed.
November 18: Fixed an error that caused you to pay for a grav screen whether you bought one or not.
November 25: Fixed an issue with the calculations for cargo cranes and loading ramps.
December 17: Found an issue with single laser turrets and the SSD page.
January 4 2022: Updated Stealth Jump to conform with Highguard Errata. Now uses one Mod slot.
January 8: Corrected an issue with Sensors on the SSD.
January 21: A typo in a drop down list affected mixed weapon turrets
March 14: Fixed maintenance on Ship's Info tab and the Record Sheet. Fix for compatibility on Weapons tab power formula
March 15 - Fix for the fix on Fix for compatibility on Weapons tab power formula
August 6 - Implemented many of the changes from High Guard Update 2022... still a few missing, such as Hop and Skip drives.
August 7 - Fixed new function of Intellect software. Added Battle Network. Corrected drop downs on mixed turrets. Corrected Sick bay for new 3 patient limit. External link removed
August 12 - The new stats for Ion Cannon Barbettes from today's update to HG2/U2022 has been included/ M-Drive TL's match update
August 14 - Pressure Hulls (thanks Spartan159), Common Area Options. A few other fixes, like stealth jump. Concealed Drives replaces stealth for M-Drives. Armor now calculates correctly with sphere and streamlined hull configurations.
August 18 - Implemented Gauss and Laser PBDs. Added Battle Loadout in parentheses for power on the Record Sheet. Hopefully will help when determining if/when you need to shut off systems due to damage. Fixed Mod calculations for drives/power plants so that costs for increased or decreased drive/plant volume is not calculated, only the cost of the mod, per HG examples.
August 20 - Corrected small craft barbettes for the new 3 firm point usage.
August 21 - Form tools removed, since they do not work on free versions of Excel.
August 25 - Fixed PDBs not displaying correctly on SDDs. Fixed Power sometimes not calculating correctly on Record Sheet after adding Battle Load.
August 26 - Big bridges should now work properly. SSD Critical Chart uses Core 2022 chart now. Made the Critical Chart more legible.
September 6 - Added TL 16 Computers and sensor options from Behind the Claw.
September 7 - Fixed issue with the first three hardpoints and power mods.
September 25 - Added Stations, Modules and research facilities.
September 27 - Small Craft barbettes no longer cost three times the listed value.
September 30 - Small modules can hold big weapons without throwing errors.
October 20 - Non-Excel compatibility issue fixed with Grapples and Forced Interlink Apparatus.
October 27 - Mineral detection suites now take up space, as intended.
February 25 - Fixed issues with cargo section. Automated reduced crew requirements on large ships.
March 3 - Fixed Agriculture factories for stations (and big portable industrial ships).
March 14/15 - Implemented Traveller Companion Ship Automation and limited data mining to the ship's main computer level. J7-9 control programs added.
March 18 - Implemented Advanced Repulsor Bays (HG2022-pg34)
July 21 - Cannonades and the GP Mass Driver from the Companion Update 2024 are implemented.
July 29 - Command bridges add 40 tons; Point Defense Batteries do not automatically add gunners to crew requirements.
August 4 - Fixed an issue where small craft firmpoint barbettes use three times as much energy as they should.
August 11 - Corrected tables in the weapons tab returning out of range TL.
August 12 - Fixed an error that crept into some crew calculations.
August 13 - Corrected prices on weapon bays, changed the Aux bridge calculation and clarified the Add Command Bridge fields. Modular hulls now cost less per dTon as intended. All hull option costs are added on Record Sheet.
August 29 - Virtual Gunner in computer tab now subtracts from required gunners in the Crew Tab. Virtual Crew remains manual via the Custom Crew setting.
September 07 - Reminder for Virtual Crew added to crew page. Correction to Officer calculation.
December 12 - Fixed a circular reference in commercial crew calculations. Implemented Fuel/Cargo Containers.

Current Version: v2024.04.10

April 10 - Solar Panels are now supported. Small weapon mounts should comply with HG22.




Edit: Thanks to Another Dilbert, MingtheMirthless and DigrizJB for identifying bugs.

 

[AnotherDilbert]

Lovely!

Let's see if I can find something...
Note: I'm using an older version of Excel, that might cause problems.


"1-Hull"

Cell F4: All costs not included, range truncated.

Cells E15-H15: No formulas?

Note modular 100% of hull, inappropriate.

Don't default to "No armour", default to the default armour for the TL, e.g. crystairon at TL-12.
Don't present inappropriate armour types for the TL, e.g. Bonded Superdense at TL-12.
Present the armour types in a functional order, e.g. TL order, not alphabetically.
Don't use the selected armour value in the calculations in cell F8. Use a validated value instead e.g. MIN( D8, Y8 ).
Note that the validation for cell D8 refers to cell Y8, but cell Y8 is not shown. That does not help much.


Opinion:
You try to rely on static input validation that becomes obsolete when something changes, e.g. Armour value (set while using a different armour type) and modular tonnage (set while using a different size of ship).

That way the designer can't easily see the difference e.g. between a TL-12 ship and a TL-13 ship. You can't flip back and forth between TLs to see the difference, you just get errors.

It's probably better to separate what the user wants and what he gets into separate cells. E.g. cell D8 is the desired armour, calculate what is possible in a separate cell and give him that instead.

Separate data tables on a separate tab, that way you can insert rows when needed. Now you will find it very difficult to change the spreadsheet with new functionality requiring new rows.

LOOKUP statements with relative references to the data table are dangerous, copy-paste will destroy the formula.

Locking the spreadsheet with a password is annoying... I can't see what is going on, modify anything, add rows in case I want more components, or even hide rows not applicable to the current design. People have house rules...

 

[AnotherDilbert]

Tab "2-Drives"

Note: Separate Hull Size and Drive Capacity. Drives may need to be dimensioned for a larger size than Hull Size, e.g. if drop tanks, docking clamps, or external cargo is carried. See the Gazelle.

Tech advantage Light Weight is Reduced Size? Ships and drives are not measured in mass tons, but in displacement tons (a volume, size).
Cost of a drive is based on effective size, not base size. See Fleet Courier.
The M-drive should be 1.5 Dton × 70% = 1.05 Dton (correct) and cost 1.05 Dton × 2 × 150% = 3.15 MCr (incorrect).

The jump drive should not be allowed, and if allowed incorrectly calculated. It should be 12.5 Dton.

Incorrect drives propagate to the "Record Sheet" tab without comment:

You can use several different Tech advantages, e.g. 1×Reduced Size and 1×Fuel Efficient. Not supported by the spreadsheet. Yes, it makes the spreadsheet very complicated, but I have needed it sometimes.

Tech advantages should only be applied if appropriate for the TL? Otherwise the drives become incorrect if you decide to change the TL later on.

How do I add an M-drive AND a Reaction drive? They add up.

How do I add two M-drives, e.g. an M-drive-3 and an M-Drive-1 backup?

High Burn Thrusters are Reaction drives and use the same tech advantages.

High Burn Thrust not validated.

Note that all components can be protected by armoured bulkheads, not just drives. Fuel might be a prime candidate.

Note that all components using power can be protected by EMP Hardened, not just drives. 75% of all components required to be Hardened in Fleet Scale, not counted.

While the minimum jump drive is 10 Dt, it's hardly necessary for rating "0"? Especially for a 50 Dt hull?

 

[AnotherDilbert]

Tab "3-Pwr Plant"

Cost should be calculated on effective size, not base size, see last post.

Tech advantages only includes Budget and advanced drives, not prototype drives.

Power calculation inaccurate. The jump drive only needs power the turn it is used to jump, not always.

Collector inaccurate, should be 50 Dt × 6% + 5 Dt = 8 Dt.

The Collector should not have anything to do with Power, it only replaces the jump fuel. Despite the Power of the Collector, it's not included in the Power calculation?

 

[AnotherDilbert]

Tab "4-Fuel"

1 Dt minimum fuel only needed if you have a power plant. E.g. a battery operated ship does not need it.

Default should probably be 4 weeks power plant fuel, not 8 weeks?

High Burn Thrusters don't need any fuel by default?

Jump Drives should get some fuel by default?

Reaction fuel is incorrect? One "thrust-hour" (one thrust for one hour?) should be 2.5% of the hull or 50 Dt × 2.5% = 1.25 Dt, not 3.75 Dt? Full thrust (22) for one hour should be 50 Dt × 22 × 2.5% = 27.5 Dt? The calculation includes the M-drive number and minus the Hull size (parenthesis missing?)? The High Burn Thruster is not referenced.

Fuel Scoops should be automatic and free for streamlined hulls, so should cost nothing.

Fuel Scoops noted as incorrect in red for some reason?

Collapsible tanks should take up 1% of rated capacity of space, i.e. a 100 Dt tank should take an extra 1 Dt, I believe. See The Great Rift, Book 2: Reft, p52.

Cargo/fuel containers not included. See The Great Rift, Book 4, p21. Quite nifty!

Note that drop tanks are mounted externally, so adds volume to the ship, so affects drive performance, when carried. They may be carried through jump, so may be needed to be added to hull size when calculating drives and fuel.

Jump fuel can be carried in Demountable or Drop tanks, so they should probably be related to the jump fuel calculation?

 

[AnotherDilbert]

Tab "5-Bridge"

Cost of bridge is MCr 0.5 per (100 Dt, rounded up) so should be MCr 0.5 for a 50 Dt craft.

You may want several bridges, see Sub-Command Centres, Element Class Cruiser, p74.

See also Starship Automation, The Traveller Companion, p162.

 

[AnotherDilbert]

Tab "6-Comp"

You may want several computers, in case of breakaway hulls.

Software selection not validated against TL. Note that TL-14 software can't be run on a TL-12 computer, see Core, p104.

 

[AnotherDilbert]

Tab "7-Sensors"

You may want to have several sensors, each with its own array.

You can probably only have one array per sensor.

Sensor components not validated against TL.

Distributed array not validated against hull size.

Sensor actions limited by number of Sensor Operators (crew), not sensor stations. By default Sensor Operators can work on the Bridge. Only sensor operators that don't fit on the bridge needs Sensor Stations. It's undefined exactly how many people fit on a bridge, so it's a bit of a judgement call how many sensor stations you need. You can also use a (or several?) Sub-Command Centre (extra small bridge) for sensor operations, alleviating the need for extra sensor stations.

Enhanced and Improved Signal Processing don't stack. You probably want either or. Same for Mail distribution, Life Scanner, and Countermeasure Suites. How about a menu with all of the choices for the given type, e.g. "None", "Distributed Array", "Extended Array", "Rapid Deployment Array" as choices for array? Perhaps modified by Hull size and TL?

You may want several components of the same type, e.g. Countermeasure Suites, in case of damage.

Total sensor/EW DM not calculated. Needed to give the designer an idea of the performance he has just specified?

 

[AnotherDilbert]

Tab "8a-Weapons"

A Firmpoint is an alternative to a Hardpoint, but for small craft; Ships have Hardpoints, small craft have Firmpoints.
A Fixed Mount is an alternative to a Turret, usable by any ship or small craft.
Any small craft can use a Fixed Mount or a Single Turret on each Firmpoint.
Any ship can use a Fixed Mount or any turret on each Hardpoint.
Firmpoints should not be confused with Fixed Mounts.

Ships should not have Firmpoints available as possible mounts.

Small craft should not have Hardpoints (and hence double and triple turrets etc.) available as mounts. (Barbettes can be mounted on two Firmpoints.)

Note that weapons on Firmpoints (=small craft) have lower Power consumption (and range).

Components from the High Tech chapter (quad turrets, plasma-pulse, meson bays, etc) are not normally available in the OTU, and should probably be noted as optional, only available with Referee approval.

You may, optionally, use several spinal mounts.


It would be much simpler if you could first select a mount, then a suitable weapon for that mount, like in mixed turrets. First selecting a weapon including the mount, and then the mount again in column C, is just a PITA. It could easily be automated?


The "Armoured Bulkhead?" title cell shows red when too many hardpoints are used.

 

[AnotherDilbert]

Tab "9a-Optional"

Why "Installed" and a quantity? Just a quantity would much simpler and faster?

A number of airlocks are included free, at not cost or space (Sidebar, p22). Additional airlocks takes space and cost. Default the minimum size of 2 Dt?


Tab "9b-Optional"

Type of Capsule need not be determined at design or build-time, it's more like ammunition.

Onboard facilities are closely related to crew spaces and should be grouped with staterooms and common areas?

 

[AnotherDilbert]

Tab "10-Crew"

Small commercial ships don't need a separate person as Captain, the highest ranking crew member fill that role. So should be 0 for commercial ships.

Small craft have separate crew requirements.

Some crew categories should probably be rounded down, e.g. Maintenance and Admin, see Free Trader.

Sensor Operators should probably be added as a crew category, as they are needed at least on military ships.

Officers should be rounded down; "...per full 10...".

Gunners should be 1 per turret or barbette for civilian and 2 per turret or barbette for military.

 

[AnotherDilbert]

Tab "11-Staterooms"

Not "birth", but "berth".

Brigs and Stables have life support costs.


Opinion:

You really are determined to make my like as difficult as possible by not defaulting crew or accommodations... If I change the size, TL, or drive number of the ship, the crew numbers will be inaccurate and tabs 10 and 11 will have to be revisited and rebalanced. Every time I add a turret or small craft...

 

[Arkathan]

Thanks for being thorough and trying to break it. That's what I needed to root out more bugs.
One note, greyed out fields, like the Jump drive on a 50 ton vessel are not meant to be used.
It isn't hard coded out because people can use a hyperdrive for a non-standard Traveller game.
Also, on several posts you show cells with red fill. Those indicate errors. Like the TL is too high.
I'll take a look at the other areas you mentioned.

 

[Arkathan]

Note modular 100% of hull, inappropriate.

Hull modularity is coded into Data validation. It disallows values outside of 0 - .75 hull. Your older version may be an issue there.

Default should probably be 4 weeks power plant fuel, not 8 weeks?

If you change it to 4 and save your master, the default is 4 weeks.

 

[AnotherDilbert]

Note modular 100% of hull, inappropriate.

Hull modularity is coded into Data validation. It disallows values outside of 0 - .75 hull. Your older version may be an issue there.

I don't think it's a version problem; I set Hull size to 100 and modular to 50, which is quite OK. Then I changed Hull size to 50 and left modular unchanged at 50. As long as I don't change it, the input field for modular will not be validated and the spreadsheet will be perfectly OK with 100% of the Hull as modular.

Default should probably be 4 weeks power plant fuel, not 8 weeks?

If you change it to 4 and save your master, the default is 4 weeks.

OK, that will work.

 

[AnotherDilbert]

Thanks for being thorough and trying to break it. That's what I needed to root out more bugs.

Thank you for understanding, I was afraid I sounded too harsh.

One note, greyed out fields, like the Jump drive on a 50 ton vessel are not meant to be used.
It isn't hard coded out because people can use a hyperdrive for a non-standard Traveller game.

OK, that is probably good. A red validation flag wouldn't hurt?

Also, on several posts you show cells with red fill. Those indicate errors. Like the TL is too high.

Yes, I was deliberately trying to break stuff.

E.g. for armour I bypassed the input field validation by changing the armour type after I set the armour value. My point was that the desired value should be validated dynamically, e.g. in another cell, not statically in the input field. I.e. treat the user input as desires to be vetted and changed to what it possible, so use a separate cell to calculate a possible armour value, something like = MAX( 0; MIN( N(D8); Y16 ) ), perhaps even with a IFERROR( ... ; 0 ) around it. That way the user doesn't have to keep track of what are currently possible armour values, he can just input, say, 99 to get as much as possible, regardless if e.g. TL or armour type is changed later.

 

[AnotherDilbert]

Recreation of Heavy Fighter:

Bridge w Armoured Bulkhead:

Record sheet missing bulkheads:

 

[AnotherDilbert]

Rounding error in volume?

Cargo of 5.75 Dton not accepted:

Only 5.7 Dton accepted.


Yet the total ship is 0.05 Dt undertonnage:

 

[Arkathan]

Tab "11-Staterooms"

Not "birth", but "berth".

Brigs and Stables have life support costs.

Fixed. Thanks.

 

[Arkathan]

E.g. for armour I bypassed the input field validation by changing the armour type after I set the armour value. My point was that the desired value should be validated dynamically, e.g. in another cell, not statically in the input field. I.e. treat the user input as desires to be vetted and changed to what it possible, so use a separate cell to calculate a possible armour value, something like = MAX( 0; MIN( N(D8); Y16 ) ), perhaps even with a IFERROR( ... ; 0 ) around it. That way the user doesn't have to keep track of what are currently possible armour values, he can just input, say, 99 to get as much as possible, regardless if e.g. TL or armour type is changed later.

Thanks for that explanation.