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.
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 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.)
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...
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
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.
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)
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!
Ok, lets try this. Version 3.51. Thanks @Leshy. Updated with new crew. Let me know if you see any issues. This is our first attempt and filling some big shoes.
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!
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!
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).
@cmdrworf visually that looks good, nice work 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!
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?
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
@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!
@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!
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)
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.
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.
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?
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.
Comments
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.
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.
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:
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.)
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...
Protecting the Galaxy's Future from itself
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.
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)
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....
https://docs.google.com/spreadsheets/d/1PZHGRtVOsvf-WCgGe7YtVWT2Sucue2aHknlXKVWwQ3Y/edit?usp=sharing
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.
Protecting the Galaxy's Future from itself
Protecting the Galaxy's Future from itself
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).
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!
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?
I still think that there is a mathematical relationship for the missing data. I just stink at algebra.
Protecting the Galaxy's Future from itself
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.
Thanks Ward, but I was after the Tier not the level.... though that is nice too
I’m not sure if @Leshy used this or not, but it might be helpful:
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)
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.
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.
https://docs.google.com/spreadsheets/d/1RThzXE_5X9VHFO0DGkfsGDguwpDeJU-ZKPZ_QxUce3A/edit?usp=sharing
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.