Post reply

Warning: this topic has not been posted in for at least 120 days.
Unless you're sure you want to reply, please consider starting a new topic.

Note: this post will not display until it's been approved by a moderator.

Name:
Email:
Subject:
Message icon:

shortcuts: hit alt+s to submit/post or alt+p to preview

Please read the rules before you post!


Topic Summary

Posted 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.
Posted 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.
Posted by: skoormit
« on: May 06, 2020, 11:07:19 AM »

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.
Posted 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...
Posted by: Iceranger
« on: May 04, 2020, 01:09:53 PM »

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

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

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