Author Topic: Ship, Sensor, Missile, Turret Design: G Sheets & OpenOffice & O365 excel — v13  (Read 9498 times)

0 Members and 1 Guest are viewing this topic.

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
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
« Last Edit: May 17, 2020, 08:55:25 AM by amram »
 
The following users thanked this post: Aoi, davidb86, YABG, unkfester, skoormit, Migi, AJS1956

Offline Demonides

  • Gold Supporter
  • Warrant Officer, Class 1
  • *****
  • Posts: 94
  • Thanked: 145 times
  • 2021 Supporter 2021 Supporter : Donate for 2021
    2022 Supporter 2022 Supporter : Donate for 2022
    2023 Supporter 2023 Supporter : Donate for 2023
    2024 Supporter 2024 Supporter : Donate for 2024
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #1 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.
 

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #2 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.
 

Offline Pedroig

  • Lt. Commander
  • ********
  • P
  • Posts: 231
  • Thanked: 61 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #3 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.
si vis pacem, para bellum
 
The following users thanked this post: amram

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #4 on: April 24, 2020, 02:14:04 PM »
Confirmed, no macros...
Thank you, I could say it, but having someone else confirm carries more weight.

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.

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.

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.


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.
« Last Edit: April 24, 2020, 02:20:33 PM by amram »
 
The following users thanked this post: Pedroig

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
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.
 
The following users thanked this post: skoormit

Offline Alsadius

  • Lieutenant
  • *******
  • Posts: 176
  • Thanked: 87 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #6 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 :/

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)
 
The following users thanked this post: amram

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel
« Reply #7 on: April 26, 2020, 09:10:43 AM »
Cool sheet. A few thoughts:
Keep them comin

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

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


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

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.
 

Offline Alsadius

  • Lieutenant
  • *******
  • Posts: 176
  • Thanked: 87 times
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 ;)

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
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.

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...
« Last Edit: April 26, 2020, 06:09:17 PM by amram »
 
The following users thanked this post: Alsadius

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v6
« Reply #10 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...)
 
The following users thanked this post: Alsadius

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v7
« Reply #11 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.
« Last Edit: April 28, 2020, 03:20:59 AM by amram »
 

Offline Zenrer

  • Leading Rate
  • *
  • Z
  • Posts: 11
  • Thanked: 1 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v7
« Reply #12 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.
 
The following users thanked this post: amram

Offline amram (OP)

  • Lieutenant
  • *******
  • a
  • Posts: 154
  • Thanked: 79 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
« Reply #13 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
 

Offline Pedroig

  • Lt. Commander
  • ********
  • P
  • Posts: 231
  • Thanked: 61 times
Re: Ship, Sensor, Missile, Turret Design Spreadsheet: O365 excel — v8
« Reply #14 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.

si vis pacem, para bellum