Aurora 4x

C# Aurora => C# Utilities => Topic started by: amram on April 24, 2020, 10:02:38 AM

Title: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v13
Post by: amram on April 24, 2020, 10:02:38 AM

Its my own personal workbook for aurora related stuff, cleaned up a bit for sharing, figure I may as well share it, others may find it useful too.

Handles both 7.1 and C#, with a simple true/false toggle in each worksheet, so its useful in either, as well as the upcoming Quasar4x given its goal of cloning 7.1.

Liberal use of notes to explain things, as well as an "About" sheet with hyperlinks to the cells referenced by the text.

I do not use any macros or VB code in the workbook at all, absolutely everything occurs in cell.  No cells are locked, all formulas are visible.

I'm sharing it both empty of configuration, and populated with data from one of my games so its more obvious what does what, from how I've used the sheet - if you already understand it, go with the empty sheet to make it your own with little fuss.

The Sensor Data worksheet:

• supports 8 active sensors at a time, FC or not,
• Calculates range, MCR, and detection of 9 specified tonnages
• Graphs all the data.  Has a couple cells to limit the graphed ranges, or you can just not display a sensor to better see low range sensors
• Also has a row for EM and TH sensors.

The Fleet Composition worksheet does a few useful things.

• Given final tonnage, speed, and range, calculates the engine and fuel combination that results in the smallest tonnage used that meets the criteria, using stated technologies.  It respects the constraints that comprise a civilian engine if the ship is not listed as military, and can have its minimum engine count specified to ensure some redundancy if desired.  Will state how many engines, of what hull size, using what boost value, are needed to meet the speed you asked for.
• Sheet suggests a combination of fuel tanks that fulfills both the needed fuel quantity, and a subdivision for redundancy — can handle compressed fuel tanks.  The subdivision is to avoid having all your fuel in one Very large tank, and having no fuel left if it gets damaged - the sheet will suggest using more of the next size down if you cannot have enough of that size to meet redundancy needs as specified.
• Calculates mission package tonnage, given armor, shields, propulsion package, deployment, and final size, it determines useful tonnage for weapons and systems.  Mostly an estimate, its usually pretty close though.  Determined by taking final tonnage, subtracting out engines, fuel, armor, shields, the bridge(if applicable), one maintenance bay(if applicable), and the requisite crew tonnage for those given the deployment length.  What remains is roughly equal to in game mission tonnage for additional components.
• Built around fleet roles, it uses a table with the roles, that states range, speed factor, and deployment length, to quickly produce a baseline that matches your typical ships — supports 43 roles at current — yielding ships with identical speeds and ranges, barring some variance for rounding.  Each of range, deployment, and speed that the roles configure, are easily overridden in the main table for ships that need to differ from a the norm.
• Has 80 rows to keep track of your hulls, proving enough room to keep an entire fleets worth.

Think of them more as Hulls awaiting fitting out with equipment, since the sheet really only concerns itself with engines, fuel, armor, shields, and the requisite maintenance bay and bridge since those are known.  If you will have the same engines, fuel, armor, and shields for an anti-missile missile ship and an anti-ship missile ship, I would just have the one hull entry since it applies to both, though there is no reason you could not have both.

The PD Turret Calculator:

• Considers gauss, railguns, and lasers. 
• Calculates the tonnage necessary given stated technologies, fire control choice, target speed, ship speed and deployment length, and expected salvo size,  to figure out how many mounts would be needed, along with how many reactors are necessary, with crew tonnage factored in
• Sorts and displays the mountings in order of lowest to greatest tonnage needs, so you can find the solution with the least required tonnage, freeing more tonnage on the ship, or see if the rail guns or lasers can adequately serve offence and defence.
• Supports C#, A7.1, and Quasar, given that all three have somewhat different behaviour here
• has a small table to indicate the size of res1 sensor necessary to obtain the requested tracking bonus vs a couple missile sizes, which blanks out for A7.1

The Missile Data Sheet started me down the path of customising excel sheets to my own needs, and then building my own entirely. 

• Given tech, Target's speed, MSP Size, and expected hit chance, it finds the agility and engine combination requiring the least MSP, with excess remaining after warhead, sensors, armor, and second stage payload spent on fuel.
• Indicates if altering boost up/down would improve range or not.
• has space for 30 missiles, each with its own tech and specification entry, so old and new can exist together
• supports multi stage missiles, and while it will let you assign a missile as its own second stage, the sheet just doesn't give a return, since its MSP-payload would equal zero, so no engine/agi will ever fit within the remaining space.
• Shows the hit chance for a given confidence other than the design confidence, against the design confidence of a hit.  If the resulting confidence is less than given, it shows the missiles necessary to have that confidence of getting at least one hit.  It also shows what target speed that missile would have that confidence against.
• Shows the hit chance and salvo size needed to obtain a specified confidence of a hit, against a new target(other than the design target) speed allowing quick comparison of which weapons are still useful enough to load if necessary.
• Displays active/passive/EM sensor data, as well as how many increments you can expect to hold the contact for

Updates

• V13 changes
  • Fixed a minor formatting issue in the PD Turret Calculator when the estimated PD active Search sensor would exceed HS 50:  a value of 75.76 would end up as 575 as a consequence.  Fixed that.
  • Forgot to make the PD Turret Calculator section for determining an appropriate active sensor also use 15 tons as MCR when set for C#, fixed.
  • Stopped Missile Velocity from showing when there is negative fuel, just as range and endurance do not show in such case.
  • Found and fixed a flaw in Missile Data where the ENG/AGI calc's could end up referencing the text MR instead of a number, and become unhappy about that.
  • Stopped the boost increase/decrease indicators from seeing "" as a value that could be greater than any actual number.
  • Fixed a math error in the MSP required estimate for PD Turret Calculator, because I am a derp.
  • Added more notes to PD Turret Calculator for the results section.
  • Changed formatting to flag a failure to obtain the requested bonus, rather than max bonus.
  • Pushed some values out to helper cells in missile data, to facilitate better matching aurora's rounding of values, and so better match C#'s missile results.
  • Missile engines in c# now change step sizes as happens in C#, with 0.01 until 3.0, 0.1 from there until 10.0, 0.5 from there until 50
  • Had managed to break the engine calcs when I last altered them to use the new helper columns, a cell reference didn't adjust correctly, they were ignoring fuel quantity, fixed.
• 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.
  • 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 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.
• 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.

Older changes

Off-Topic: show

   

*  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 immediately 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(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 inconsistently applying in sheets, instead of spanning the whole width, some cells lack them, some have them, do not know why yet.

*  V8 changes:
*   
*      Tug Mass was not being multiplied into tons as it was supposed to be, so it required entry as tons, rather than kTons as indicated.  Fixed
   
*  V7 changes:
*   
*      Found another spot where I still had some lingering formulas in cells expecting entry - cleared it.
*      Rewrote the missile AGI/ENG MSP calcs to use a stepped method, reduced rows needed from 1450 to 150.
*      Leveraged the now considerably shorter AGI/ENG cals to do two additional complete passes, one with .05 more boost, and one with .05 less, sheet can now indicate if raising or lowering boost will increase range.  And even still, it is ~70% less workload than previous.
*          "5.9 x "  where x is an up arrow indicates increased range with 5.95 boost
*          "5.9 x " where x is a down arrow indicates increased range with 5.85 boost

*  V6 Changes:
*   
*      Got my hands on the FastExcel Profiler Bundle trial and went a murderin', lol.  Took the nastiest formula from 4,000ms, to the worst being 108ms.
*      Rewrote the boolean for the sensor graph prep work.
*      Punted the grunt work for TUG mass to a pair of helper cells.  Determining maximum tonnage mil/civ happens in just two cells now, rest look at those.
*      Used an excel profiler: 80% of the book's workload was in the missile data, specifically, the visual indicator for min AGI/ENG cost.  Reworked it.  Update changed just those 30 columns costing 3773.82 millisec, to the entire workbook costing 839msec, and those columns down to just 63 millisec total.  Still pretty volatile though, work continues to trim fat.
*      Pulled apart the AGI, ENG, and visual indicator to isolate some row by row constants, and stashed them at the very bottom to reference rather than repeat the effort, yet more gains had.


* V5 changes:
*    Moved all the C# toggles to put the entry in cell C3....because reasons.
*    Sensor Data
*        Had missed a column in verifying a given tonnage had a sensor to graph.
*        Eliminated a case that existed from when the graph limits had a minimum range - never did get excel to let me move the minimum Y axis dynamically without VB.
*        Simplified the formulas for the graphing prep work - punted the repeated lookups for max range and resolution to helper cells
*    Fleet Composition:
*        Added a note for Subdivide Tanks -> H5
*        Had been re-calculating engine quantity four times each boost, now just once and reference the one result.
*        Found and fixed a bug in the formula for engine EP necessary per boost - one cell reference to max boost was row locked, should not be.
*        Converted Armor Type, Engine Type, Max Eng HS, Armor Tons, Want Speed, Engine Power, Desire Range, to use named ranges instead of cell ranges.
*        Converted Role range, K tons, Build This, Military references to named ranges
*        Added some more missing notes -> Role column headers
*        Pushed the Ship-Role row-match out to a helper so it gets done once rather than 5 times.
*        Moved tug mass formula from sitting in the user entry, to an assumed value when user entry is not given, added column to over-rule it.
*        Rewrote TUG Mass to be a bit less convoluted.
*        Added isTUG column to Roles so its not locked to a name anymore. - Military ships with an isTUG role use maximum military ship tonnage, likewise for civilian using civilian.
*    Missile Data:
*        Was repeating the lookup for Engine EP/MSP for missiles in Velocity, Range_mkm, and ENG_PWR, now has a helper cell next to ENG_PWR.
*        Changed ENG_EP to use a named range rather than cell reference for both c# and 7.1
*        The C# toggle wasn't shaded, should be since its a user setting.
*        Converted Engine Tech, and every missile ENG_HS lookup, to use named ranges instead of cell ranges.
*        Added another row to the Missile Tech, to store and reuse the looked up missile configuration row, rather than look it up 15 times per missile.

V4 Changes:
* 'Sensor Data'!H5:H14, 'Sensor Data'!N5:N14, and 'Fleet Composition'!R6:S85 are now validated TRUE/FALSE as well.

V3 changes:
* Added in some missing notes.
* Found some typos.
* Cleaned up some of the number formats a bit more in both fleet composition and missile data.
* Changed 'PD Turret Calculator'!H27:H30 to show actual bonus gained, rather than relative percentage (actual bonus vs actual bonus / max bonus)
* Forgot to shade in 'PD Turret Calculator'!C26:C27 and 'PD Turret Calculator'!E27:E30
* Made the sensor HS in for an active PD sensor in 'PD Turret Calculator' work with 7.1 sensors - for quasar when its ready.
* Switched 'PD Turret Calculator' reactor output tech entry in 'PD Turret Calculator' to use the tech name, rather than the corresponding 1hs output.
* 'PD Turret Calculator' now supports Quasar tracking bonuses — using 7.1's expected outcomes, am aware 7.1 is broken, it doesn't use them.
* Found and removed a couple more random values I had scattered around.
* Converted all cell references in the About sheet to manipulate named ranges - I shouldn't have to adjust text nor the hyperlinks after this - your own edits might also not break the links and so keep the help relevant.

V2 changes:
* Wiped out some personal preference formulas that should have been killed before I set it loose.
* Corrected 'Sensor Data'!B5's dependents so b5 works correctly again
* Unhid Fleet Composition Column D(engine tech power rating) and moved it to safety in the main calculations section before it gets deleted accidentally.
* Punted the shield fraction from Roles - While I used it that way, the formula in a cell expecting entry should probably die.

For myself, the excel sheet is considerably faster than sheets is, your mileage may vary.

Google Sheets V13 Empty:  https://docs.google.com/spreadsheets/d/1Ae2yaHq__cTlDQ9voCJh9S1XAMWZ3MYWIbfODah7d78/edit?usp=sharing
Google Sheets V13 populated:  https://docs.google.com/spreadsheets/d/1gJo3MI1vCu2z3Wa7nIpfz6daAqTodR_m12pESfXEH9Y/edit?usp=sharing



I still can't open the xlsx directly in open office, saved as ods from within excel, and scalc can get that open and it seems functional.  Much of the formatting got dropped though, may be some other minor issues I haven't noticed yet.

Use the Empty sheets if you don't want data from my previous game in the way - all that is removed in those.  Use the populated sheet if you want to see how I had it configured

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: Demonides on April 24, 2020, 10:13:43 AM

Personally, I don't download zipped office files where macros can be added by strangers. That is why I suggest that you provide the above document in the form of Google online doc.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: amram on April 24, 2020, 10:16:09 AM

Which is why I also avoid ever touching the macros, so one never need enable them.

If someone want's to be ambitious and rebuild it in google sheets, be my guest, Excel I know.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: Pedroig on April 24, 2020, 11:46:32 AM

Confirmed, no macros...

Questions:

1.  You indicate that shaded cells are for input, but then have many shaded cells with formulas in them, like the entire Thermal Strength row.
2.  Thermal Strength tooltip is wrong for C#:  For ships and missiles thermal sensor formula is Max Engine Output * (Current Speed / Max Speed) * Thermal Reduction (You don't account for TR which is fine, but tooltip indicates ship size is a factor, which it is not)
3.  Sensor Data B5:B7 does not seem to modify graph.
4.  Fleet Comp:  Changing a select value changes all values under it til it hits a different valued cell.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: amram on April 24, 2020, 02:14:04 PM

Quote from: Pedroig on April 24, 2020, 11:46:32 AM

Confirmed, no macros...

Thank you, I could say it, but having someone else confirm carries more weight.

Quote from: Pedroig on April 24, 2020, 11:46:32 AM

1.  You indicate that shaded cells are for input, but then have many shaded cells with formulas in them, like the entire Thermal Strength row.

You're quite right, that is conflicting information.

Some personal preference that survived the preparatory cleaning.

Armor and shields in fleet composition come down to this for example.  I tend to use it as a baseline starting point, and adjust from there, but there is absolutely no harm in wiping out the formulae in those cells and dropping in whatever suits you, its what I've just done to it since I suspect it will be needlessly confusing if it remains, and most likely overwritten immediately anyways.

Gave another pass looking for such, and cleared out what I noticed....I probably missed something somewhere, by all means mention it if you find it.

Quote from: Pedroig on April 24, 2020, 11:46:32 AM

2.  Thermal Strength tooltip is wrong for C#:  For ships and missiles thermal sensor formula is Max Engine Output * (Current Speed / Max Speed) * Thermal Reduction (You don't account for TR which is fine, but tooltip indicates ship size is a factor, which it is not)

Do you mean the Note for cell O1 in Sensor Data?

If so, I've edited it down to just what it is, given that I also cleared out the formula sitting in a set of cells expecting user entry.

Quote from: Pedroig on April 24, 2020, 11:46:32 AM

3.  Sensor Data B5:B7 does not seem to modify graph.

B5 seems to be erroneous indeed, with ranges in hundreds of millions, entering 1 for a range limit of 1 million km does nothing while the chart shows ranges up to 600 million km, but 0.1 does limit maximum to 100m. I missed a 1000 multiple somewhere when I changed from showing thousands to millions, the range limit was still being multiplied by 1000 to get thousands from it. 
Fixed.

entering 1 in B6, for a minimum tonnage of 1 ktons works as expected
entering 1 in B7, for a maximum tonnage of 1 ktons works as expected

Note that excel does insist on having the chart a little bit larger than its data set, the data ends where one would expect it to, and the chart re-scales accordingly.

Quote from: Pedroig on April 24, 2020, 11:46:32 AM

4.  Fleet Comp:  Changing a select value changes all values under it til it hits a different valued cell.

Ah, that is an oversight I meant to clear out.  I tend to keep an eye on what the current tech would mean for ships I frequently build, so the main group of ships was tied to the same tech to ease updating them all, and I add my own to the bottom, as it should have already been.

Cleared it out, will not be an issue going forward.


Wiped out some personal preference formulas that should have been killed before I set it loose.
Corrected 'Sensor Data'!B5's dependents so b5 works correctly again
Unhid Fleet Composition Column D(engine tech power rating) and moved it to safety in the main calculations section before it gets deleted accidentally.
Punted the shield fraction from Roles - While I used it that way, the formula in a cell expecting entry should probably die.

Updated the main post.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v3
Post by: amram on April 26, 2020, 05:51:09 AM

V3 changes:

• Added in some missing notes.
• Found some typos.
• Cleaned up some of the number formats a bit more in both fleet composition and missile data.
• Changed 'PD Turret Calculator'!H27:H30 to show actual bonus gained, rather than relative percentage (actual bonus vs actual bonus / max bonus)
• Forgot to shade in 'PD Turret Calculator'!C26:C27 and 'PD Turret Calculator'!E27:E30
• Made the sensor HS in for an active PD sensor in 'PD Turret Calculator' work with 7.1 sensors - for quasar when its ready.
• Switched 'PD Turret Calculator' reactor output tech entry in 'PD Turret Calculator' to use the tech name, rather than the corresponding 1hs output.
• 'PD Turret Calculator' now supports Quasar tracking bonuses — using 7.1's expected outcomes, am aware 7.1 is broken, it doesn't use them.
• Found and removed a couple more random values I had scattered around.
• Converted all cell references in the About sheet to manipulate named ranges - I shouldn't have to adjust text nor the hyperlinks after this - your own edits might also not break the links and so keep the help relevant.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: Alsadius on April 26, 2020, 08:47:44 AM

Cool sheet. A few thoughts:
- For cells like Sensor Data!H5:14, you've got TRUE/FALSE options with no data validation. Copy the B2 cell there, so that you give people the dropdown box.
- The way you used number formats in cells like Sensor Data!R:Z5 is really cool. I want to find an excuse to steal that for my other projects now.
- I was going to play around with it more, but your sheet kept crashing my Excel :/

Quote from: Demonides on April 24, 2020, 10:13:43 AM

Personally, I don't download zipped office files where macros can be added by strangers. That is why I suggest that you provide the above document in the form of Google online doc.

It's a .xlsx file, which cannot have macros. For modern office formats, .xlsm is the extension you need for macros to exist. Also, modern versions of Excel have pretty good security, so even if you open a .xlsm file, there's two separate "I trust this" buttons you need to hit before macros are active - you need to enable editing, and then enable macros. (I deal with this a lot for work)

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
Post by: amram on April 26, 2020, 09:10:43 AM

Quote from: Alsadius on April 26, 2020, 08:47:44 AM

Cool sheet. A few thoughts:

Keep them comin

Quote from: Alsadius on April 26, 2020, 08:47:44 AM

- For cells like Sensor Data!H5:14, you've got TRUE/FALSE options with no data validation. Copy the B2 cell there, so that you give people the dropdown box.

So obvious, and I'm oblivious to it, lol, I usually just type it in.

'Sensor Data'!H5:H14, 'Sensor Data'!n5:n14, and 'Fleet Composition'!R6:S85 are now validated TRUE/FALSE as well.

Updated copy incoming.

Quote from: Alsadius on April 26, 2020, 08:47:44 AM

- The way you used number formats in cells like Sensor Data!R:Z5 is really cool. I want to find an excuse to steal that for my other projects now.

Its all in the cell properties, I like having number formats take care of little things, because the values remain unharmed and every bit as useful, its all cosmetic.

Quote from: Alsadius on April 26, 2020, 08:47:44 AM

- I was going to play around with it more, but your sheet kept crashing my Excel :/

If you can figure out what's doing it, there are often a couple ways to get things done, I might be able to do it a different way that doesn't crash you.

I pretty heavily abuse fairly recent features.

Anyways, attached the separate sheets to this post, updated the main post with the full book.

Quote from: Alsadius on April 26, 2020, 08:47:44 AM

It's a .xlsx file, which cannot have macros. For modern office formats, .xlsm is the extension you need for macros to exist.

TIL, didn't know that one.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v4
Post by: Alsadius on April 26, 2020, 10:24:02 AM

I tried cleaning up your formulas for sensor range calculations, because it seemed way too complex. Most of the crashes were when I was trying to copy the new formula into a couple thousand cells at a time, looked like it just barfed on the size of the job. Which is really weird - I've handled bigger things no problem before - but maybe it was having a graph of it up that did it or something.

I only really looked at the sensors tab, but I can make one extra suggestion - your formulas are huge. If you ever find an error, or Steve ever changes the math, you'll have a hell of a time. What I've learned to do over the years is use more helper cells to do part of the calculation. The logic is basically the same as using formulas in coding - you isolate things, simplify the logic, and make it easier to follow what's what. (You can even name your cells if you'd like - the Named Ranges button on the Formulas tab of the ribbon lets you simply say "D5:D12" is now called "Names", and reference it accordingly.)

For reference, here's a sensor formula I made for simplicity of coding/understanding (with cell references replaced with descriptions)

Range in km
=SQRT(Size*EM strength*Grav strength*Resolution^(2/3)/PI())*1000000*IF(Type="Sensor", 1, 2)

Range at a given target size in HS
=MIN(1, (Target size/Resolution)^2)*Nominal range

That doesn't include a C#/VB toggle, but you can see how much simpler it is. INDEX/MATCH is a fun trick, but you don't need it here. Instead of a lengthy lookup of what the range is, just add header rows for range and resolution, which are copied from the spots earlier on the sheet where they were already given. Move everything down two cells, and the formulas get like 80% shorter. Likewise, you've got (cleaned up) function bits like IFERROR(Range*MIN(1,(Previous tonnage tested/Resolution)^2)=Range,FALSE). Why isn't that instead IF(Previous tonnage tested >= Resolution, TRUE)?

I'm not trying to bring you down here - I've gotten bonuses from my employer for Excel work on this level. But I've also had the headache of maintaining it since then, so I'll encourage you to learn from my pain ;)

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v5
Post by: amram on April 26, 2020, 04:51:45 PM

Most of the bulk is from embedded lookups and occasionally repeats to get a value for logic, or to work with it.  Did a little housecleaning.  Some of the bigger ones should be quite a bit easier to mentally parse now.

I'd done a huge cleanup of the formulae in Missile Data and Fleet Compositions engine/boost - if you thought the formulae in Senor Data were big......, hadn't really hit Sensor Data yet with such a pass.  Done.  Mostly its the lookup for Max range, and resolution, since they are needed in a few places for logic before settling on do it and keep the result.  I need to dig into and mitigate repeated work anyways, the sheet is huge, its still quick but not instant for me, for some it may be noticeably grinding on workload at times.

I only fairly recently made the jump from 2k7 to o365, so I'm probably abusing a few things in ways I shouldn't for a project this demanding, triggering re-calc in places where its not needed because I've gone and made it volatile without realising, which is another thing I plan to try and track down and smite if I can.

Self taught in excel, so I'm pretty much guaranteed to have at least some bad habits.  But don't worry, I can handle constructive criticism, bring it on, lol.

Named cells always seemed more difficult to work with than just having a raw address to me.  They read better, agreed.  If you forget that cell's address though, you're stuck poking through the name manager to remind yourself where it is. With with many names(and I'm starting to get there...), and multiple neighbouring references, that can be tedious.  With addresses you can see at a glance where it comes from, and which highlight that one is if you want to change it via drag and drop.  Still, replaced most of the ranges with named ranges this pass too.


V5 changes:

• Moved all the C# toggles to put the entry in cell C3....because reasons.
  
• Sensor Data
  • Had missed a column in verifying a given tonnage had a sensor to graph.
  • Eliminated a case that existed from when the graph limits had a minimum range - never did get excel to let me move the minimum Y axis dynamically without VB.
  • Simplified the formulas for the graphing prep work - punted the repeated lookups for max range and resolution to helper cells
• Fleet Composition:
  • Added a note for Subdivide Tanks -> H5
  • Had been re-calculating engine quantity four times each boost, now just once and reference the one result.
  • Found and fixed a bug in the formula for engine EP necessary per boost - one cell reference to max boost was row locked, should not be.
  • Converted Armor Type, Engine Type, Max Eng HS, Armor Tons, Want Speed, Engine Power, Desire Range, to use named ranges instead of cell ranges.
  • Converted Role range, K tons, Build This, Military references to named ranges
  • Added some more missing notes -> Role column headers
  • Pushed the Ship-Role row-match out to a helper so it gets done once rather than 5 times.
  • Moved tug mass formula from sitting in the user entry, to an assumed value when user entry is not given, added column to over-rule it.
  • Rewrote TUG Mass to be a bit less convoluted.
  • Added isTUG column to Roles so its not locked to a name anymore. - Military ships with an isTUG role use maximum military ship tonnage, likewise for civilian using civilian.
• Missile Data:
  
  • Was repeating the lookup for Engine EP/MSP for missiles in Velocity, Range_mkm, and ENG_PWR, now has a helper cell next to ENG_PWR.
  • Changed ENG_EP to use a named range rather than cell reference for both c# and 7.1
  • The C# toggle wasn't shaded, should be since its a user setting.
  • Converted Engine Tech, and every missile ENG_HS lookup, to use named ranges instead of cell ranges.
  • Added another row to the Missile Tech, to store and reuse the looked up missile configuration row, rather than look it up 15 times per missile.

Main post updated.

Quote from: Alsadius on April 26, 2020, 10:24:02 AM

shorter. Likewise, you've got (cleaned up) function bits like IFERROR(Range*MIN(1,(Previous tonnage tested/Resolution)^2)=Range,FALSE). Why isn't that instead IF(Previous tonnage tested >= Resolution, TRUE)?

...Late edit...it probably should be...

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v6
Post by: amram on April 26, 2020, 08:29:31 PM

V6 Changes:

• Got my hands on the FastExcel Profiler Bundle trial and went a murderin', lol.  Took the nastiest formula from 4,000ms, to the worst being 108ms.
  
• Rewrote the boolean for the sensor graph prep work.
• Punted the grunt work for TUG mass to a pair of helper cells.  Determining maximum tonnage mil/civ happens in just two cells now, rest look at those.
• Used an excel profiler: 80% of the book's workload was in the missile data, specifically, the visual indicator for min AGI/ENG cost.  Reworked it.  Update changed just those 30 columns costing 3773.82 millisec, to the entire workbook costing 839msec, and those columns down to just 63 millisec total.  Still pretty volatile though, work continues to trim fat.
• Pulled apart the AGI, ENG, and visual indicator to isolate some row by row constants, and stashed them at the very bottom to reference rather than repeat the effort, yet more gains had.

Its enormously more responsive now.  Main post updated.

Ok, That's enough digging through this sheet for one day.....(lies, I'll probably be tampering in a couple hours again...)

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v7
Post by: amram on April 27, 2020, 08:21:29 PM

V7 changes:
   

• Found another spot where I still had some lingering formulas in cells expecting entry - cleared it.
• Rewrote the missile AGI/ENG MSP calcs to use a stepped method, reduced rows needed from 1450 to 150.
• Leveraged the now considerably shorter AGI/ENG cals to do two additional complete passes, one with .05 more boost, and one with .05 less, sheet can now indicate if raising or lowering boost will increase range.  And even still, it is ~70% less workload than previous.
  • "5.9 x "  where x is an up arrow indicates increased range with 5.95 boost
  • "5.9 x " where x is a down arrow indicates increased range with 5.85 boost
       

blegh, forum won't let me have the arrows here, lol.

First post updated.

A note on the boost indicators - you're basically doing a manual binary search where all you know is more/less than what you just tried.  Like this: https://encrypted-tbn0.gstatic.com/images?q=tbn%3AANd9GcSc_UMsDhGqEBhTHkFjaIWG4yCdY_WfStTYG0YpL4Ja8RQjBDox&usqp=CAU

As boost decreases, the results will have increasing amounts of AGI, so the missiles will be slower to obtain the same interception odds.  So at some point you will find them too slow, that one I can't help with, its all your call.

I can't indicate what boost is ideal without an excessively demanding amount of workload on the sheet, just one missile would bring excel to a halt for a while, to say nothing of 30 of them, so all I have done is calculate for the next boost up and down from the one you last chose, and see if range improves in either direction and indicate if it does.

To save yourself tweaking boost in 0.05 increments, make bigger steps until it changes its answer. 

Since you don't know how much to change, guess and move that much, when you overshoot, go back a smaller amount, repeat until you're happy with it or find optimal.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v7
Post by: Zenrer on April 28, 2020, 02:13:33 AM

There's an error when it comes to TUG mass in the fleet comp. The instructions say to input the TUG mass in kton, but the sheet won't spit out the correct config unless you're inputting it in tons instead. From a quick look ,just looks like a calculation error in the AP column.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: amram on April 28, 2020, 03:13:35 AM

Noted, and fixed, main post updated. 

Seems that was missed when I switched from using raw tonnage for entry, to kilotons for easier entry.

V8 changes:
   

• Tug Mass was not being multiplied into tons as it was supposed to be, so it required entry as tons, rather than kTons as indicated.  Fixed

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: Pedroig on April 28, 2020, 08:37:16 AM

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.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: amram on April 28, 2020, 10:23:00 AM

Quote from: Pedroig on April 28, 2020, 08:37:16 AM

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?

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: Roughshot on April 28, 2020, 02:13:49 PM

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.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: amram on April 28, 2020, 02:16:37 PM

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?

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: Roughshot on April 28, 2020, 02:19:56 PM

Quote from: amram link=topic=11064. msg128898#msg128898 date=1588101397

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.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
Post by: amram on April 28, 2020, 02:22:34 PM

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.

Title: Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v9
Post by: amram on April 28, 2020, 09:14:48 PM

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.

Title: Re: Ship, Sensor, Missile, Turret Design: Google Sheets & O365 excel — v9
Post by: esavier on April 29, 2020, 06:00:16 AM

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?

Title: Re: Ship, Sensor, Missile, Turret Design: Google Sheets & O365 excel — v9
Post by: Cedras on April 29, 2020, 06:13:40 AM

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

Title: Re: Ship, Sensor, Missile, Turret Design: Google Sheets & O365 excel — v9
Post by: amram on April 29, 2020, 11:35:23 AM

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.

Title: Re: Ship, Sensor, Missile, Turret Design: Google Sheets & O365 excel — v9
Post by: amram on April 29, 2020, 01:09:36 PM

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

Title: Re: Ship, Sensor, Missile, Turret Design: Google Sheets & O365 excel — v9
Post by: amram on April 29, 2020, 06:21:21 PM

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.

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v11
Post by: amram on April 29, 2020, 08:14:55 PM

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.

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v12
Post by: amram on May 04, 2020, 11:25:41 AM

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.

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v12
Post by: Iceranger on May 04, 2020, 01:09:53 PM

Quote from: amram on May 04, 2020, 11:25:41 AM

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

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v12
Post by: amram on May 04, 2020, 01:20:48 PM

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

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v12
Post by: skoormit on May 06, 2020, 11:07:19 AM

Quote from: amram on May 04, 2020, 01:20:48 PM

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

Oh, it's certainly possible.
After all, every piece of custom business software merely implements a subset of the functionality of Excel.
And the ship design screen certainly acts like a piece of custom business software.

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v12
Post by: amram on May 06, 2020, 11:32:39 AM

Note that I said no good way, rather than just no way.  Without macros, we get one of either way too many rows, or way to many columns, way too many sheets, or all of the above.  Otherwise its too limited to be useful.

Look at how many columns there are now, and that's just deployment, range, engines, fuel, shields, and armor.

Suppose I make a new table for weaponry.  You design a weapon there, and in Fleet Comp sheet, we want to reference it.  You'd need two columns, one for quantity and one for the reference.  Two columns doesn't sound bad, but then, you aren't limited to one weapon, should the sheet?  Probably not since that would hobble its usefulness pretty badly, say we make it 5 weapons, its 10 columns now.

Repeat this for every component type, be it racial or otherwise needing at minimum two entries for item and quantity, and more if its sensible to expect multiple types.

I could compress that a bit moving to two rows per hull, so the quantities can exist in the same column, compressing it horizontally by spending vertical space.  It will still be extremely wide, there's how many separate components it would make sense to have in one ship together?

I could go vertical, it would not be as tall as it would be wide. It does make it more difficult to keep more than a couple designs on screen at once since the columns become quite wide to fit the widest entry, it just moves the overview problem.

I could take a different approach, and have a mission package sheet, whose whole point is collecting up multiple component names and quantities under a single reference, and then only have to reference a couple mission packages in Fleet Composition, that solves the UI problem....by sweeping it off to another sheet, making a UX problem, scattering stuff all over the place.

Couple that with keeping fleet Composition, but treating it less as a design page and instead as an overview of a new designs page, that keeps the overview functionality, but also expands the number of sheets to manage and switch between.

It'll certainly fit into excel, we have more than enough rows, columns and sheets, but I've yet to work out a sensible way to build it and keep it both functional, manageable, and useful, I've got a couple abortive attempts, they get out of hand pretty quickly.

Title: Re: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v13
Post by: amram on May 14, 2020, 10:10:23 AM

Finally had some time to sit down in Aurora and play a game, so I was actively using the sheet again.

Found I had horribly broken the ship engine calculation before when I added the helper cell, so it typically suggested only that you max out the engine multiplier since it was ignoring fuel due to looking at an empty cell.  Hadn't realised until I used the sheet in a game again and it gave me nonsense suggestions.

V13 changes

• Fixed a minor formatting issue in the PD Turret Calculator when the estimated PD active Search sensor would exceed HS 50:  a value of 75.76 would end up as 575 as a consequence.  Fixed that.
• Forgot to make the PD Turret Calculator section for determining an appropriate active sensor also use 15 tons as MCR when set for C#, fixed.
• Stopped Missile Velocity from showing when there is negative fuel, just as range and endurance do not show in such case.
• Found and fixed a flaw in Missile Data where the ENG/AGI calc's could end up referencing the text MR instead of a number, and become unhappy about that.
• Stopped the boost increase/decrease indicators from seeing "" as a value that could be greater than any actual number.
• Fixed a math error in the MSP required estimate for PD Turret Calculator, because I am a derp.
• Added more notes to PD Turret Calculator for the results section.
• Changed formatting to flag a failure to obtain the requested bonus, rather than max bonus.
• Pushed some values out to helper cells in missile data, to facilitate better matching aurora's rounding of values, and so better match C#'s missile results.
• Missile engines in c# now change step sizes as happens in C#, with 0.01 until 3.0, 0.1 from there until 10.0, 0.5 from there until 50
• Had managed to break the engine calcs when I last altered them to use the new helper columns, a cell reference didn't adjust correctly, they were ignoring fuel quantity, fixed.

Main post updated.