Home Ready Room

Captain Idol's Voyage Analysis Sheet (CIVAS 2.1) - Comma Bug fixed

Captain IdolCaptain Idol ✭✭✭✭✭
edited July 2022 in Ready Room
What's New!
* New Input Field, with converter to help move over your old data
* A Date selector, choose the range in which you want to view
* Voyage Limiter, only want the 5 most recent voyages, choose that here
* Voyage date comparisons, want to know the difference between 2021 and 2022, you can do that here too

You know, it's been a while since I made a new sheet, who likes mindless data entry, no one? Okay then. Let me present...

Captain Idol's Voyage Analysis Sheet 2.1...and yes, I had to call it CIVAS for my dumb naming convention.

c9wqr85pl7f6.png

So what is this for. I came upon the problem that with More Golds, More Problems or specifically, who exactly was I using day to day, and who was languishing on the sidelines, destined for the cold embrace of the freezer.
Yes, this will require some manual entry on your part to keep track of whom you are sending day by day but I have found it invaluble so far for keeping track of which pairs are trending higher, where I need to put more cites and who has fallen off in recent weeks.

What you can see on that page:
  • * Unfrozen 5/5 Crew Who Never Voyage - Good to find those slackers not earning their pay, however this will include all your Gauntlet wonders and Base gods so best to parse that list finely. This is the only section that needs the Datacore import.
  • * Count Of Top 12 - Each pair will have a top 12 list of who is the best at that combination, this colates that list into something that represents the more powerful of your roster. If it's not Braxton at the top, go get Braxton and put him on the top.
  • * Count Of All Voyages - This is less accurate for analysis but represents who fills a lot of seats on your voyages, again, Braxton to the top please.
  • * Voyagers Not On Any Top 12 List - Simple people who are riding seats for you but don't quite make that top cut, not useless and may fill an obscure seat somewhere, but a good indication to see who is starting to fall out of favour due to some new hotness taking their place
  • * Time/Date estimations - This isn't essential to fill out but something to help see where nears improvement, or just where RNG is hitting you the hardest.

Instructions are inside, hope you have fun and may your voyage be blessed with a twelve hour length!

CLICK HERE FOR SHEET

Comments

  • Thank you, since publication I use this sheet.
    German 5618 For more info on us, please check our wiki page: Fleet => click STT Wiki
    ━ Do as you would be done by ━
    Fleet with international Members
    Vice Adm. | Recruiting
    Founded in August 2017
  • Sven LundgrenSven Lundgren ✭✭✭✭✭
    There seems to be some issue with Bashir, Julian Bashir being recognized.

    m80zvqvn5a3l.png
  • MururoaMururoa ✭✭✭
    edited October 2021
    Hi,
    This looks great but after deleting the sample entry I get #N/A in AP6.
    This disapears if I enter a new CMD/DIP entry.
  • Sven LundgrenSven Lundgren ✭✭✭✭✭
    Mururoa wrote: »
    Hi,
    This looks great but after deleting the sample entry I get #N/A in AP6.
    This disapears if I enter a new CMD/DIP entry.

    I saw that too so I just left it until I get a CMD/DIP to over write the sample with.
  • PeetsPeets ✭✭✭✭
    Copy and empty cell into it. That worked for me.

    I love this sheet. Awesome stuff!
  • Appreciate your fabulous tools, as always! There does seem to be some sort of formula error in cell AP6. When I attempt to download it as an xlsx it tries to repair the sheet and just deletes that formula. I will continue using it on Google Sheets though as I'm very excited about this new tracking sheet!
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Appreciate your fabulous tools, as always! There does seem to be some sort of formula error in cell AP6. When I attempt to download it as an xlsx it tries to repair the sheet and just deletes that formula. I will continue using it on Google Sheets though as I'm very excited about this new tracking sheet!

    Yeh Google Sheets have some different formula than regular excel, in this case I believe it's a QUERY formula which Excel doesn't supports so, like the majority of my other sheets, these will only work in Google.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Added in an Anti-Matter remaining section on each pairings with an average in the pair list too. For those that like to see how much the RNG hates them :smiley:
  • So, I ran into the AP6 problem a while back. If you encapsulate the whole formula in IFERROR(), it goes away.

    There is another problem which I've run into and I believe it is the same as the Bashir, Julian Bashir issue. Mine is Tyler, Son of None. There is "Tyler" on "Voyagers not on any Top 12 List" and also on "Count of All Voyages". Simultaneously, there is Tyler, Son of None on "5/5 Unfrozen Crew Who Has Never Voyaged". The issue that I believe is occurring is when "Count of All Voyages" is being appended from Array to a single cell. That step means that the names are separated by commas so it is "..., Tyler, Son of None, ..." So when you separate it to individual card names, they become E3 = ... E4 = Tyler and I'm not sure if Son of None is considered separate or breaking it because I believe he may be last on my list.
  • Okay. Figured it out. Unlike the Badda-Bing, Badda-Bang collection getting counted twice on CIDAS (and thanks for a better, more intuitive fix on that than I was trying, Idol!), this was pretty simple.

    Simply change all the ARRAYFORMULA delimiters (the &"," part) and SPLIT delimiters (the , "," part) to something that you're not going to see in a name but is still a reasonable, even standard delimiter so you won't run into this problem again down the road. I used ;. Just remember, you have to do this on the "Count of All Voyages" list, "Count of Top 12s" list, all the hidden paired lists, and all the individual top 12 lists. If you still get Bashir instead of Bashi, Julian Bashir, you've missed something. Do a replacement search and include formulas.
  • Hi @Captain Idol. I have a question. I've been using this sheet for about 5 months now and it is *so* useful but what I'm starting to discover is that, as time goes on, those who have been around but aren't being used as much aren't dislodged from the Top 12s very easily. Worse, those on a rocket don't rise quickly at all, or ever. Consider the following.

    First, some perspective. Let me start at the beginning. Of Timelines. That's when I started playing. I've never spent a dime on it. I am not the kind of person who spends money on mobile gaming but am very willing to do my part with adwarps, etc. Translation: the kid gets my money and I have been watching Star Trek since I was 6. We're talking TOS movie days. Early. But I was watching reruns at that point. No seeing scientists hung upside down Predator style in WoK.

    I digress. My point is there is no way I'm not committed to this game in the long term. Over the years, I've been using different sheets and resources to figure out the best way to determine who is a rockstar, a team player or a specialist on my voyages. So, when I completed Bell Riots Bashir -- remember when you had to earn honor the hard way and there were no citation sales? -- , my first or second 5-star, he was all over the place on my voyages. He became a voyage rockstar. He was on practically every voyage I ran. If I had been using this sheet for 5 months then, he'd have a massive run count, his Top 12 count would be 27 or 28 and I would have 25+ others with 10+ Top 12s.

    Right now, I have 3 voyagers with 20-24 Top 12s and 2 more who are 18-19 and duck in and out of 20 depending on the event type, faction, crew, trait, etc. I have 10 more above 10 and a spread of 20 who have at least 1 Top 12 run on the sheet.

    This sheet is great because it builds up individual, group and skill statistics. The one problem is that the longer you use 1 copy, the harder it is for a rocket to crack Top 12s. For instance, at the last citation sale with begolds, I picked up Mycelial Culber. Using Datacore's Citation Optimizer, I realized he would be the best route to go first. I set aside the plan I had in terms of citing (Rurigan -- he still got 2 and is #1 on the list to get finished at the next sale) and immortalized MC. The guy whose name rhymes with "poo" is an absolute rockstar. I haven't been able to get through all the skill pairs yet but I suspect he will wind up on Top 12 for 18-24 of them. That is if I start a new sheet.

    c1sqdlleac6j.png

    That right there is the top of my list.

    b5e5khifpwx9.png

    The bottom for Top 12s on the right. See Culber there? Notice his 21 runs on the left. He has run in 9 separate skill pairs so far. If I had just started this sheet, he would have 9 Top 12s and would continue to rack them up. But with Medic Jett having almost 80 runs, even if she's slipping, I won't know where he is or that she's slipping for a long, long time. You can see similar run-ups by others.

    So, now we get to my question. Is it possible to to adjust to a sliding window for ARRAYFORUMULA? Or is this not allowed by definition? Because, it seems to me that, if we limited each Top 12 calculation to 5 runs or something like that, then we could see someone slip off even if they have 80 runs or we could see a rockstar rise on a year-old sheet. The best part is you really only have to edit the Top 12 formulas and/or the stuff under the hood that they crunch from. Everything else hoovers those up.
  • PeetsPeets ✭✭✭✭
    I keep my last 200 voyages because of that and remove older ones to keep track of crew that is useful now.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Here it is! The brand new CIVAS (Captain Idol's Voyage Analysis Sheet) **Version 2.0**, containing some big new additions! A new input field (and converter to help move your old data over), a date selector, voyage limiter and the ability to compare your voyages between two different dates!
    Check it out here: https://docs.google.com/spreadsheets/d/1edSWAoaqFMK4S9CSI7Qk21iNSWKExZGeySp06CW1B4c/edit?usp=sharing
  • Captain IdolCaptain Idol ✭✭✭✭✭
    n7vv1lqa547w.png

    A practical application, this is my 2021 compared to 2022, can def see who's rising and falling.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Fixed a bug with Unique Crew showing the wrong number
  • There seems to be some issue with Bashir, Julian Bashir being recognized.

    m80zvqvn5a3l.png

    This did not get a fix in v2.0. With more sheets, obviously, there are more chances for disaster. I haven't fully investigated using my fix from v1.0 but it does seem to work in the most remedial sense.
  • I'm having a problem with date formatting. The old formatting from Datacore was all standard American formatting MM/DD/YYYY, but some of the cell locations that the data goes into plus the sliding settings such as on the VOY Dates page appear to be in the more standard European format DD/MM/YYYY.

    I used the converter page to move my data but, frequently, the dates, runtimes and AMs remaining all converted to less-than-meaningful decimals instead of sensible values, so I'm not sure if it's my data, the conversion, the new forms or the new settings.

    Also, the dates related to last-used don't seem to make sense (when they are not 31/12/1899, which is the vast majority of the time) and I'm not sure how to read them yet.

    Unfortunately, I"ve tooled around with my ";" for "," fix (for Bashir, Julian Basir, Tyler, Sone of None, and others) and have reformatted the data to try to get everything to make sense. Is anyone else seeing this?
  • Captain IdolCaptain Idol ✭✭✭✭✭
    Yeh I'm aware of those. I think for the next version I'm going to use ISO dating (YYYY-MM-DD) to try and keep it universal. I'll let the datacore team aware.

    For the meaningless numbers, I'll fix that in a future version but for now you can select the cell and go Format -> Numbers -> Date and that will fix it.

    Comma's are a bane. I've put a JOIN/SPLIT in somewhere for comma entries and I'll look to fix that one too if I can, though for now just put the crew name without commas. The only part that will break is the "Not Ever Voyaged" section.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    I'm having a problem with date formatting. The old formatting from Datacore was all standard American formatting MM/DD/YYYY, but some of the cell locations that the data goes into plus the sliding settings such as on the VOY Dates page appear to be in the more standard European format DD/MM/YYYY.

    I used the converter page to move my data but, frequently, the dates, runtimes and AMs remaining all converted to less-than-meaningful decimals instead of sensible values, so I'm not sure if it's my data, the conversion, the new forms or the new settings.

    Also, the dates related to last-used don't seem to make sense (when they are not 31/12/1899, which is the vast majority of the time) and I'm not sure how to read them yet.

    Unfortunately, I"ve tooled around with my ";" for "," fix (for Bashir, Julian Basir, Tyler, Sone of None, and others) and have reformatted the data to try to get everything to make sense. Is anyone else seeing this?

    Good news, I've fixed it. Enjoy!
    https://docs.google.com/spreadsheets/d/1utIuwIgIRO7mwYMSP3P9eWEygf1YT9k24A8BZ_l4pOw/edit?usp=sharing
  • edited July 2022
    Thanks for the fixes!

    This spreadsheet is super good. Here's what I did.

    1) I re-converted and re-entered all my v1.0 data. It was tedious and it sucked but I did it because I tried switching my dates to various forms at some point and could no longer tell what were the actual dates.
    2) I checked them against my 1.0 sheet to see what was North American format and what was European format and switched it all to NA format.
    3) I copied my Skill Order and date columns over to Excel. I made a copy of my date column next to it and highlighted. I did a Right-click -> Format cells -> Date -> YYYY-MM-DD. I spot-verified several entries I had changed the order on.
    4) I copied the resultant data back to my Input sheet. Then, I spot-checked them again.

    All of my date problems cleared up.

    As for the comma problem, I could only find 1 instance of it left: on the Count of All Voyages list on the Limited page. The way I see it, you have 3 options:

    1) Accept that you will have the name split and therefore you will have 2 lines with duplicate numerical values.
    2) Remove the comma from the card names as suggested. This has the least risk as far as active solutions go.
    3) Do a formula search for "," and replace it with ";". There are only 2 instances (for now) but it changes the appending delimiter from , to ; and eliminates the confusion.

    Thanks again, Idol! You do great work!
  • Captain IdolCaptain Idol ✭✭✭✭✭
    edited July 2022
    Thanks for the fixes!

    This spreadsheet is super good. Here's what I did.

    1) I re-converted and re-entered all my v1.0 data. It was tedious and it sucked but I did it because I tried switching my dates to various forms at some point and could no longer tell what were the actual dates.
    2) I checked them against my 1.0 sheet to see what was North American format and what was European format and switched it all to NA format.
    3) I copied my Skill Order and date columns over to Excel. I made a copy of my date column next to it and highlighted. I did a Right-click -> Format cells -> Date -> YYYY-MM-DD
    4) I copied the resultant data back to my Input sheet.

    All of my date problems cleared up.

    As for the comma problem, I could only find 1 instance of it left: on the Count of All Voyages list on the Limited page. The way I see it, you have 3 options:

    1) Accept that you will have the name split and therefore you will have 2 lines with duplicate numerical values.
    2) Remove the comma from the card names as suggested. This has the least risk as far as active solutions go.
    3) Do a formula search for "," and replace it with ";". There are only 2 instances (for now) but it changes the appending delimiter from , to ; and eliminates the confusion.

    Thanks again, Idol! You do great work!

    Argh and I thought I had gotten them all. Will have to give this one a think. Thanks for the feedback.
    Edit: I might have a fix already, for now if you goto the formula, replace the last two "," with "%" that should fix it. I'll push it to the main one soon
  • Consistency is good. It's hard to find a good delimiter, though. Comma is obviously a problem. ; Is used in code so it creates a need for constant escapes (ugh) and % is part of standard address code for escaped characters. I don't envy you, @Captain Idol .
  • JhamelJhamel ✭✭✭✭✭
    A shy question: Is there already a tool that reads your crew data and gives you the ideal setup for a voyage (especially reading the bonus AM for ideal slotting), meaning it reads the voyage from the game and knows your crew roster?
    "Everything about the Jem'Hadar is lethal!" - Eris (ST-DS9 Episode 2x26 "The Jem'Hadar")
  • ~peregrine~~peregrine~ ✭✭✭✭✭
    Jhamel wrote: »
    A shy question: Is there already a tool that reads your crew data and gives you the ideal setup for a voyage (especially reading the bonus AM for ideal slotting), meaning it reads the voyage from the game and knows your crew roster?
    Yes, there is this one on the DataCore website:

    https://datacore.app/playertools?tool=voyage
    "In the short run, the game defines the players. But in the long run, it's us players who define the game." — Nicky Case, The Evolution of Trust
  • JhamelJhamel ✭✭✭✭✭
    Thankies so muchies! :) I'll have a look.
    "Everything about the Jem'Hadar is lethal!" - Eris (ST-DS9 Episode 2x26 "The Jem'Hadar")
  • <TGE> Darxide<TGE> Darxide ✭✭✭✭
    A suggestion for CIVAS if I may? How about a place to enter a white-list of crew to manually enter in your Gauntleteers and other 5/5 crew that you know will never voyage, but you keep for other reasons? It would help the "5/5 Unfrozen Crew Who Has Never Voyaged" list from getting out of hand which is happening in my case between Gauntleteers, daily shuttlers, and commonly used FBB node clearers.
  • Captain IdolCaptain Idol ✭✭✭✭✭
    A suggestion for CIVAS if I may? How about a place to enter a white-list of crew to manually enter in your Gauntleteers and other 5/5 crew that you know will never voyage, but you keep for other reasons? It would help the "5/5 Unfrozen Crew Who Has Never Voyaged" list from getting out of hand which is happening in my case between Gauntleteers, daily shuttlers, and commonly used FBB node clearers.

    Right now I'm not working on any of my sheets anymore as I'm currently not playing. Others are updating the Hyper Sheet. If anyone wants to they can take it over and push updates at their own discretion.
Sign In or Register to comment.