Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v13

[amram (OP)]

Suggestions:

Move Spare Mass and Mission Tonnage to in between Military? and Force Deployment.  This does two things.  1)  Breaks off Force Deployment form the rest of the shaded block.  2)  Gives CRITICAL information available without scrolling on any display setting.

Move Force Tug and Tug Mass further to the right.  This is a very situational field which causes CRITICAL information to be off screen on most displays.

Change kTons to desired tonnage and make it be entered as such.  (There is way too much switching between kTons and actual tons in this spreadsheet, consistency is more important than brevity.)

See attached for the differences on the sheet.

Used to have the entry in tons - just noticed I hadn't also changed its note title when I changed it.  Still, you're definitely right I have more tonnage than kilo-tonnage in the sheet, and consistency is good, bad as I am at it....lol.  Agreed and kicked kTons back to tonnage.

On that note, I should probably undo the v8 change to make TUG mass be kilotons as implied then, lest it be the odd entry out.

Had the same thing going on with speeds, forced entry in thousands, everything else was km/sec, including the speeds per engine type in C104:I118, so set that entry to be just km/sec as well.

Fair call on the column positions - I've been solely on 1920x1080 for so long, I never really thought about anything smaller.  Moved spare mass and mission tonnage like you put them between Military and forced deployment, and moved Tug and forced tug to the far right where spare/mission tonnage were. 

Moved engine EPH  from left of the minimum engine Qty entry, to just after Engine Mod.

Anything else that seems sensible to relocate?

I'm looking at Missile Data and thinking it might be worth picking up Velocity, Range, Endurance, and Boost, and dropping those between Want hit chance, and Active MSP, essentially putting that after the really common missile configuration, and before the beginning of all the sensor/ecm/armor stuff.  See attached.  Thoughts?

[Roughshot]

Just FYI, I downloaded v7 and uploaded it to Google Docs, everything seems to work the same, no changes needed.    All the formulas and information also perform correctly. 

Link to the example - https://drive.  google.  com/open?id=1PfBGrxZs664rrywcb1eLhh-EMWAf1DDG

Edit - Forgot to mention you just need to open it in google sheets to use it.

[amram (OP)]

Now that is interesting, I somewhat expected it to barf on some of the things I pull in there, in particular the missile cross referencing to match rows with their sets of columns to calculate.

Good to know.

Was there anything particular about the upload needed, or just default and go?

[Roughshot]

Now that is interesting, I somewhat expected it to barf on some of the things I pull in there, in particular the missile cross referencing to match rows with their sets of columns to calculate.

Good to know.

Was there anything particular about the upload needed, or just default and go?

Nothing I'm aware of, I simply downloaded v7, opened google docs and used drag and drop to upload it, after that it showed the excel document and I opened it with google sheets, ran a few tests with missiles and then compared it to Aurora 1. 8.   As far as I can tell everything works correctly.

[amram (OP)]

Excellent, I'll probably start doing dual updates from here then, I know excel, so its where I'll get stuff done, since I can get a better return on time put in there, but I'll start running a copy on google docs too so its more available.

[amram (OP)]

V9 Changes

• Converted Ktons back to Tonnage, Noticed I never changed its title in its note either, since I used to have it that way...
• Guilty of same with force kkms, shows the obtained speed in km/sec, but forcing it in thousands - changed retitled to km/s, and changed to km/s entry, rather than kkm/s entry.
• Moved Missile Data Velocity, Range, Endurance, and Boost to imediately after "Want Hit Chance"
• Moved Fleet Comp Engine EPH a few columns right to just after the Engine Mod
• Goofed the number formatting for missile boost, it only showed integers if more/less boost would improve range, fixed in excel.
• Sensor Data: Replaced the graph prep result NA() with if(iserror(info("version")), , na()).  Google sheets was graphing NA()'s, but ignoring blanks.  Excel graphs blanks as zeroes but ignores NA().  Excel doesn't throw a fit over info(), sheets does, so I can test for which the formula is executing under, and give the correct "ignore me" value.
• At a glance, About, Sensor Data, Fleet Composition, PD Turret Calculator, and Missile Data appear to be mathematically functional and in agreement between sheets and excel.
  • Sheets also appears to be reasonably quick all things considered.  Definitely slower than excel on my machine, noticeably so, but its still responsive in sheets.
• Known issues with Sheets version:
  • Widths sometimes are a bit wonky, probably won't be too fussy about that until I run out of stuff to try and update the sheet with.
  • The Conditional formats applied to 'Missile Data'!Engine Boost are not applying in sheets.  Have not investigated deeper yet — the boost (in/de)crease suggestion formats are not functional in sheets.
  • The light blue row line formats are inconsitently applying in sheets, instead of spanning the whole width, some cells lack them, some have them, do not know why yet.
   

If anyone has suggestions on getting both the conditional formats for 'Missile Data'!U21:U50, the regular border/fill formats for much of 'Missile Data', and the column widths in general to play nice in both excel and sheets, I'd love to hear them and see if they help at all.

First post updated.

[esavier]

Hey!
i tried to use the sheet as a helper to missile design, but no matter where i open the sheet i am unable to use missile composition tab.
on google docs it seems to almost work but it keeps "loading"  engine tech (i. e.  i cant specify engine tech for missile,
openoffice loads the sheet in about 30-40 minutes, and then there are mostly errors on each page
MS Excel reports some weird errors i am not sure of (on friend's PC, i do not have MS packet myslf so i can not recheck this)

Are those issues known?

[Cedras]

I have the same problem.   
The drop down menus for armor type and engine type in Fleet composition and Missile Data just load infinitely in the google sheets version.   
The formulas in the office version throw #VALUE, when I use any of the TRUE/FALSE drop downs (office online).   

[amram (OP)]

hmm, I can confirm the drop down issue in missile Data for sheets.

Both the Fleet Comp and Missile Data barf on it, definitely something up with the drop down for engine tech.

Had not tried the sheet in open office, may be a compatibility issue - I do leverage some fairly recent office additions.  Will see if I can't figure out what, might be able to try something different if so.

[amram (OP)]

If I save the file as an ods in excel, I can at least open it in openoffice and see what is broken:

• About cell references - very unhappy about those.  I modified them to calculate the cell references so that they would update theirself if I modified the sheet and those cells ended up moving, or the ranges changed size.
• Sensor Data seems to be fine until it comes time to figure out what the important tonnages in the graph are, then it throws up #Name?, so I use something it hates: iferror, I use that in many places so the sheet can gracefully just stop trying if it doesn't like something rather than a sea of error cells.  That can be rewritten though to use an if(iserror(), So I'm pretty sure I can get Sensor Data running in open office with minimal changes
• Fleet Composition has a number of open office issues:
  • some unknown excel functions, iferror, com.microsoft.ceiling
  • some #REF!, in stead of named ranges, which bothers me because the goal range does survive with a named range and seems to work....name length limits?[/li
• PD Turret Calc is, of course, also pissed off.
  • Mostly seems to stem from yet more iferror() usage.
  • has a com.microsoft.single, which in excel is another iferror, strange how this one came out.
• Missile Data is...of course....pissed in open office.
  • The engine tech drop downs are just #REF!, though the same validation worked in Fleet Comp.  Could be that the referenced range(for both sheets) is in fleet comp.
  • iferror() appears in this sheet as well, so, of course, there will be complaints here.
  • AH, I now know what the com.microsoft.single is, excel has been auto inserting @ infront of some functions, it says for compatibility reasons, I've been letting it since it hasn't be breaking function.
  • So its not a matter of named ranges being on the wrong sheet, since Fleet_Tech_ENG_EP_C_Sharp made it into open office, but both Fleet_Tech_ENG_EP_7.1 and Fleet_Tech_ENG_7.1 did not, maybe it hates the decimal in the name?

I'll try punting those from the sheet, and see what open office does with it after the fact.

Google sheets though....

• About seems fine.
• Sensor Data appears fully functional as is
• Fleet Comp barfs some of the named range validations - no drop downs for armor, engine type, max engine HS, while roles works.  What is different?  An if() statement in the validation to test for c# or 7.1, and return the correct validation.  This I can probably fix by moving the test and return to an external set, and using that range for validations in all copies.  Then there is just a names range reference, no if statement in the validation.
• PD Turret Calculator has the same issue with an if statement within validation, same solution presumably
• Missile Data has...of course, the same if() in its validations, for the same reasons, with the same solution.  Otherwise appears functional.

V10 update in progress, will have the if() extricated from validations shortly, and that should get google sheets working if thats all it was, which it seems to be.
V11, I'll tear out the iferror(), and swap ceiling() for roundup(x,0), which might be enough to get most of open office working(or at least uncover something else it will be pissed about) - about is still going to loathe what I have done....might just remove that sheet, I think the worksheet column notes have most of it covered anyways.

Stay tuned...

[amram (OP)]

ok, looks like I might have google sheets working properly finally - updated the main post google link.

I don't have time to settle into a game and really test it, but it looks like its updating properly, not throwing a fit anywhere, drop downs work, and the cells I looked at are giving the same answers as my excel sheet.

Open office is endlessly pissed off at named references on another sheet.  They work fine on the same sheet, but on another sheet, its just caves in.

To get that working I will have to see if cell references work when they reach out to other sheets.  if they do, i'll drop the named references and go back to cell references, then , barring more issues still hiding behind the current ones, it should be functional everywhere.

[amram (OP)]

V10 + V11 Changes

• converted all named ranges that had _7.1 in the name to have _71 instead.
• Added a new tab, C# toggle and tech data.
  • The c# toggle is now sheet wide, rather than per sheet.
  • All of the main tech data ranges have been relocated here, instead of wherever I had made them.
  • All of the toggles to use c# or 7.1 data now happen here forming a new set of ranges that contain the result of that decision.
• Killed the About page.  I think the Notes probably cover everything, and Missile Data has a bit more explanation in sheet to help out.
• Rebuilt the engine calculations to not require ceiling, and do a few less conditional checks
• Rebuilt the fleet comp main body engine results to use lookups for the already completed effort in the calculations table rather than rebuilding it from the given engine Power mod.  Ceiling no longer exists in the sheet.
• @ no longer exists as a symbol in the sheet — excel is probably going to give it back to me sooner or later....
• Removed a number of iferror tests to let them just happen if they do.
• Rewrote some iferror tests to test for a precedent case and not do work if so
• Rewrote some iferror to test their result with iserror instead, this does mean some repeated work.
• iferror removed from the entire workbook.
• Moved 'Fleet Composition'!'armor tons' to just before 'mission tonnage'
• Replaced all the named ranges that exist on other sheets with cell references since open office hates non-local named ranges.
• Fixed a few minor issues along the way.

Main post updated, google sheets link updated, 065 xlsx and Open Office ods attached.

The formatting is still pretty flaky in google sheets, and almost nonexistent in the Open Office, but the sheet seems functional in both.

[amram (OP)]

V12 changes

• search and replace hit an extra "quasar" string, the text label for its toggle.....fixed.
• Steve is changing MCR from 0.33 HS to 0.3 HS, so I've changed C# MCR tonnage from 16.5 to 15.  It he hasn't released that version yet then MCR is lying to you until he does....
• Still had an if inside a validation in the PD turret calculator, affecting google sheets, fixed.
• Still had validation by named range in PD Turret Calculator, affecting Open office, fixed.
• Some missing BFC techs for C#, hadn't input all of them yet, fixed.
• PD Turret Calculator now shows expected weapon failures and the respective MSP cost to replace them.
  • Note that while I could calculate the engineering bay / maintenance storage tonnage associated, I'd have to know so much about the ship that you'd need to entirely design it here.

Also started posting emptied versions of the sheet, where I have stripped nearly all the game specific data, like hull setups, or sensors, missiles, etc, so they aren't in your way if your using the sheet.  Some data remains, like I haven't changed the tonnage and EM/TH signatures for the sensor sheet.

[Iceranger]

Note that while I could calculate the engineering bay / maintenance storage tonnage associated, I'd have to know so much about the ship that you'd need to entirely design it here.

Everyone is working towards their own version of the ship design screen

[amram (OP)]

I've tried a couple times, no good way to wedge it into excel that i've figured out, yet, lol.

At least, not without using macros, which understandably people would prefer to avoid, and I tend to agree, so I usually avoid implementations that require them.

Not much I haven't got at least a little excel sheet somewhere to calculate(weapon ranges, damage, crew needs, materials costs, armor, magazines, etc), I can do most things, bringing it together in a workable manner though...