If you want to see the formulas.... 2*, 3*, 4* columns have the formulas. 2nd tab has the lookups for the calculations.
It took me beating my head against the spreadsheet for a minute to see what you did. Because the percentages are on a curve depending on the difference in value from 1* to 5*, once you have the known additive (in your table) it is easy to calculate 2*, 3* and 4* values. The percentages that @~peregrine~ posted were a good starting point.
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
If you want to see the formulas.... 2*, 3*, 4* columns have the formulas. 2nd tab has the lookups for the calculations.
It took me beating my head against the spreadsheet for a minute to see what you did. Because the percentages are on a curve depending on the difference in value from 1* to 5*, once you have the known additive (in your table) it is easy to calculate 2*, 3* and 4* values. The percentages that @~peregrine~ posted were a good starting point.
You are correct. This assumes fe only. And yeah the percentage was a good start. This locked it in. 4* is not a clear or the wiki is off. I've got a post in the bridge looking at a wave of values to verify
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)
EDIT 3: I attempted to fix my own snag - hoping @Leshy or @Ward Serpentine would be willing to help one more time. This code was great, except for characters who are level 100 but not FE. It was displaying as N/A - Error: No Match. So I attempted the following:
I've gone through all of the 4* in the stats tab and came up with a list. I'm thinking that there may be some bad values in the sheet, as there were 12 one offs and 3 that had two sets of stats. There were 9 that had common values. This is the chart I came up with.
@cmdrworf Doing good work sir! Keep it up! Might be worth creating your own thread as a fork so people can keep up to date on the top of the thread.
I'll do that soon and see if Shan can swap the stickies for us then. I'll get to the 4* and 5* data added to the sheet next week. Busy weekend and frankly I get most of this done at work. :-)
I've gone through all of the 4* in the stats tab and came up with a list. I'm thinking that there may be some bad values in the sheet, as there were 12 one offs and 3 that had two sets of stats. There were 9 that had common values. This is the chart I came up with.
@Leshy or anyone else who may know the answer: I decided to go back to manually updating the sheet, i.e., no datacore input. For me, the crew added in version 3.50 have the "active" and the "keep" cells greyed out. I cannot edit them. I'm wondering what I've done wrong ...
Update: I figured something out. I created a second 3.50 sheet to do some testing and what I found is that none of the "active" or "keep" cells can be manually edited. Clicking on the cell gives me:
=iferror(vlookup($A5,ImportData!$A$2:$J,10,false))
I'm thinking the addition of the datacore input may be the culprit.
For me, the crew added in version 3.50 have the "active" and the "keep" cells greyed out. I cannot edit them.
The changelog for 3.50b mentions this.
When you put a checkbox into an empty cell, it becomes a checkbox that can be toggled on and off. If you put a formula into that cell, the checkbox becomes a display of the formula result, and it can no longer be manually switched on or off.
If you want to edit the 'Active' column after importing your crew, you can select all cells in the 'Active' column, hit Ctrl-C to copy them, then Del to empty them, then use Insert -> Checkbox to put checkboxes back in the cells, then Ctrl-Shift-V to past only the values you copied earlier.
That will put checkboxes back into the column that you can freely switch on or off. Doing this should, to the best of my knowledge, not break anything except the DataCore import functionality of the sheet (but you should not need to do that twice anyway).
The 'Keep' column does not have any formulas in it and should be editable at any time.
This code was great, except for characters who are level 100 but not FE.
The formula you posted should work just fine; an IFS statement just keeps going through the conditions until it encounters one that is correct, then returns the matching value.
However, there is a missing ) in your second condition that might be messing up things if it also missing in your actual formulas in the sheet: it currently says vlookup($A204,ImportData!$A $ 2:$F,6,false<100,. There should be a ) between the false and <100.
With the ) in the right place, the following formula seems to work correctly for me:
However, there is a missing ) in your second condition that might be messing up things if it also missing in your actual formulas in the sheet: it currently says vlookup($A204,ImportData!$A $ 2:$F,6,false<100,. There should be a ) between the false and <100.
A little background .. For me, this is a game. I've got a busy work and home life, so having tools like this one are the key for me to continue to enjoy the game and not have it feel like more work. As a result, I would regularly use the spreadsheet to identify crew that I can safely freeze.
With the datacore input, that's become a bit more difficult. See below:
While I've added Dress Uniform Bashir and the latest 5* Bones, Doc Crusher has been a top med crew member for quite a bit of time. Same with Disguised Tuvok. It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE. Does anyone have any thoughts on how to work through this conundrum?
A little background .. For me, this is a game. I've got a busy work and home life, so having tools like this one are the key for me to continue to enjoy the game and not have it feel like more work. As a result, I would regularly use the spreadsheet to identify crew that I can safely freeze.
With the datacore input, that's become a bit more difficult. See below:
While I've added Dress Uniform Bashir and the latest 5* Bones, Doc Crusher has been a top med crew member for quite a bit of time. Same with Disguised Tuvok. It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE. Does anyone have any thoughts on how to work through this conundrum?
Tagging Leshy to validate, but based on what I can see, the sheet does not take into account level or FE status. It appears to just look at stars. Personally I still do the manual updates and only put in my FFE crew. I've not spent enough time looking at this part yet to know what I could change to help with that but I can add it to my list and see if I can figure it out.
FYI, this weeks release (after the Tuesday new crew) will have the auto calc for the 5* and 4* stats in it. I've got them working now with a few exceptions on the 4* that are either wiki or keying errors (or perhaps a stay oddity that DB has added).
@USS Dart The space/freeze recommendation is based on FE'd crew. I believe that Leshy added logic in the spreadsheet to account for stars a while back. You also have to consider what you plan to use the crew for. The new McCoy has the highest MED skill, but he is a single skiller, so he might not be as effective on Voyages as Doc Crusher (a three skiller). It all depends on where you want to use the crew. Doc Crusher is #489 on the top voyagers list whereas McCoy is #598
The Guardians of Tomorrow Protecting the Galaxy's Future from itself
It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE.
The sheet does indeed assume that any crew entered are level 100/FE for their star count, and indeed, the import functionality does also mark non-level 100/FE as Active.
There are a few possible ways of addressing this issue:
1. Manually deleting the entries for the non-100/FE crew after importing.
2. Change the import functionality to have the sheet not import any non-100/FE crew.
3. Amending the sheet to take into account the actual stats of the crew upon time of import.
The first solution has the drawback of still being fairly labour-intensive.
The second solution could work, but might be counter-intuitive for a lot of people ("why did it not import my level 85 2/5 Assimilated La Forge?").
The third solution would perhaps be neatest – the imported data does have the actual crew stats in, so it would be possible to take those into account instead of the sheet's preset stats at level 100 FE. Of course, there would be some discrepancy as you leveled your crew in-game for as long as you did not re-import your crew, although this should be relatively minor. This solution is the most work, however.
A little background .. For me, this is a game. I've got a busy work and home life, so having tools like this one are the key for me to continue to enjoy the game and not have it feel like more work. As a result, I would regularly use the spreadsheet to identify crew that I can safely freeze.
With the datacore input, that's become a bit more difficult. See below:
While I've added Dress Uniform Bashir and the latest 5* Bones, Doc Crusher has been a top med crew member for quite a bit of time. Same with Disguised Tuvok. It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE. Does anyone have any thoughts on how to work through this conundrum?
Tagging Leshy to validate, but based on what I can see, the sheet does not take into account level or FE status. It appears to just look at stars. Personally I still do the manual updates and only put in my FFE crew. I've not spent enough time looking at this part yet to know what I could change to help with that but I can add it to my list and see if I can figure it out.
How are you doing the manual updates? I couldn’t figure out how to do that.
@Leshy I really feel like a giant jerk/giant male appendage. Your work, along with the work of others, makes this game tolerable for me. Please understand that nothing I say is criticism of you or your work.
You can still manually update the sheet if you choose. You can just copy-paste from a previous version as before. Entering values into column B overrides the formulas that are in there for auto-filling. It's just column E ("Active") where you'll have to delete the checkboxes and add new ones back in to clear the underlying formulas that prevent the checkboxes from being toggled.
I am sure that upcoming versions will contain improvements to the automated functionality, however.
I really don't get why you'd want to rank crew based on current partially fused status instead of FE status when planning game decisions. Think long term. Sure if they're 4/4 then finish them up, but even then if it's orion slave trader he still really doesn't matter. Even for accomplishments dil finishing the crew you'd be better off completing a useful cadet than spending chrons finishing lousy crew if you are short on game resources. You just don't need weaker SR crew until you've got main crew variants heavily covered, and you won't know which SR crew are strong unless you're focused on who is strong FF/FE.
It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE.
The sheet does indeed assume that any crew entered are level 100/FE for their star count, and indeed, the import functionality does also mark non-level 100/FE as Active.
There are a few possible ways of addressing this issue:
1. Manually deleting the entries for the non-100/FE crew after importing.
2. Change the import functionality to have the sheet not import any non-100/FE crew.
3. Amending the sheet to take into account the actual stats of the crew upon time of import.
The first solution has the drawback of still being fairly labour-intensive.
The second solution could work, but might be counter-intuitive for a lot of people ("why did it not import my level 85 2/5 Assimilated La Forge?").
The third solution would perhaps be neatest – the imported data does have the actual crew stats in, so it would be possible to take those into account instead of the sheet's preset stats at level 100 FE. Of course, there would be some discrepancy as you leveled your crew in-game for as long as you did not re-import your crew, although this should be relatively minor. This solution is the most work, however.
@Leshy I've already done the third option on my personal sheet. I'm looking to add this into the release version of the checklist as soon as @cmdrworf gets back to me in our group message.
The sheet does indeed assume that any crew entered are level 100/FE for their star count, and indeed, the import functionality does also mark non-level 100/FE as Active.
There are a few possible ways of addressing this issue:
1. Manually deleting the entries for the non-100/FE crew after importing.
2. Change the import functionality to have the sheet not import any non-100/FE crew.
3. Amending the sheet to take into account the actual stats of the crew upon time of import.
I always used the 4th option before the auto-import. Marking the crew stars and not marking them as active. It works wonders for non FF crew because you can focus on FE the crew that brings the most returns. ( Voyagers, and gauntlet crew for exemple. finishing up a crew that's in 2 collections instead of one etc...)
The only issue is non FF that are not FE that appear as frozen instead of need but it's a minor inconvenience.
FYI, this weeks release (after the Tuesday new crew) will have the auto calc for the 5* and 4* stats in it. I've got them working now with a few exceptions on the 4* that are either wiki or keying errors (or perhaps a stay oddity that DB has added).
Hey @cmdrworf was the new Mirror Burnham one of those 4* exceptions? Just realising that on the most recent version of the sheet I'm using, she's frozen. But in my game she's FFFE and active
I really don't get why you'd want to rank crew based on current partially fused status instead of FE status when planning game decisions. Think long term. Sure if they're 4/4 then finish them up, but even then if it's orion slave trader he still really doesn't matter. Even for accomplishments dil finishing the crew you'd be better off completing a useful cadet than spending chrons finishing lousy crew if you are short on game resources. You just don't need weaker SR crew until you've got main crew variants heavily covered, and you won't know which SR crew are strong unless you're focused on who is strong FF/FE.
So, this is where my “giant male appendage” comment comes into play. I’m not a spreadsheet person and @Leshy’s work really does appear to be magic to me. That said, for dullards like me, I think “space/freeze” should mean that even at FFFE, this character does not improve your crew. That way, if I get that message at 1/4 or 2/4, out the airlock they go.
FYI, this weeks release (after the Tuesday new crew) will have the auto calc for the 5* and 4* stats in it. I've got them working now with a few exceptions on the 4* that are either wiki or keying errors (or perhaps a stay oddity that DB has added).
Hey @cmdrworf was the new Mirror Burnham one of those 4* exceptions? Just realising that on the most recent version of the sheet I'm using, she's frozen. But in my game she's FFFE and active
@PhilJS89 Stealth fixed her yesterday in the published version. Icheb was also wrong.
I really don't get why you'd want to rank crew based on current partially fused status instead of FE status when planning game decisions. Think long term. Sure if they're 4/4 then finish them up, but even then if it's orion slave trader he still really doesn't matter. Even for accomplishments dil finishing the crew you'd be better off completing a useful cadet than spending chrons finishing lousy crew if you are short on game resources. You just don't need weaker SR crew until you've got main crew variants heavily covered, and you won't know which SR crew are strong unless you're focused on who is strong FF/FE.
So, this is where my “giant male appendage” comment comes into play. I’m not a spreadsheet person and @Leshy’s work really does appear to be magic to me. That said, for dullards like me, I think “space/freeze” should mean that even at FFFE, this character does not improve your crew. That way, if I get that message at 1/4 or 2/4, out the airlock they go.
@Synthetic Commander@USS Dart We're working on a fix to this..... We'll post the details on our approach/proposal once we have it done.
Comments
It took me beating my head against the spreadsheet for a minute to see what you did. Because the percentages are on a curve depending on the difference in value from 1* to 5*, once you have the known additive (in your table) it is easy to calculate 2*, 3* and 4* values. The percentages that @~peregrine~ posted were a good starting point.
Protecting the Galaxy's Future from itself
You are correct. This assumes fe only. And yeah the percentage was a good start. This locked it in. 4* is not a clear or the wiki is off. I've got a post in the bridge looking at a wave of values to verify
EDIT 3: I attempted to fix my own snag - hoping @Leshy or @Ward Serpentine would be willing to help one more time. This code was great, except for characters who are level 100 but not FE. It was displaying as N/A - Error: No Match. So I attempted the following:
Except now the "100" overwrites printing FE/FF and FE. How do I make it state that if none of those other conditions are true, then do THIS?
https://docs.google.com/spreadsheets/d/1U2azlkkUonzwivf7f3t6yKxglHNYl9WLBAxcAANVLdc/edit?usp=sharing
Protecting the Galaxy's Future from itself
I'll do that soon and see if Shan can swap the stickies for us then. I'll get to the 4* and 5* data added to the sheet next week. Busy weekend and frankly I get most of this done at work. :-)
Thanks @Ward Serpentine That will save me a bit of time.
Update: I figured something out. I created a second 3.50 sheet to do some testing and what I found is that none of the "active" or "keep" cells can be manually edited. Clicking on the cell gives me:
=iferror(vlookup($A5,ImportData!$A$2:$J,10,false))
I'm thinking the addition of the datacore input may be the culprit.
When you put a checkbox into an empty cell, it becomes a checkbox that can be toggled on and off. If you put a formula into that cell, the checkbox becomes a display of the formula result, and it can no longer be manually switched on or off.
If you want to edit the 'Active' column after importing your crew, you can select all cells in the 'Active' column, hit Ctrl-C to copy them, then Del to empty them, then use Insert -> Checkbox to put checkboxes back in the cells, then Ctrl-Shift-V to past only the values you copied earlier.
That will put checkboxes back into the column that you can freely switch on or off. Doing this should, to the best of my knowledge, not break anything except the DataCore import functionality of the sheet (but you should not need to do that twice anyway).
The 'Keep' column does not have any formulas in it and should be editable at any time.
The formula you posted should work just fine; an IFS statement just keeps going through the conditions until it encounters one that is correct, then returns the matching value.
However, there is a missing ) in your second condition that might be messing up things if it also missing in your actual formulas in the sheet: it currently says vlookup($A204,ImportData!$A $ 2:$F,6,false<100,. There should be a ) between the false and <100.
With the ) in the right place, the following formula seems to work correctly for me:
As always, thanks for your genius!
With the datacore input, that's become a bit more difficult. See below:
While I've added Dress Uniform Bashir and the latest 5* Bones, Doc Crusher has been a top med crew member for quite a bit of time. Same with Disguised Tuvok. It's not clear to me whether I can safely freeze them because I don't know whether the "space/freeze" recommendation is occurring based upon a non-FE crew that the sheet thinks is FE. Does anyone have any thoughts on how to work through this conundrum?
@Leshy
Tagging Leshy to validate, but based on what I can see, the sheet does not take into account level or FE status. It appears to just look at stars. Personally I still do the manual updates and only put in my FFE crew. I've not spent enough time looking at this part yet to know what I could change to help with that but I can add it to my list and see if I can figure it out.
Protecting the Galaxy's Future from itself
Protecting the Galaxy's Future from itself
There are a few possible ways of addressing this issue:
1. Manually deleting the entries for the non-100/FE crew after importing.
2. Change the import functionality to have the sheet not import any non-100/FE crew.
3. Amending the sheet to take into account the actual stats of the crew upon time of import.
The first solution has the drawback of still being fairly labour-intensive.
The second solution could work, but might be counter-intuitive for a lot of people ("why did it not import my level 85 2/5 Assimilated La Forge?").
The third solution would perhaps be neatest – the imported data does have the actual crew stats in, so it would be possible to take those into account instead of the sheet's preset stats at level 100 FE. Of course, there would be some discrepancy as you leveled your crew in-game for as long as you did not re-import your crew, although this should be relatively minor. This solution is the most work, however.
How are you doing the manual updates? I couldn’t figure out how to do that.
You can still manually update the sheet if you choose. You can just copy-paste from a previous version as before. Entering values into column B overrides the formulas that are in there for auto-filling. It's just column E ("Active") where you'll have to delete the checkboxes and add new ones back in to clear the underlying formulas that prevent the checkboxes from being toggled.
I am sure that upcoming versions will contain improvements to the automated functionality, however.
@Leshy I've already done the third option on my personal sheet. I'm looking to add this into the release version of the checklist as soon as @cmdrworf gets back to me in our group message.
I always used the 4th option before the auto-import. Marking the crew stars and not marking them as active. It works wonders for non FF crew because you can focus on FE the crew that brings the most returns. ( Voyagers, and gauntlet crew for exemple. finishing up a crew that's in 2 collections instead of one etc...)
The only issue is non FF that are not FE that appear as frozen instead of need but it's a minor inconvenience.
Hey @cmdrworf was the new Mirror Burnham one of those 4* exceptions? Just realising that on the most recent version of the sheet I'm using, she's frozen. But in my game she's FFFE and active
So, this is where my “giant male appendage” comment comes into play. I’m not a spreadsheet person and @Leshy’s work really does appear to be magic to me. That said, for dullards like me, I think “space/freeze” should mean that even at FFFE, this character does not improve your crew. That way, if I get that message at 1/4 or 2/4, out the airlock they go.
@PhilJS89 Stealth fixed her yesterday in the published version. Icheb was also wrong.
@Synthetic Commander @USS Dart We're working on a fix to this..... We'll post the details on our approach/proposal once we have it done.
I've never done it.
I did and found the word Spock in the revision log... Maybe something else would have happened if I had an US-QWERTY keyboard. I have no idea.