Community Tool: STT Voyage Planning Tool
PsionStorm
✭✭✭
Hey folks,
This is a PUBLIC BETA. I need your help to make this sheet better! I’m looking for people to test this sheet out, help me find bugs, and help me find ways to improve the results.
I have been spending the past couple of months working on a spreadsheet in my free time that I believe will help users determine the best crew for their voyages. It’s not meant to replace IAP, but it is meant to help fill the void left by the tool.
This sheet requires NO user credentials and has absolutely NO access to your account. In addition, it does not commit any actions for you - it simply makes recommendations and provides education on where there may be gaps in the roster.
To use, make a copy of the sheet before filling out, then simply fill out your Fully Equipped roster crew. Add the appropriate fusion level for each crew (yes, this will work with 1/5s or 3/4s!).Then, under settings, add your starbase and collection bonuses. Under the Voyages tab, change the dropdown in cell I1 to the favored skills. The sheet will provide you with your top voyage crew based on three skills (favored skills + ancillary skill), and fill in any gaps you have with pairs of one of the favored with the ancillary. At the bottom of the Voyages tab will be a list of your top 25 crew by pair.
This sheet is a great way to see how your roster shakes up in certain combinations, but it’s also a great way to determine who to choose on a behold, or who to cite next. If you have crew you’re considering, simply change their number on the roster page and see where they stack up in the Top 50 Voyagers, or run their skill pairs in the Voyages tab.
I would like to thank everyone who has been a part of testing this sheet in one way or another. I’ve gotten a ton of excellent feedback over the past few months and without your recommendations and support I wouldn’t have been able to get it to this point.
Long Term Goals:
- Filtering 1, 2, and 3-star crew from results.
- Filtering frozen crew from results.
- Adding a means to select crew from the list and calculate estimated voyage length. This feature is now working on a basic level.
Q: Why does my x/5 ___ give an error when I add him to the roster?
A: It’s possible there are missing stats for this character. All data is added manually. When Disruptor Beam adds a character, they announce the character’s Fully Equipped stats at their lowest and highest fusion, but rarely in between. Unless I personally get the character or the wiki is updated, I can only put placeholders to remind myself to keep checking those characters for the missing data. If you’d like to help, send me the base stats for each star I’m missing and I’ll add them to the next edition. In the meantime, there is a hidden tab for stats that you can add the numbers into yourself. Just stick to the Base stats. The sheet will figure out the modified totals automatically.
Q: Why can’t I select which is primary and which is secondary?
A: For purposes of this sheet, it doesn’t matter. The two skills are added together to generate a total.
Q: Should I just pick the top 2 from each category and fill in the blanks?
A: I mean, you can, and you’ll probably get a decent voyage out of it. In my experience, you’re probably better off using the sheet as a starting point and trying tweaking from there. Perhaps there’s crew you’d never considered using. Perhaps the sheet isn’t including your 5/5 Surak on that voyage (put him on that voyage!). Take a look at the whole, not just the preferred stats. It makes a difference.
Q: Why are my 1,2, and 3-star crew represented on this sheet?
A: My goal when creating this sheet was to provide a tool that anyone at any level can use. At this time there is no way to filter out that crew, however that is on my list of things to do. For now, if you don’t want to see the lower crew in your results, simply omit them from your roster.
Q: How can I show my level 80 fully fused ____?
A: Unfortunately, the amount of data required to do anything but fully equipped crew is too much for this sheet to handle. However you can use this sheet to get a sense how much impact a fully equipped crew member would have on your current roster. This can help you decide who to work on next.
Q: How can I transfer my roster from one version of the sheet to another?
A: Go to the Roster page and filter out the most recent version. Copy Column B from the old sheet and paste it into the new sheet. Then remove the filter. It should copy the data accurately.
Q: Hey, doesn't this look a lot like the Do Not Airlock Checklist?
A: It definitely does. When something works, it just works. Hopefully Leshy doesn't mind? However I'm open to changing the format as long as there's a good way to distinguish crew rarities and ownership. Suggestions are welcome.
Any questions I haven’t posted here, feel free to ask me below and I’ll do my best to answer them. I am by no means an expert on the game, but I’ve gotten a ton of help from other players and I wanted to find a way to give back to the community. Hopefully this helps everyone improve their voyages (and their roster)! I will do my best to keep this up-to-date on a fairly regular basis. I cannot promise a schedule at this time, but I'm not planning on abandoning the game any time soon either.
STT Voyage Planning Tool Beta 1.13 (12 November 2019):
https://docs.google.com/spreadsheets/d/1sd_81DEEnbrCM0ArZFh1AAzHghZLXRTonaqEu9IuCcw/edit?usp=sharing
This is a PUBLIC BETA. I need your help to make this sheet better! I’m looking for people to test this sheet out, help me find bugs, and help me find ways to improve the results.
I have been spending the past couple of months working on a spreadsheet in my free time that I believe will help users determine the best crew for their voyages. It’s not meant to replace IAP, but it is meant to help fill the void left by the tool.
This sheet requires NO user credentials and has absolutely NO access to your account. In addition, it does not commit any actions for you - it simply makes recommendations and provides education on where there may be gaps in the roster.
To use, make a copy of the sheet before filling out, then simply fill out your Fully Equipped roster crew. Add the appropriate fusion level for each crew (yes, this will work with 1/5s or 3/4s!).Then, under settings, add your starbase and collection bonuses. Under the Voyages tab, change the dropdown in cell I1 to the favored skills. The sheet will provide you with your top voyage crew based on three skills (favored skills + ancillary skill), and fill in any gaps you have with pairs of one of the favored with the ancillary. At the bottom of the Voyages tab will be a list of your top 25 crew by pair.
This sheet is a great way to see how your roster shakes up in certain combinations, but it’s also a great way to determine who to choose on a behold, or who to cite next. If you have crew you’re considering, simply change their number on the roster page and see where they stack up in the Top 50 Voyagers, or run their skill pairs in the Voyages tab.
I would like to thank everyone who has been a part of testing this sheet in one way or another. I’ve gotten a ton of excellent feedback over the past few months and without your recommendations and support I wouldn’t have been able to get it to this point.
Long Term Goals:
- Filtering 1, 2, and 3-star crew from results.
- Filtering frozen crew from results.
- Adding a means to select crew from the list and calculate estimated voyage length. This feature is now working on a basic level.
Q: Why does my x/5 ___ give an error when I add him to the roster?
A: It’s possible there are missing stats for this character. All data is added manually. When Disruptor Beam adds a character, they announce the character’s Fully Equipped stats at their lowest and highest fusion, but rarely in between. Unless I personally get the character or the wiki is updated, I can only put placeholders to remind myself to keep checking those characters for the missing data. If you’d like to help, send me the base stats for each star I’m missing and I’ll add them to the next edition. In the meantime, there is a hidden tab for stats that you can add the numbers into yourself. Just stick to the Base stats. The sheet will figure out the modified totals automatically.
Q: Why can’t I select which is primary and which is secondary?
A: For purposes of this sheet, it doesn’t matter. The two skills are added together to generate a total.
Q: Should I just pick the top 2 from each category and fill in the blanks?
A: I mean, you can, and you’ll probably get a decent voyage out of it. In my experience, you’re probably better off using the sheet as a starting point and trying tweaking from there. Perhaps there’s crew you’d never considered using. Perhaps the sheet isn’t including your 5/5 Surak on that voyage (put him on that voyage!). Take a look at the whole, not just the preferred stats. It makes a difference.
Q: Why are my 1,2, and 3-star crew represented on this sheet?
A: My goal when creating this sheet was to provide a tool that anyone at any level can use. At this time there is no way to filter out that crew, however that is on my list of things to do. For now, if you don’t want to see the lower crew in your results, simply omit them from your roster.
Q: How can I show my level 80 fully fused ____?
A: Unfortunately, the amount of data required to do anything but fully equipped crew is too much for this sheet to handle. However you can use this sheet to get a sense how much impact a fully equipped crew member would have on your current roster. This can help you decide who to work on next.
Q: How can I transfer my roster from one version of the sheet to another?
A: Go to the Roster page and filter out the most recent version. Copy Column B from the old sheet and paste it into the new sheet. Then remove the filter. It should copy the data accurately.
Q: Hey, doesn't this look a lot like the Do Not Airlock Checklist?
A: It definitely does. When something works, it just works. Hopefully Leshy doesn't mind? However I'm open to changing the format as long as there's a good way to distinguish crew rarities and ownership. Suggestions are welcome.
Any questions I haven’t posted here, feel free to ask me below and I’ll do my best to answer them. I am by no means an expert on the game, but I’ve gotten a ton of help from other players and I wanted to find a way to give back to the community. Hopefully this helps everyone improve their voyages (and their roster)! I will do my best to keep this up-to-date on a fairly regular basis. I cannot promise a schedule at this time, but I'm not planning on abandoning the game any time soon either.
STT Voyage Planning Tool Beta 1.13 (12 November 2019):
https://docs.google.com/spreadsheets/d/1sd_81DEEnbrCM0ArZFh1AAzHghZLXRTonaqEu9IuCcw/edit?usp=sharing
8
Comments
EDIT: It's been moved. Thanks Shan!
Column B. Sorry, I'll clear that up in the next update.
I have actually previously tried to implement a Voyage crew selection tool in my own personal copy of the DNA checklist. I got something working very roughly, but it was nowhere near polished or bullet-proof enough to turn it into an actual feature - great to see that you've created something that actually works.
I have the same issue on the DNA Checklist. I therefore created a quick lookup sheet – when you enter the min and max stats, it looks through the stats of existing crew to find ones with an identical stat range, and returns what should be the stats for the intermediate fuse levels.
I have found that the values tend to be either correct, or at least very close to what they should be. If you do not want to have to wait for the wiki to be updated and want the crew to work correctly right away, you could use the same tool. You'll probably still need to check and confirm or update at some point, of course.
I have made a copy at https://docs.google.com/spreadsheets/d/1W6Y6CuNk1jnCyXRlYlSbvPgfZffhpCAeFptTfPb-XfE/edit?usp=sharing
It's not very polished – just enter the rarity of the crew you're looking up the stats for and what the stat is at 1/x and what it is at FF status. With so many crew in the game, it almost always returns a result.
Well, that's to be determined still, hence the public beta. :-P
Oh wow, very cool. Thank you for sharing. I'll give this a shot and hopefully it'll do the trick. So for the Min and Max, that's at 1/5 and 5/5 for a single skill, yes?
Just two suggestions. When you add new crew, add them to the bottom in chronological order. That way it does not mess up crew already set.
Second, split the tool into two separate spreadsheets.
Spreadsheet one has the list of crew, and raw data, second has the selectors to say whether you have the crew or not. The second spreadsheet use the importrange function to import the raw data. Then you only have to edit sheet one, and all copies of sheet two get updated automatically..
The stats in the list have not been updated for a while, so they do not include the most recent crew. I need to give it an update sometime soon, but so far it seems to work fairly well.
Thanks for the suggestions! As far as the first one goes, I typically do that in between versions when I'm adding new crew, but my thought process is that a player stepping into this sheet for the first time may not have been around for the revisions and if they're looking for a recent addition they might not know where it is.
As far as the importrange suggestion, I'll need to research that a bit. I'm in the process of swapping out the array functions as it seems to be causing bugs with sorting. I am a little confused with what you're suggestion though - this tool does have two spreadsheets and pulls the data from a hidden sheet with the raw numbers. Unless I'm confused?
I am far from an Excel/G-sheets expert and have a ton to learn, so I'm open to investigating all possibilities. Could you elaborate more? I'd love to learn.
Point one. The problem with keeping it all in alphabetical order is that when new crew are added, it throws the whole thing out of whack. People have to start at the beginning and re-enter the crew they have. This is tedious when crew can run into the hundreds.
New crew can be found with the search function ctrl-F on windows.
point two.
go to the stats tab. copy the tab to a new spreadsheet, i.e. a different file. You only need up to column as. This is the one you edit when you add new crew, or add stats.
Then on the current stats tab, you use import range to load in the data from the other file, with the file URL. When you update the stats file, others will be able to use it without needing to make a new copy of your spreadsheet.
Sorting, there is a bug in google sheet about sorting where to cells have the same value. This has been bypassed on some spreadsheet by adding a nominal value to data, e.g. the row number/1000000
I don't know if this is your bug.
I'll use a recent ENG/SEC Voyage as an example. Currently the sheet shows the top 10 for each triple skill. E/S/MED is the most exotic combo and lists the following:
Changeling Bashir (4/4)
Doctor LaForge (4/4)
After that it lists crew in order of highest combined stats:
Cornwell (5/5)
Caretaker (5/5)
DaVinci (5/5)
However, I don't think this is the ideal way of displaying things. After goofing around with Chewie's Voyage time estimator I compared Bashir, Cornwell, Caretaker and 5of12 (1/5). I included 5of12 since at 1/5 his SEC/ENG is roughly the same as Bashir's.
Caretaker has a roughly 10-15 minute advantage over Bashir since he has a higher net ENG/SEC and a huge amount of tertiary voyage stats.
Cornwell has about 3-4 minutes due to the large tertiary skills but lower ENG/SEC.
Five has roughly two minutes due to a modest edge in tertiaries.
So I think the best of displaying things is by the total of primary and secondary even if there's only one skill present. A separate column shows the total amount of Tertiary skills. Dividing the tertiaries by 3 should give the user a rough idea of how they compare to the primary and secondary.
You may want to factor in traits in some fashion as well. 25 AM is worth about 100 of a primary or secondary skill.
For this Voyage I ended up using a combination of crew with strong ENG/SEC (ex BoxingTucker), strong SEC (ex Kortar) or strong ENG (ex Caretaker).
Players can filter by version number using the filter in column G. If they remove the latest version, they can copy/paste their player data from a previous sheet, then add the filter back in. I have yet to encounter an issue with this - if players do I want to know about it - but until I find there is an issue with this method I will continue to sort crew alphabetically.
I understand what you're suggesting now, and I agree this could be a good idea. I will need to look into this further to see if it works appropriately for this sheet - especially with the changes I'm making for the next version.
With a rare set of skills I agree 100% that this isn't the best way to present data. With common sets this method becomes problematic. If data is sorted by the total of primary/secondary only the sheet will pull data for crew that doesn't have the appropriate tertiary skills - especially if we get into common sets. If a low-level player runs CMD + DIP the sheet will be dominated by certain sets of skills and will fail to hit the uncommon ones.
I ran a version of the sheet that did this during my private beta and found that the results were swinging too far in the opposite direction. Skill checks for CMD + DIP + MED were dominated by CMD + DIP + SEC and I wouldn't get any results at all that featured the tertiary skill.
The good news is the sheet does scale based on your crew, so if you have 2 that have the triad, it'll fill in the rest with the pairs only. It's not locked in to 10.
If you're handy with Excel and can mock up an example of what you're suggesting I'd love to take a look at it (even if it doesn't contain any formulas - we can work that out).
Traits are definitely on my list of long-term goals (somehow I failed to include them in the list). I have no idea how to implement them yet, but it's on my list!
Whoops. I'll correct that for the next version. Thank you!
Evolved Janeway is missing from the list and will be added in the next version. Sorry about that. I wouldn't be surprised if there is more crew missing. Please let me know if you find any others.
Another user has also reported incorrect stats for 1/5 Mirror T'Pol. I have not had a chance to look into that yet but it will be fixed in the next version as well. The stats have been verified correct against the wiki. I will work with them to see if there is another bug causing a problem.
Thank you all for the feedback so far. Please keep it coming!
Thanks to infiniteboat for reporting it!
Oh, is that all?!
I hope to have a new update out before the next event!
Edit: Never mind... I found it. Thank you, great tool!
Yup, it's just hidden. If users prefer it unhidden I can definitely change that for the next release. I just felt that most users probably wouldn't be utilizing it.
Let me know what you prefer (if anything) and I'll make the change if there's enough of an opinion either way.
She is SCI, not MED
I also downloaded spreadsheet as Excel but it does not work. Some formulas are not transfered in Excel form.
I went back and stopped using the Excel spreadsheet as the formulas don't translate from Google to Excel. However, that aside, it is still an impressive tool, and gives a lot more insight into how one approaches Voyages.
I have an upcoming update that changes a number of the formulas. I haven't tested it in Excel but perhaps it will work better for you?
I should hopefully have it out soon. Apologies for the delay. Real life has been hectic since the initial release.
This version mainly addresses bugs found in the previous version, and updates the crew. Nearly all crew that was missing information in the previous version has had their missing stats updated in the Wiki, and as such has been updated in the sheet. For the brand new crew, I have used the tool @Leshy has graciously shared to estimate the values. YMMV when using crew beyond verified stats. If you find a discrepancy, please let me know so I can update it.
For this release I focused on improving sorting on the Roster page. Unfortunately, this process required changing the formulas from Arrays (which require one formula at the top of a column and perform calculations against the entire column in one shot) to individual formulas for each cell. This causes performance issues on the sheet. You may experience slowdown that you hadn't previously experienced. Please let me know how performance goes and if there's anything that breaks/crashes the sheet - I've already had an issue with sorting the roster crashing my Chromebook, but my Chromebook is older and, well, it's a Chromebook.
There have been no major changes to the voyage tabs.
Due to the formula change I do recommend entering your roster in manually rather than copying/pasting from the previous version. Going forward you should be able to filter back to your last used version and copy/paste the roster.
Thank you again for downloading and giving this a try. Don't hesitate to contact me with any questions, concerns or bug reports. You can reach me here, on Discord (PsionStorm#4843), or on Reddit (/u/PsionStorm).
Enjoy!