High Guard 2e Ship Design Spreadsheet v1.1 (Google Docs)

[bluekieran]

I've just added the options from the High Technology Chapter: https://docs.google.com/spreadsheets/d/1eD6FzG7a7QnYo21tUWisvPi9OvcpBXxTV6GcBRAJfcU/edit?usp=sharing

Edit: v1.3 now up at https://docs.google.com/spreadsheets/d/1OiLxq63AMFT-bs8K-sgRwWRdGVpkabEBXrbczcCnQLw/edit?usp=sharing

Edit 2: v1.5 now up at http://bit.ly/2naoqqI with Hardening and Primitive/Advanced options now in - though choices are only enforced for drives and power plant, because those were enough of a pain without repeating it for dozens of weapons!

 

[AnotherDilbert]

Thank you for sharing your effort.

It looks good.

 

[bluekieran]

Version 1.3 now up at https://docs.google.com/spreadsheets/d/1OiLxq63AMFT-bs8K-sgRwWRdGVpkabEBXrbczcCnQLw/edit?usp=sharing

TLs are now shown, though I haven't yet set it up so you can have advanced/primitive drives and so forth; I suspect that will make it even more ponderous!

 

[arcador]

It's really good! Well done

We modified it for our house rules.

 

[bluekieran]

Version 1.5 now up at http://bit.ly/2naoqqI

It wasn't until I was doing Primitive/Advanced stuff that I realised what a mistake I was making mixing values and formulas earlier in an attempt to make the whole thing a bit more compact... have learned a lot about spreadsheets doing this!

I've also altered the crew formulas so they round down rather than up, and fixed a couple of other errors.

 

[snrdg121408]

Morning PST bluekieran,

I'm joining in with another thank you for sharing your spreadsheet with the MgT community.

Since I do not have access to Google Docs I tried converting the work into MS Excel and OpenOffice Calc.

The conversion to Excel found and deleted three items that were not compatible and I'm trying to figure out why.

OpenOffice Calc is not playing well with the "regexmatch" found in Main Cells D52, G52, H52 and Data B86, B88 returning the errors #Name and #N/A. Other than not liking "regexmatch" Calc appears to run just fine, now if I can figure out what Calc has that does the function of "regexmatch" I'll be able to play with the spreadsheet better.

Thank you again for the work you have put in to provide a game aid, especially for me who needs all the help I can get.

 

[bluekieran]

Since I do not have access to Google Docs I tried converting the work into MS Excel and OpenOffice Calc.

The conversion to Excel found and deleted three items that were not compatible and I'm trying to figure out why.

OpenOffice Calc is not playing well with the "regexmatch" found in Main Cells D52, G52, H52 and Data B86, B88 returning the errors #Name and #N/A. Other than not liking "regexmatch" Calc appears to run just fine, now if I can figure out what Calc has that does the function of "regexmatch" I'll be able to play with the spreadsheet better.

I'm glad you like the sheet!

I can probably swap all the RegExMatch's out for similar functions that might be a little more cross-platform; I'm just using them to search for substrings, but my spreadsheet skills weren't up to much when I started this so a lot of the tricks I've used are google hits. I'll take a look later on and see whether Find(), Search() o something else would suit.

Let me know if you figure out what Excel is throwing out.

 

[snrdg121408]

Hello bluekieran,

Since I do not have access to Google Docs I tried converting the work into MS Excel and OpenOffice Calc.

The conversion to Excel found and deleted three items that were not compatible and I'm trying to figure out why.

OpenOffice Calc is not playing well with the "regexmatch" found in Main Cells D52, G52, H52 and Data B86, B88 returning the errors #Name and #N/A. Other than not liking "regexmatch" Calc appears to run just fine, now if I can figure out what Calc has that does the function of "regexmatch" I'll be able to play with the spreadsheet better.

I'm glad you like the sheet!

I can probably swap all the RegExMatch's out for similar functions that might be a little more cross-platform; I'm just using them to search for substrings, but my spreadsheet skills weren't up to much when I started this so a lot of the tricks I've used are google hits. I'll take a look later on and see whether Find(), Search() o something else would suit.

Let me know if you figure out what Excel is throwing out.

I tackled OpenOffice and the solution I came down to using is =IF(B47="Core";"-";(100000*$Data.B78)). I'm going to try build the design I was working on using CRB 1e and HG 1e back on 2/8/09.

I'll check back with the items MS Excel Converter does not play well with shortly.

Update for MS Excel 0809 PST

Excel reported the the following items were unreadable content and removed:

Formula /xl/worksheets/sheet1.xml part
Shared Formula /xl/worksheets/sheet1.xml part
Formula /xl/worksheets/sheet2.xml part

The use of regexmatch for the computer had the same result as in OpenOffice Calc.

Excel does not like the formula of =IF(B100>0,12,"")+VLOOKUP(I100,Data!$A$176:$D$182,2,FALSE) for the weapons TL however changing the formula to =IF(B100>0,12+VLOOKUP(I100,Data!$A$176:$D$182,2,FALSE),"") does appear to fix the issue.

Another update to follow since I'm working on the TL issue.

Update 0901 PST

I found two more items that I corrected in Excel:

Folding Drive Data: =ROUNDUP((Main!B2/100)*(IF(Main!B256="No",0,Main!B256*2.5)),0)

Recommended Crew: Excel Error Check indicates that the numbers for Engineer; Maintenance; Medic; Gunner; Stewards; Administrators; and Officer are stored as text. To convert the text to numbers clicking on the flag and click covert to numbers fixes the issue.

I'm keeping my fingers crossed that I've got both OpenOffice Calc and MS Excel fixed, unfortunately more testing will have to wait until after an appointment.

 

[egoaz2ca]

Morning PST bluekieran,
...
Since I do not have access to Google Docs I tried converting the work into MS Excel and OpenOffice Calc.
...

Docs is free, and available to everyone. Anyone with a Google/Gmail login already has it, but of course there are those that don't want an account.

 

[snrdg121408]

Hello egoaz2ca

Morning PST bluekieran,
...
Since I do not have access to Google Docs I tried converting the work into MS Excel and OpenOffice Calc.
...

Docs is free, and available to everyone. Anyone with a Google/Gmail login already has it, but of course there are those that don't want an account.

Thank you for the reply and the information about Google/Gmail accounts.

 

[snrdg121408]

Hello bluekieran,

I've found out what some of the items of items Excel reported as being unreadable content and removed:

Formula /xl/worksheets/sheet1.xml part
Shared Formula /xl/worksheets/sheet1.xml part
Formula /xl/worksheets/sheet2.xml part

The Power requirement calculations on the main worksheet are victims of the removal process.

 

[snrdg121408]

Evening PST bluekieran,

Under the Hull section in Cell A10 the option of Armored Bulkhead (tons) is listed followed in Cell B10 with the entry for tons. Cell C10 has the formula of 200,000 x B10 and Cell D has the formula 0.1 x B10

Here is a copy and paste of the instructions

"Armoured Bulkheads MgT PDF HG 2e p. 35
Armoured bulkheads protect specific areas and systems, such as the ship’s computer, jump drive or fuel tanks, making them much more resilient to damage.

Adding armoured bulkheads consumes an amount of space equal to 10% of the tonnage of the protected item. The Severity of any critical hit to the item will be reduced by -1 (to a minimum of Severity 1)."

From the instructions the tonnage of the armored bulkhead is 10% of the specific area or component that the designer want to add more protection to.

I think that if the Hull can have the Armored Bulkhead option applied the bulkhead tonnage = Hull Tonnage x 10% and the Cost is determined by Hull Armored Bulkhead Tonnage x 200,000.

So far I have not located any more formulas that Excel removed and from what I can see Calc has only had the "regexmatch" hick-up.

Update:

Nuts, I just noticed that the only Armored Bulkhead option appears to be for the Hull rather than for individual systems and areas.

 

[bluekieran]

I was also going over the fuel for X weeks operation formula today, as I was aware it was way off the example ships in HG.

Turns out the HG rules round up when getting the 4 week figure, then just multiply that, which I guess you could justify somehow, but I don't love.
i.e. 12 weeks operation will always require at least 3 tons of fuel tank, and if the plant is slightly too big for that, it will suddenly require 6 tons.

*twitch*

My sheet was (and currently still is) working off a weekly figure then rounding up after you decide how many weeks operation you need, then telling you how many weeks you get after the rounding; I can see how that would be onerous without a spreadsheet to do it for you, I 'spose. It's a big difference though - my Type-S scout gets 20 weeks on a 2-ton fuel allowance, or 30 weeks on the 3-ton allowance the book specifies for 12 weeks operation!

 

[AndrewW]

Turns out the HG rules round up when getting the 4 week figure, then just multiply that, which I guess you could justify somehow, but I don't love.
i.e. 12 weeks operation will always require at least 3 tons of fuel tank, and if the plant is slightly too big for that, it will suddenly require 6 tons.

Yup, that would be correct.

 

[bluekieran]

Yup, that would be correct.

Yeah. So power plants are much more fuel-efficient in 10-ton increments, because technical reasons; a 1-ton or 11-ton plant is a real gas-guzzler.

I do want the sheet to be usable for people playing RAW though; I'll put in a "Rules as Written / Accurate" drop-down choice.

 

[ochd]

Just wanted to add my appreciation for creating and sharing this sheet. It's really helpful, thanks.

Dan.

 

[bluekieran]

Just wanted to add my appreciation for creating and sharing this sheet. It's really helpful, thanks.

Dan.

Thanks, it's been an interesting project. I'm thinking about picking up the Vehicle Handbook and doing that one as a website, as I've at least as little web-coding clue as I had spreadsheet clue a month ago!

 

[snrdg121408]

Morning PST bluekieran,

Should I be providing feedback here or is there another route that I should be following?

I've been trying for an hour to open the latest version with out success my Firefox browser returns the message the connection has timed out.

The converted Calc 1.5 spreadsheet is working great while I'm still trying to figure out what the MS Excel Converter broke.

I'm looking forward to your Vehicle Handbook designer which will probably be much better than the rough Excel spreadsheet I put together following the Honey Badger example which appears to work and from the HG spreadsheet I've decided to put my efforts on hold.

Another thank you for sharing, Is the Vehicle designer done yet? :wink:

 

[bluekieran]

Morning PST bluekieran,

Should I be providing feedback here or is there another route that I should be following?

Here is fine, I am on pretty frequently.

I've been trying for an hour to open the latest version with out success my Firefox browser returns the message the connection has timed out.

Hopefully that's just the bit.ly URL shortener that's broken - the full link is https://docs.google.com/spreadsheets/d/1bZbqf9xyzBYgq9vn1nK-O-0e13M-ofndN6-vFgASScY/edit?usp=sharing
Of course if Google Docs is offline for you, there's not much I can do!

Another thank you for sharing, Is the Vehicle designer done yet? :wink:

I have the PDF nearly downloaded, just have to pick a programming language to learn

 

[snrdg121408]

Thank you bluekieran for letting me know I've not been providing feedback in the wrong place.

I was successful in downloading v1.7 from the link in this post, so the short version from earlier does appear to be under siege from web gremlins.

OpenOffice Calc still does not like regexmatch, but copying and pasting from v1.5 fixed the issue.

Once I find the stuff Excel Converter broke in v1.5 I'll hold off converting v1.7.

How does the spreadsheet's Armored Bulkhead option work?

As stated in the post from Fri Mar 10, 2017 4:20 AM my understanding is that armored bulkheads tonnage and cost is based on the component or area the option is being used to protect.

Morning PST bluekieran,

Should I be providing feedback here or is there another route that I should be following?

Here is fine, I am on pretty frequently.

I've been trying for an hour to open the latest version with out success my Firefox browser returns the message the connection has timed out.

Hopefully that's just the bit.ly URL shortener that's broken - the full link is https://docs.google.com/spreadsheets/d/1bZbqf9xyzBYgq9vn1nK-O-0e13M-ofndN6-vFgASScY/edit?usp=sharing
Of course if Google Docs is offline for you, there's not much I can do!

Another thank you for sharing, Is the Vehicle designer done yet? :wink:

I have the PDF nearly downloaded, just have to pick a programming language to learn