Excel Ship Designer

Discuss the Traveller RPG and its many settings
User avatar
Arkathan
Mongoose
Posts: 130
Joined: Wed Jun 30, 2021 9:39 pm

Excel Ship Designer

Postby Arkathan » Fri Jul 02, 2021 3:28 am

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.

Current Version: v2021.7.d7

November 25 - Fixed an issue with the calculations for cargo cranes and loading ramps.

https://cdn.discordapp.com/attachments/ ... .7.d7.xlsx


Edit: Thanks to Another Dilbert for identifying bugs.

I made significant changes to the Drives page and Weapons page, and most fixes Dilbert suggested.

I'm also working on a Book 8 Robots spreadsheet to tide me over until Mongoose comes out with a ruleset.
Last edited by Arkathan on Fri Nov 26, 2021 4:46 am, edited 30 times in total.
AnotherDilbert
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 6:21 am

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?

Image
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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 7:19 am

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).
Image

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:
Image

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 7:37 am

Tab "3-Pwr Plant"

Image

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 8:19 am

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?

Image

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 8:27 am

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 8:38 am

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 9:01 am

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?
Last edited by AnotherDilbert on Fri Jul 02, 2021 10:54 am, edited 1 time in total.
AnotherDilbert
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 9:52 am

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 10:12 am

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 10:24 am

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 10:40 am

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...
User avatar
Arkathan
Mongoose
Posts: 130
Joined: Wed Jun 30, 2021 9:39 pm

Re: Excel Ship Designer

Postby Arkathan » Fri Jul 02, 2021 5:04 pm

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.
Last edited by Arkathan on Fri Jul 02, 2021 5:31 pm, edited 1 time in total.
User avatar
Arkathan
Mongoose
Posts: 130
Joined: Wed Jun 30, 2021 9:39 pm

Re: Excel Ship Designer

Postby Arkathan » Fri Jul 02, 2021 5:28 pm

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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 6:43 pm

Arkathan wrote:
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.

Arkathan wrote:
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.
Last edited by AnotherDilbert on Fri Jul 02, 2021 7:03 pm, edited 1 time in total.
AnotherDilbert
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 7:02 pm

Arkathan wrote: 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.

Arkathan wrote: 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?

Arkathan wrote: 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
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 7:45 pm

Recreation of Heavy Fighter:

Bridge w Armoured Bulkhead:
Image

Record sheet missing bulkheads:
Image
AnotherDilbert
Cosmic Mongoose
Posts: 4591
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: Excel Ship Designer

Postby AnotherDilbert » Fri Jul 02, 2021 7:50 pm

Rounding error in volume?

Cargo of 5.75 Dton not accepted:
Image
Only 5.7 Dton accepted.


Yet the total ship is 0.05 Dt undertonnage:
Image
User avatar
Arkathan
Mongoose
Posts: 130
Joined: Wed Jun 30, 2021 9:39 pm

Re: Excel Ship Designer

Postby Arkathan » Fri Jul 02, 2021 9:29 pm

AnotherDilbert wrote:
Fri Jul 02, 2021 10:40 am
Tab "11-Staterooms"

Not "birth", but "berth".

Brigs and Stables have life support costs.

Fixed. Thanks.
User avatar
Arkathan
Mongoose
Posts: 130
Joined: Wed Jun 30, 2021 9:39 pm

Re: Excel Ship Designer

Postby Arkathan » Fri Jul 02, 2021 9:31 pm

AnotherDilbert wrote:
Fri Jul 02, 2021 7:02 pm

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.

Who is online

Users browsing this forum: blaster219 and 40 guests