Community Tool: STT Voyage Planning Tool

PsionStormPsionStorm ✭✭
edited May 22 in Ready Room
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.

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.06 (22 May 2019):
https://docs.google.com/spreadsheets/d/1PNcGeoYS7k7e2Yb5SbHnkXREZNYrfpwOy5BAPmLr9Vw/edit?usp=sharing
«1

Comments

  • PsionStormPsionStorm ✭✭
    edited May 1
    I may have posted this in The Bridge in error. Could've sworn it was Ready Room. Whoops! :(

    EDIT: It's been moved. Thanks Shan!
  • ByloBandByloBand ✭✭✭✭✭
    I'll check this out when I wake up.
    Rather than take up real estate on The Bridge with my random game thoughts, I've started doing log entries in the Starport recruitment thread in Fleet Central.
  • DavideBooksDavideBooks ✭✭✭✭✭
    Awesome
  • AviTrekAviTrek ✭✭✭✭✭
    Awesome. Thanks.
  • PsionStormPsionStorm ✭✭
    FYI: There appears to be a bug on the Roster page. If you sort by skill it breaks the sheet. In the meantime stick to filtering. I'll correct it for the next release.
  • Commander SinclairCommander Sinclair ✭✭✭✭✭
    I can't wait to see this project finished. I have always wondered what the theoretical max Voyage (time) would be, and this would be a great tool to figure that out! I have mapped out many Voyages and kept stats, but this is definitely superior to the info I was tracking. Well done!
    "Deep in the human unconscious is a pervasive need for a logical universe that
    makes sense. But the real universe is always one step beyond logic."

    #STOPMACROS
  • I'm not seeing what column you enter the star level number on the Roster page. Can you clarify?
  • PsionStormPsionStorm ✭✭
    I'm not seeing what column you enter the star level number on the Roster page. Can you clarify?

    Column B. Sorry, I'll clear that up in the next update.
  • LeshyLeshy ✭✭✭
    edited May 1
    PsionStorm wrote: »
    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? :)
    Absolutely not, great work! :)

    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.
    PsionStorm wrote: »
    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.
    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.
  • PsionStormPsionStorm ✭✭
    Leshy wrote: »
    great to see that you've created something that actually works.

    Well, that's to be determined still, hence the public beta. :-P
    Leshy wrote: »
    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.

    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?
  • Mirror CartmanMirror Cartman ✭✭✭✭
    Very nice piece of work. I was trying something like that a year or so ago for voyages, and gave up on the crew data entry part. I only completed the voyage length calculator, so I might steal your work.

    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..
  • LeshyLeshy ✭✭✭
    edited May 1
    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?
    It is indeed. 1/4 and 4/4 if you indicate a 4-star crew; likewise for a 3-star one.

    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.

  • PsionStormPsionStorm ✭✭
    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..

    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. :smiley:
  • Mirror CartmanMirror Cartman ✭✭✭✭
    PsionStorm wrote: »
    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..

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

    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.
  • IvanstoneIvanstone ✭✭✭✭✭
    edited May 2
    Ok, I started playing around with this and it does a pretty good job of keeping things nice and neat. I do have an issue with regards to forming a Voyage. I think it over emphasizes crew that have both Primary and Secondary skills.

    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).
    VIP 9 - 165 Crew Slots - 412 Immortals
  • ByloBandByloBand ✭✭✭✭✭
    I am still inputting my "Data", but so far it looks very cool!
    Rather than take up real estate on The Bridge with my random game thoughts, I've started doing log entries in the Starport recruitment thread in Fleet Central.
  • Super awesome. One error I noticed is Doctor Ann Muhal has sci instead of med
  • ByloBandByloBand ✭✭✭✭✭
    While inputting my crew I cannot seem to locate Evolved Janeway on the list of crew.
    Rather than take up real estate on The Bridge with my random game thoughts, I've started doing log entries in the Starport recruitment thread in Fleet Central.
  • PsionStormPsionStorm ✭✭
    edited May 2
    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.

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

    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.
    Ivanstone wrote: »
    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.

    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).
    Ivanstone wrote: »
    You may want to factor in traits in some fashion as well. 25 AM is worth about 100 of a primary or secondary skill.

    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!
    Trajan wrote: »
    Super awesome. One error I noticed is Doctor Ann Muhal has sci instead of med

    Whoops. I'll correct that for the next version. Thank you!
    ByloBand wrote: »
    While inputting my crew I cannot seem to locate Evolved Janeway on the list of crew.

    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!
  • PsionStormPsionStorm ✭✭
    edited May 2
    Bug update: There was an issue with DIP+SCI showing incorrect results for DIP+SCI+MED. I've corrected it in the existing version. If you'd like to just update your sheet, copy and paste the following code into cell B44 under the Voyages tab:
    =IFERROR(IF(I1="CMD+DIP",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AM WHERE AM>0 AND AQ>0 AND AU>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AM WHERE AQ>0 OR AU>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+ENG",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AN WHERE AN>0 AND AQ>0 AND AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AN WHERE AQ>0 OR AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AO WHERE AO>0 AND AQ>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AO WHERE AQ>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AP WHERE AP>0 AND AQ>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AP WHERE AQ>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AQ WHERE AQ>0 AND AP>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AQ WHERE AP>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+ENG",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AR WHERE AR>0 AND AU>0 AND AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AR WHERE AU>0 OR AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AS WHERE AS>0 AND AU>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AS WHERE AU>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AT WHERE AT>0 AND AU>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AT WHERE AU>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AU WHERE AU>0 AND AS>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AU WHERE AS>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AV WHERE AV>0 AND AX>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AV WHERE AX>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AW WHERE AW>0 AND AX>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AW WHERE AX>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AX WHERE AX>0 AND AW>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AX WHERE AW>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SEC+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AY WHERE AY>0 AND AZ>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AY WHERE AZ>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SEC+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AZ WHERE AZ>0 AND AY>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AZ WHERE AY>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SCI+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,BA WHERE BA>0 AND AY>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,BA WHERE AY>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,))))))))))))))),"No Matching Crew")

    Thanks to infiniteboat for reporting it!
  • ByloBandByloBand ✭✭✭✭✭
    PsionStorm wrote: »
    Bug update: There was an issue with DIP+SCI showing incorrect results for DIP+SCI+MED. I've corrected it in the existing version. If you'd like to just update your sheet, copy and paste the following code into cell B44 under the Voyages tab:
    =IFERROR(IF(I1="CMD+DIP",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AM WHERE AM>0 AND AQ>0 AND AU>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AM WHERE AQ>0 OR AU>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+ENG",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AN WHERE AN>0 AND AQ>0 AND AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AN WHERE AQ>0 OR AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AO WHERE AO>0 AND AQ>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AO WHERE AQ>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AP WHERE AP>0 AND AQ>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AP WHERE AQ>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="CMD+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AQ WHERE AQ>0 AND AP>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AQ WHERE AP>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+ENG",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AR WHERE AR>0 AND AU>0 AND AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AR WHERE AU>0 OR AX>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AS WHERE AS>0 AND AU>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AS WHERE AU>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AT WHERE AT>0 AND AU>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AT WHERE AU>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="DIP+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AU WHERE AU>0 AND AS>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AU WHERE AS>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+SEC",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AV WHERE AV>0 AND AX>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AV WHERE AX>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AW WHERE AW>0 AND AX>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AW WHERE AX>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="ENG+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AX WHERE AX>0 AND AW>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AX WHERE AW>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SEC+SCI",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AY WHERE AY>0 AND AZ>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AY WHERE AZ>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SEC+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AZ WHERE AZ>0 AND AY>0 AND BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,AZ WHERE AY>0 OR BA>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,
    IF(I1="SCI+MED",
    ARRAY_CONSTRAIN(UNIQUE(QUERY({
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,BA WHERE BA>0 AND AY>0 AND AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    ;
    IFERROR(QUERY(Roster!A3:BA,"SELECT A,B,C,D,E,AL,AF,AG,AH,AI,AJ,AK,BA WHERE AY>0 OR AZ>0 ORDER BY AL DESC LIMIT 12",0),
    {0,0,0,0,0,0,0,0,0,0,0,0,0})
    },"SELECT * where Col1<>'0'")),12,14)
    ,))))))))))))))),"No Matching Crew")

    Thanks to infiniteboat for reporting it!

    Oh, is that all?! :)
    Rather than take up real estate on The Bridge with my random game thoughts, I've started doing log entries in the Starport recruitment thread in Fleet Central.
  • Commander SinclairCommander Sinclair ✭✭✭✭✭
    Yeah, that didn't work. I suspect you have to have a certain version of Excel for that? I will wait for the next update. :)
    "Deep in the human unconscious is a pervasive need for a logical universe that
    makes sense. But the real universe is always one step beyond logic."

    #STOPMACROS
  • PsionStormPsionStorm ✭✭
    Yeah, that didn't work. I suspect you have to have a certain version of Excel for that? I will wait for the next update. :)

    I hope to have a new update out before the next event!
  • edited May 6
    Hi, I just tried downloading the spreadsheet to Excel to begin working on my crew. However, I noticed that the "Stats" tab is missing on spreadsheet. How do we get this information into the spreadsheet? Is the data stored somewhere else? Thanks.

    Edit: Never mind... I found it. Thank you, great tool!
  • PsionStormPsionStorm ✭✭
    Hi, I just tried downloading the spreadsheet to Excel to begin working on my crew. However, I noticed that the "Stats" tab is missing on spreadsheet. How do we get this information into the spreadsheet? Is the data stored somewhere else? Thanks.

    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.
  • Banjo1012Banjo1012 ✭✭✭✭✭
    Trajan wrote: »
    Super awesome. One error I noticed is Doctor Ann Muhal has sci instead of med

    She is SCI, not MED
  • HaBlackHaBlack ✭✭✭
    Hi, I just tried downloading the spreadsheet to Excel to begin working on my crew. However, I noticed that the "Stats" tab is missing on spreadsheet. How do we get this information into the spreadsheet? Is the data stored somewhere else? Thanks.

    Edit: Never mind... I found it. Thank you, great tool!

    I also downloaded spreadsheet as Excel but it does not work. Some formulas are not transfered in Excel form. :( Too bad I really looked forward to use this...
    PlayingSince: 2016-09-16
    Can we get some more characters from TAS?
    Character wishlist:
    • Lieutenant M'Ress
    • Amanda Rogers
    • Sarah MacDougal
    • Mirasta Yale
    • Agmar
  • HaBlack wrote: »
    Hi, I just tried downloading the spreadsheet to Excel to begin working on my crew. However, I noticed that the "Stats" tab is missing on spreadsheet. How do we get this information into the spreadsheet? Is the data stored somewhere else? Thanks.

    Edit: Never mind... I found it. Thank you, great tool!

    I also downloaded spreadsheet as Excel but it does not work. Some formulas are not transfered in Excel form. :( Too bad I really looked forward to use this...

    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.
  • HaBlack wrote: »
    I also downloaded spreadsheet as Excel but it does not work. Some formulas are not transfered in Excel form. :( Too bad I really looked forward to use this...

    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.
  • Hey folks, Public Beta 1.05 is now available here. Please make a copy of the sheet before attempting to add in your own roster.

    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!
Sign In or Register to comment.