Home Ready Room

Do Not Airlock Checklist Thread 3: Press Ctrl-F, type Spock

12930313335

Comments

  • Automaton_2000Automaton_2000 ✭✭✭✭✭
    Can't brute force burnout. Enjoy riding off into the sunset, my dude. Thank you for serving the community.
  • Thanks for all your work and best of luck @Leshy !
  • LeshyLeshy ✭✭✭
    USS Dart wrote: »
    I'm thinking that the downside to the datacore input is that the sheet thinks that everyone is FE.
    Yes, the sheet does assume that by default. Perhaps the import could be modified to not include the crew not actually FE'd.
    Also, I know I've taken us around this block before, but I do wish there was some way of the sheet telling me that the new 1/4 I just got will never be good enough, even at FFFE, to replace my current crew, so I can safely push them out the airlock.
    As previously said, the really best solution for that is to not mark the crew as 1/4, but to leave it empty and use the Notes column to indicate that you have that crew partially fused. You will then get the recommendations for that crew at FF status and will be able to fairly easily see which partially-fused crew are worth levelling up further.

    It's a tricky one to do, as the sheet would essentially have to do all calculations twice – once to see what a crew can do for you at FF status, and once to see what they can do for you at their current status. It'd make the sheet even heavier than it currently is.
  • Forum Newbie - but longtime user/lover of the DNA spreadsheet here.

    Mad props @Leshy for helping to pick up where the game left off and make it so much more fun! DB should give you a commission for all the players you kept in the game so much longer with your amazing work! Best of luck to you in your next endeavors. LLAP.
  • dext74dext74 ✭✭✭✭✭
    edited February 2020
    Warning... complicated Google sheet post ahead.

    I'm working on making some modifications to my sheet and I wanted to get some input from anyone smarter than me on sheets to see if there's a more efficient way to write this than the nested if's that I've come up with & help solve a problem I've run into.

    Basically, for my own keeping track of, I use the notes column to display the level or equipped status of a crew - so it shows level or FE or FF/FE. (I know the sheet doesn't change anything based on crew level, but I thought if I'm going to use input data to update my acquisition and stars, I might as well find a way to update the crew levels automatically too).

    So I ended up with the following statement:
    =IF((vlookup($A248,ImportData!$A$2:$F,6,false))<100,"WHAT-DO-I-PUT-HERE-TO-PRINT-LEVEL-VALUE",IF((vlookup($A248,ImportData!$A$2:$H,8,false))=TRUE,"FF/FE",IF((vlookup($A248,ImportData!$A$2:$G,7,false))=TRUE,"FE","100")))
    

    So it looks up the crew and if the level amount is less than 100, then it prints the level (but this is the snag; see below). If the level IS 100, then it checks to see if it's immortalized and prints FF/FE. If it's not immortalized, it checks the FE column and if that's true then it prints FE. Otherwise it just prints 100.

    I got the vlookups to work to find the crew info. My problem is for crew that aren't at level 100 -- how do I get it to print the level amount in column F of the ImportData sheet? I can't directly link it to that cell because the order of the ImportData sheet will change. So somehow I need to tie this to another vlookup function I think?

    (I'm proud of myself for getting this far - just have this last hurdle before it should be able to display on the sheet correctly.)
  • ~peregrine~~peregrine~ ✭✭✭✭✭
    @Leshy , thank you for being a pillar of support in our STT community. I wish you all the best in your future endeavors. Live long and prosper. 🖖
    "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
  • Ward SerpentineWard Serpentine ✭✭✭
    edited February 2020
    dext74 wrote: »
    Warning... complicated Google sheet post ahead.

    The best way to tackle this, IMO, would be to follow the "IFS" formula in the Recommendation column.
    I s u c k at spreadsheet functions, but I believe that you want to use "IFS" instead of multiple IF

    Quotes added because the censor thinks I'm swearing... :smiley:
    The Guardians of Tomorrow
    Protecting the Galaxy's Future from itself
  • Leshy wrote: »
    As previously said, the really best solution for that is to not mark the crew as 1/4, but to leave it empty and use the Notes column to indicate that you have that crew partially fused. You will then get the recommendations for that crew at FF status and will be able to fairly easily see which partially-fused crew are worth levelling up further.

    Thats what works for me - I note the voyage count in the notes column already, as V###, and have not fully fused unleveled purples marked with their Tier and the fuse level, as T## F#. When I sort the notes column Z-A I have the top voyagers on top with the partially fused purples below that.

  • LeshyLeshy ✭✭✭
    edited February 2020
    dext74 wrote: »
    So I ended up with the following statement:
    =IF((vlookup($A248,ImportData!$**tsk tsk**2:$F,6,false))<100,"WHAT-DO-I-PUT-HERE-TO-PRINT-LEVEL-VALUE",IF((vlookup($A248,ImportData!$**tsk tsk**2:$H,8,false))=TRUE,"FF/FE",IF((vlookup($A248,ImportData!$**tsk tsk**2:$G,7,false))=TRUE,"FE","100")))
    
    You're already practically there – the thing that you want to print (the crew's level) is actually the thing that you just looked up to check whether it was lower than 100. So you can just copy-paste the very first vlookup you did to print the level.

    Note:
    The current formula will return a lot of 0's for the crew that you do not have. To prevent this, you would have to add another IF statement to check if the level value is 0 (before checking if it is < 100).

    As Ward Serpentine suggested above, using IFS will be easier than nesting IF statements. The formula above should then become (ignore spaces to avoid the word censor)
    =ifs(vlookup($A248,ImportData!$A $ 2:$F,6,false)=0,"",vlookup($A248,ImportData!$A $ 2:$F,6,false)<100,vlookup($A248,ImportData!$A $ 2:$F,6,false),vlookup($A248,ImportData!$A $ 2:$H,8,false)=TRUE,"FF/FE",vlookup($A248,ImportData!$A $ 2:$G,7,false)=TRUE,"FE")
    
  • Thanks Leshy for the point in the right direction.. Any other nuts out there pasting in the wrong tab, be sure its the Import tab and NOT the ImportData tab! DOH..

    Next - I'd like to reference into the note column the Tier for each - I see that data - anyone work out a formula to snag it? I had actually manually added some tier info, but if it can grab it from the data....
  • @Leshy thank you so much for doing this sheet for so long! I totally get the whole burnout issue! Good luck in your future endeavours :) Long life and prosperity to you!
    Now on Star Trek Timelines as Kathryn Crow's Eye.
  • Ward SerpentineWard Serpentine ✭✭✭
    edited February 2020
    DeeMentia wrote: »
    Thanks Leshy for the point in the right direction.. Any other nuts out there pasting in the wrong tab, be sure its the Import tab and NOT the ImportData tab! DOH..

    Next - I'd like to reference into the note column the Tier for each - I see that data - anyone work out a formula to snag it? I had actually manually added some tier info, but if it can grab it from the data....

    I put the level in the notes column, but you could just adjust the formula...

    =iferror(vlookup($A10,ImportData!$ A $3:$F,6,false))

    Take out the spaces around the A as again the f-ing profanity filter is hosing it.
    The Guardians of Tomorrow
    Protecting the Galaxy's Future from itself
  • @Leshy Could you please share how you calculate your estimated skill score for those that are missing from stt.wiki? I know there is a relationship that should be easy to calculate, but I'm missing it. Thanks!

    th64pcb2azxu.png
    The Guardians of Tomorrow
    Protecting the Galaxy's Future from itself
  • SpukkZSpukkZ ✭✭✭
    @Leshy Could you please share how you calculate your estimated skill score for those that are missing from stt.wiki? I know there is a relationship that should be easy to calculate, but I'm missing it. Thanks!

    th64pcb2azxu.png

    There is no real formula to calculate those inbetween values. What Leshy did is looking at crew with the same/similar min/max values and usually the intermediate numbers will be identical, or very close (within a couple of points difference).
  • @Leshy and @cmdrworf , thank you so much. The import function is massively faster than trying to remember all the astrophysicists I just unfroze. :smile:

    jtzwmeipkvoi.jpg
  • @cmdrworf visually that looks good, nice work :smile: I'm trying it now to see if it matches up.

    Also, following a post from @Leshy earlier in the thread on how to add new crew, I tried it myself and it worked out great. Nice clear instructions, lovely! :smile:

    So the next question is how does one keep on top of the new crew that crop up? Its easy enough to follow the posts on new events that are due, but then there's the packs that randomly crop up with the new crew in them. These are a bit more subtle. But is there a place on the wiki that indicates the newest crew that are introduced? Or are we looking elsewhere?
  • @PhilJS89 Between the new crew announcements and the event descriptions in Starfleet Communications, you should be able to glean the new crew stats.

    I still think that there is a mathematical relationship for the missing data. I just stink at algebra.
    The Guardians of Tomorrow
    Protecting the Galaxy's Future from itself
  • cmdrworfcmdrworf ✭✭✭✭✭
    @PhilJS89 Between the new crew announcements and the event descriptions in Starfleet Communications, you should be able to glean the new crew stats.

    I still think that there is a mathematical relationship for the missing data. I just stink at algebra.

    I've not been able to figure it out. if I can, I'll update the sheet to auto do the 2/3/4* stats.
    Sir, I protest! I am NOT a merry man!
  • cmdrworfcmdrworf ✭✭✭✭✭
    One stealth update. I had the 4* crew listed with 5* stats. fixed now.
    Sir, I protest! I am NOT a merry man!
  • DeeMentia wrote: »
    Thanks Leshy for the point in the right direction.. Any other nuts out there pasting in the wrong tab, be sure its the Import tab and NOT the ImportData tab! DOH..

    Next - I'd like to reference into the note column the Tier for each - I see that data - anyone work out a formula to snag it? I had actually manually added some tier info, but if it can grab it from the data....

    I put the level in the notes column, but you could just adjust the formula...

    =iferror(vlookup($A10,ImportData!$ A $3:$F,6,false))

    Take out the spaces around the A as again the f-ing profanity filter is hosing it.

    Thanks Ward, but I was after the Tier not the level.... though that is nice too
  • ~peregrine~~peregrine~ ✭✭✭✭✭
    @Leshy Could you please share how you calculate your estimated skill score for those that are missing from stt.wiki? I know there is a relationship that should be easy to calculate, but I'm missing it. Thanks!

    th64pcb2azxu.png
    @PhilJS89 Between the new crew announcements and the event descriptions in Starfleet Communications, you should be able to glean the new crew stats.

    I still think that there is a mathematical relationship for the missing data. I just stink at algebra.

    I’m not sure if @Leshy used this or not, but it might be helpful:

    ysi0mqfbxdzx.jpeg
    https://stt.wiki/wiki/Crew_Progression#Crew_progression_fusion_rule_of_thumb
    🖖
    "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
  • cmdrworfcmdrworf ✭✭✭✭✭
    edited February 2020
    @Leshy Could you please share how you calculate your estimated skill score for those that are missing from stt.wiki? I know there is a relationship that should be easy to calculate, but I'm missing it. Thanks!

    th64pcb2azxu.png
    @PhilJS89 Between the new crew announcements and the event descriptions in Starfleet Communications, you should be able to glean the new crew stats.

    I still think that there is a mathematical relationship for the missing data. I just stink at algebra.

    I’m not sure if @Leshy used this or not, but it might be helpful:

    ysi0mqfbxdzx.jpeg
    https://stt.wiki/wiki/Crew_Progression#Crew_progression_fusion_rule_of_thumb
    🖖

    I've found the actual formula. working on calculating it out now and I'll post it soon. the above is not correct. (at least I've got it for 5*) (and I'm doing it at work so its on and off in between things)
    Sir, I protest! I am NOT a merry man!
  • cmdrworfcmdrworf ✭✭✭✭✭
    edited February 2020
    Here it is....
    nq0mir8dt9qd.png

    Basically, calculate the difference between the 5* and the 1*. Then using this table subtract the value shown from the 5* to get the 2/3/4* based on the difference determined. I'm adding this into the spreadsheet so the next version should have all of the 5* working correctly. I'm working on the 4*/3* next.

    I have this working for every 5* crew right now. They all fit this based on one of the difference shown and are 100% matches.
    Sir, I protest! I am NOT a merry man!
  • AviTrekAviTrek ✭✭✭✭✭
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?
  • cmdrworfcmdrworf ✭✭✭✭✭
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?

    This applies to every 5* crew. take the 5* FFE value - 1* FE value. then using that value the above chart shows what to subtract to get the 2/3/4* FE values (this works for any skill they have but its by skill. The formula is long an messy and involves multiple sheets, etc. Im building it into the Do Not airlock sheet but i need to get the 3* and 4* working first. I've only got the 5* programmed so far and I've not published that.

    Sir, I protest! I am NOT a merry man!
  • Sven LundgrenSven Lundgren ✭✭✭✭✭
    How about adding an Event Crew counter?

    7aguewuw7ek2.png

  • cmdrworfcmdrworf ✭✭✭✭✭
    edited February 2020
    If you want to see the formulas.... 2*, 3*, 4* columns have the formulas. 2nd tab has the lookups for the calculations.

    https://docs.google.com/spreadsheets/d/1RThzXE_5X9VHFO0DGkfsGDguwpDeJU-ZKPZ_QxUce3A/edit?usp=sharing
    Sir, I protest! I am NOT a merry man!
  • AviTrekAviTrek ✭✭✭✭✭
    cmdrworf wrote: »
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?

    This applies to every 5* crew. take the 5* FFE value - 1* FE value. then using that value the above chart shows what to subtract to get the 2/3/4* FE values (this works for any skill they have but its by skill. The formula is long an messy and involves multiple sheets, etc. Im building it into the Do Not airlock sheet but i need to get the 3* and 4* working first. I've only got the 5* programmed so far and I've not published that.

    Can you give an example. I don't understand what you mean. Let's take Mirror Picard.

    5* CMD = 1042
    1* CMD = 652
    1042 - 652 = 390

    Are you saying 5*-1* of every 5* crew is one of those 4 values? What are the values under the 0 in your chart?
  • cmdrworfcmdrworf ✭✭✭✭✭
    edited February 2020
    AviTrek wrote: »
    cmdrworf wrote: »
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?
    AviTrek wrote: »
    What does this mean? How do you do that with values for a different crew? Don't you need to share the formula or sheet not just a screenshot?

    This applies to every 5* crew. take the 5* FFE value - 1* FE value. then using that value the above chart shows what to subtract to get the 2/3/4* FE values (this works for any skill they have but its by skill. The formula is long an messy and involves multiple sheets, etc. Im building it into the Do Not airlock sheet but i need to get the 3* and 4* working first. I've only got the 5* programmed so far and I've not published that.

    Can you give an example. I don't understand what you mean. Let's take Mirror Picard.

    5* CMD = 1042
    1* CMD = 652
    1042 - 652 = 390

    Are you saying 5*-1* of every 5* crew is one of those 4 values? What are the values under the 0 in your chart?

    So then the 2* is 1042 - 364 = 678
    3* is 1042 - 286 = 756
    Etc....

    There are 8 combinations above and it's all based on the difference between 1 and 5 star. Then the chart shows how much to subtract from the 5 star to get the other ratings. I'm having more issues with the 4*. Either the wiki has a lot of issues or it's not as easy with the 4* crew.
    Sir, I protest! I am NOT a merry man!
Sign In or Register to comment.