Captain Idol's Voyage Analysis Sheet (CIVAS 2.1) - Comma Bug fixed
Captain Idol
✭✭✭✭✭
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.
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
Author Of Captain Idol's Datacore Analysis Sheet, Timelines Backend Stats Super Hyper Edition,Captain Idol's Voyage Analysis SheetCome join the Trek Time Wednesday Stream on YouTube at https://www.twitch.tv/TrekTime and catch up with old episodes at https://www.youtube.com/@TrekTime Join our Discord at https://discord.gg/HmNrwakbN9
9
Comments
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.
I love this sheet. Awesome stuff!
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.
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.
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.
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.
That right there is the top of my list.
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.
Check it out here: https://docs.google.com/spreadsheets/d/1edSWAoaqFMK4S9CSI7Qk21iNSWKExZGeySp06CW1B4c/edit?usp=sharing
A practical application, this is my 2021 compared to 2022, can def see who's rising and falling.
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 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?
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.
Good news, I've fixed it. Enjoy!
https://docs.google.com/spreadsheets/d/1utIuwIgIRO7mwYMSP3P9eWEygf1YT9k24A8BZ_l4pOw/edit?usp=sharing
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!
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
https://datacore.app/playertools?tool=voyage
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.