STT Level 100 Spreadsheet

Peachtree Rex

Zottex and I have moved the location of the Lvl 100 Spreadsheet to a location where we now have control over it. The original owner has stopped playing the game and we were unable to invite others to update, maintain, and contribute to it.

I've copied much of Zottex's FAQ here and added a few new items.

Link to the Spreadsheet


FAQ


Why do you add some strange number to totals?
A small number based on row number is added to each value in order to ensure every value is unique, because multiple same values create problems with rankings, e.g. Mirror Reagent Worf and Changeling Founder both have minimum of 1155 in their command skill.

Why some values are coloured for Cadets?
SeaGreen marks skill of 380+
DarkOrange shows skill of 270-379

270 skill is required to finish last mission in Cadet Challenges on Elite difficulty, while skill of 380 is needed to complete it on Epic.

What is ‘Personal ranking’ feature?
It allows player to mark characters they have and if ‘Personal ranking’ option is chosen, then all rankings will only show characters possessed by player.

How do I use ‘Personal ranking’ feature?
1. Copy the spreadsheet to your Google Drive (File -> Make a copy)
2. Fill ‘Owned’ (column V) on 2. Crew Stats tab.
3. Choose ‘Personal ranking’ option from dropdown in cell C2 in the Filters and Settings sheet.
4. Enjoy your own personal rankings!

I want to see all characters in ranking again.
Go to Filters and Settings Sheet and in cell C2 and choose ‘Full ranking’ option from dropdown.
There are a few other ranking options available as well: The 4* And Lower and 3* And Lower rankings will only include the selected ones in the rankings.


I disagree with your methodology on gauntlet ranking. What Gives??
We are always open to suggestions for how these are calculated. It isn't that hard to modify and I'm very open to suggestions on ways to better present this information.

For the overall ranking, we add ALL of a character's proficiencies (no matter how many stats they have). For the stat-pair ranking, we only consider the proficiencies of that given stat pair.

In the top left, you can choose to rank them by either Minimum Roll or Average Roll.


How do I include traits in my gauntlet ranking? Those make a huge difference!
They certainly do!

On the "3. Filters and Settings" sheet, there are three boxes around row 20 to enter traits. Enter the three traits for your current gauntlet and it'll factor those into the calculations on both the Overall and Top rankings.


Some of the data that I saw earlier is either hidden, appears missing, or is radically different from the last time I was here. Where'd it go?
Odds are, someone left a filter on the sheet. I happen to use this sheet myself and I try to always revert it to a "neutral" state, but it doesn't always happen.


What is column ‘Notes’ for?
You can add notes regarding given character if you wish.

Captain Nelson

Thanks to you all for doing this! It's very helpful to have as a handy reference and guide.

[9BE] Kanazak

I second that - thanks for taking the time @Peachtree Rex! It's one of my go-to resources.

Phd

When new spreadsheet versions come out, what's the best way to populate the new version from the old version, or do we just have to manually do it every time?

Peachtree Rex

Phd wrote: »

When new spreadsheet versions come out, what's the best way to populate the new version from the old version, or do we just have to manually do it every time?

So, the #1 concern is probably keeping your list of owned cards intact in the new version. Generally, I don't update the sorting of the Stats sheet. The top few hundred are all alphabetized, but I add on from the bottom. You SHOULD be able to just make a copy of the sheet and copy your "owned" list directly there if you're keeping htem in the same order.

Alternatively, you can add in the new ones at the bottom of your list, verify you have the same number, sort them alphabetically, and then copy your "owned" column into the new copy.

eXo | Cadet Matt

Oh Rex, my old nemesis. How I wish to destroy you in the gauntlet, yet cannot help but feel apprecation for your work on this database. It’s an excellent resource.

I’m still a bit confused about the voyages and the skill multiplier. Can you assist me with this? If the primary skill is MED and the secondary skill is ENG, what values am I entering in each of the skill boxes? I can’t seem to find the directions for this so I apologize if you’ve addressed this elsewhere.

Dralix

eXo | Cadet Matt wrote: »

I’m still a bit confused about the voyages and the skill multiplier. Can you assist me with this? If the primary skill is MED and the secondary skill is ENG, what values am I entering in each of the skill boxes?

I'm interested in the answer too, but I'll tell what I've done myself. I have been able to make use of this feature and the answer is "it depends."

For a normal voyage estimator, I used values of 1+3/9 for primary, 1+2/9 for secondary, 1+1/9 for the others. The fractions are one estimate that's been thrown out for the weight of the skills in hazards. I tried first just the fractions, but didn't like the result.

On the other hand, if I'm trying to identify a good sci/med/eng crew to level, I'd set the others to zero.

I think that might have been the intention - that you can use different weights for different reasons.

eXo | Cadet Matt

Dralix wrote: »

eXo | Cadet Matt wrote: »

I’m still a bit confused about the voyages and the skill multiplier. Can you assist me with this? If the primary skill is MED and the secondary skill is ENG, what values am I entering in each of the skill boxes?

I'm interested in the answer too, but I'll tell what I've done myself. I have been able to make use of this feature and the answer is "it depends."

For a normal voyage estimator, I used values of 1+3/9 for primary, 1+2/9 for secondary, 1+1/9 for the others. The fractions are one estimate that's been thrown out for the weight of the skills in hazards. I tried first just the fractions, but didn't like the result.

On the other hand, if I'm trying to identify a good sci/med/eng crew to level, I'd set the others to zero.

I think that might have been the intention - that you can use different weights for different reasons.

Thanks for the idea! I’ll give it a look.

Peachtree Rex

I wasn't too sure how to quantify exactly how to rank them, but I knew I wanted a system to amplify the score of the primary skills and to also downplay "other" skills.

I've had times I set the primary/secondary to a 2x multiplier and MED to a 0x multiplier and then picked the two highest remaining with the MED skill, simply because I didn't care if I tanked on it completely (since it'd be failing after 30 mins vs 45...).

Basically, I wanted it to be flexible because an "optimal" solution isn't particularly clear for that problem.

Edit: Yea, the intent is basically exactly as @Dralix has explained above. I'm totally open for other suggestions on how to calculate this, though.

[TFA] Gaois

The gauntlet part is very helpful and easy to use, cheers👍🏽

@R@ T'Pol

Why are there two Crell Moset? The first one has inaccurate stats for medical and science.

Peachtree Rex

@R@ T'Pol wrote: »

Why are there two Crell Moset? The first one has inaccurate stats for medical and science.

One is 1/5 stats and the other is Fully Fused. One of my predicessors on the sheet added in some partial fusion stats. I dont think it is suoer useful and the sheet would have awful performance if I added stats for all fusion levels.

Kgb

Please fix link on title page to point to New Forums thread as opposed to the old forums. Thanks

Peachtree Rex

Kgb wrote: »

Please fix link on title page to point to New Forums thread as opposed to the old forums. Thanks

I'm not sure I understand? The link is to a Google Sheet that is completely external to the forum.

{DD} Capt Jello

On the Cadet Minimalist page, is Pazlar still suggested for SCI, or has Festive Jadzia replaced her?

Data1001

Peachtree Rex wrote: »

Kgb wrote: »

Please fix link on title page to point to New Forums thread as opposed to the old forums. Thanks

I'm not sure I understand? The link is to a Google Sheet that is completely external to the forum.

Confusion regarding how the URL formatted in that post, perhaps? I've noticed there is a weird thing that happens on this forum when you paste a URL into your post then try to make it a link via the little icon above the edit window: it gets all weird-looking. But then I discovered that you don't need to make those into links at all. For example, if you type https-colon-slash-slash-google-dot-com as a URL, it will automatically turn into a clickable link when you post.

https://google.com

But if I were to copy and paste that below, then try to make it a link, here's what happens:

https://google.com

I'm also pretty sure that some of those done the latter way don't even resolve properly. I'm guessing this issue has to do with DB wanting to put an intermediary page in between this forum and whatever link you click on in a post (the intermediary being the page that says "You are now leaving Disruptor Beam Forums. Click the link to continue to [website]").


EDIT: Just as an addendum, the "link" feature works fine as long as the text you're making into a link isn't already a URL. Like if I want to link to Google by linking that word. Otherwise, the forum software gets confused, I guess.

{DD} Capt Jello

So, this is a great tool and I thank you for continuing it. I finally put all my data in for my crew. As I was doing so, I noticed a discrepancy on the Min/Max of a bunch of older crew. I remember when DB changed some stats and recipes which f-d up all the wiki stata. Seems a lot of it was not updated in the sheet. Many are just a difference of 2-5 points, but there are a couple that are way off. Here are some from my list that I noted:

Min/Max Inaccurate:
5*
"Dark Ages" McCoy
1701 Sisko
Age of Sail Crusher
Bashir, Julian Bashir
Captain Pike DIP
Changeling Founder
Commander Troi
Lt Commander Jadzia Dax
Mirror Kirk
Mirror Spock
Shinzon CMD/DIP

4*
1701 Jadzia Dax
Ambassador Sarek
Antaak
Captain Janeway
Captain Proton Paris
Captain Sulu
Changeling Bashir
Chief O'Brien
Command Hologram (SEC big drop)
Doctor La Forge
Holonovel Doctor
Kazon Seska
Lore
Mirror Jennifer Sisko
RAF Julian Bashir (MED Big Drop)
Rianna Mayweather
Tuskegee Mayweather

Not sure if you can re-pull from the wiki, but wanted to make you and others aware if it had not been brought up.

Also, it looks like someone was able to edit the sheet and there are a handful of crew that have "1/5" or something after their name (and copied as a duplicate crew member with these lower stats). You may want to remove them from the master list people copy. I used the idea for my crew, however putting 1/5 or 2/5 breaks the link to the other pages from showing/calculating that crew member rank. I found that if I put a (1) or (2) next to the name, that it allows the other pages to pull the data. I don't make a copy of the original line. I will just update the numbers if I get another fusion. This lets me see in the rankings what fusion level my crew are and if they can possibly get better stats.

Last, I tried to insert 2 columns next to Owned called "Immortalized" and "Frozen" so I could keep track of them easily. When I inserted the 2 columns, it messed up the calculations on the other pages (I thought Sheets and Excel were supposed to adjust formulas when rows/columns/cells are added/removed). For now, I added in the Notes "Immortalized, Frozen" if they are. Not easy to sort on, but if you add these columns, I'm sure it would be appreciated.

Peachtree Rex

Done:

1. Updated the link on the main post to look better
2. I deleted the "minimalist cadet" page. I feel there are better resources on this forum that are much more active that would better maintain that.
3. I added Festive Jadzia and Kathryn Janeway to the "380 chance" sheet

TODO:

1. Update stats changed. I was aware of this, but I had been unwilling to go back and update this for such a minimal change. I SHOULD fix this, I'm just not anxious about doing a full audit
2. Cadet 380 chance sheet needs a lot of TLC. It wasn't originally pulling from the stats sheet, so it doesn't updated dynamically when things are added and it doesn't pull the Starbase Modified stats.

{DD} Capt Jello

What is inflating the Gauntlet Max roll numbers? For example:

Locutus should be:
698 DIP/492 SEC/525 SCI (1715 Total)

But [Gauntlet] Overall calculates it at:
763 DIP/536 SEC/523 SCI (1822 Total)

All crew are doing this. Is there a multiplier somewhere that is throwing off the numbers? Everything else is correct (like calculating trait %)

Peachtree Rex

Can you please post a screen shot of what you're seeing?

First of all, I don't do any display of "Gauntlet Max" anywhere, all of the gauntlet sheets are based on average rolls.

If I take a screen shot of my current spreadsheet that has no traits set for gauntlet, I get the following:


As far as I can tell, these averages are all correct. The Crit% is applied to all of them and total skill should just be the summation of the components.

{DD} Capt Jello

What I mean by Gauntlet "Max Roll" is the stats for Gauntlet are based on Min-Max, so using the example of Locutus. DIP Base is 548 with proficiency Min Roll of 354 and Max of 698. If you match traits, his crit% can go from 5% on up to 65%, but that doesn't mean the proficiency Max Roll stat increases. When you enter the Gauntlet, his stats are:

DIP 354-698
SEC 247-492
SCI 197-525

From what I understand, no other numbers play into that proficiency Roll (like Starbase %) to change it. His Max Roll is his max roll. I thought the "[Gauntlet] Overall" tab was to list the proficiency Max roll of the crew skills and add them together and put them in order from greatest to least. So for me (and most everyone who has him FE), it should be 698+492+525 = 1715. I'd like to see this raw number with no other calculations as I don't rely on the 5%/25%/45%/65% crit helping much with DB-RNG the way it is. However, my "[Gauntlet] Overall" tab shows 763 DIP/536 SEC/523 SCI (1822 Total) and I'm wondering where the calculation increase is coming from... unless I don't understand how the Gauntlet numbers are supposed to work.

Peachtree Rex

Captain Jello wrote: »

What I mean by Gauntlet "Max Roll" is the stats for Gauntlet are based on Min-Max, so using the example of Locutus. DIP Base is 548 with proficiency Min Roll of 354 and Max of 698. If you match traits, his crit% can go from 5% on up to 65%, but that doesn't mean the proficiency Max Roll stat increases. When you enter the Gauntlet, his stats are:

DIP 354-698
SEC 247-492
SCI 197-525

From what I understand, no other numbers play into that proficiency Roll (like Starbase %) to change it. His Max Roll is his max roll. I thought the "[Gauntlet] Overall" tab was to list the proficiency Max roll of the crew skills and add them together and put them in order from greatest to least. So for me (and most everyone who has him FE), it should be 698+492+525 = 1715. I'd like to see this raw number with no other calculations as I don't rely on the 5%/25%/45%/65% crit helping much with DB-RNG the way it is. However, my "[Gauntlet] Overall" tab shows 763 DIP/536 SEC/523 SCI (1822 Total) and I'm wondering where the calculation increase is coming from... unless I don't understand how the Gauntlet numbers are supposed to work.

The gauntlet proficiency tabs rely on average roll. The formula for this is:

Average Roll = (1+Crit%)(Min Roll + Max Roll)/2

You can see these calculations on the "2. Crew Stats" sheet in columns AA through BP. The Crit% on individual skills aren't applied until they get to the [Gauntlet] Overall sheet. The Overall value is calculated on column BS, which is a summation of the summation of all average proficiencies multiplied by the crit.

You keep mentioning "max proficiency" but I do not use max proficiency alone anywhere in my calculations. Everything is driven off of average proficiency (that is, (Min+Max)/2). I also account for crit % as well in the individual rolls. If you don't want to include crit %, then that is fine, just leave the traits out. It will increase all chracters by 5%, but, since it's a uniform increase, it won't change the ordering of any of the ranking.

My guess is that you have added two traits into your spreadsheet. That would give him a crit% of 45 and increase his overall proficiency to 1822:


As I said, if you don't want to rely on Crit%, then simply don't enter traits on the "3. Filters and Settings" sheet.

eXo | Cadet Matt

The security stats for Maquis Tuvok are incorrect. The numbers listed are his science numbers. Thanks!

Peachtree Rex

eXo | Cadet Matt wrote: »

The security stats for Maquis Tuvok are incorrect. They numbers listed are his science numbers. Thanks!

Fixed!

Bethkoppy

How do I use ‘Personal ranking’ feature?
1. Copy the spreadsheet to your Google Drive (File -> Make a copy)
2. Fill ‘Owned’ (column CX) on 2. Crew Stats tab.
3. Choose ‘Personal ranking’ option from dropdown in cell A2.
4. Enjoy your own personal rankings


I've been trying to fill in the 'Owned' column, but it doesn't exist. The last column available is CP (Notes). Am I missing something?

Peachtree Rex

Bethkoppy wrote: »

How do I use ‘Personal ranking’ feature?
1. Copy the spreadsheet to your Google Drive (File -> Make a copy)
2. Fill ‘Owned’ (column CX) on 2. Crew Stats tab.
3. Choose ‘Personal ranking’ option from dropdown in cell A2.
4. Enjoy your own personal rankings


I've been trying to fill in the 'Owned' column, but it doesn't exist. The last column available is CP (Notes). Am I missing something?

"Owned" is column V in "2. Crew Stats". By default, all characters have been marked as owned.

I have updated the instructions.

guest_911225695432704

What happened to the list of 2* crew to keep for cadet missions? And while I'm on this topic, is there a list anywhere of the 3* crew we need to keep for cadet missions?

Peachtree Rex

guest_911225695432704 wrote: »

What happened to the list of 2* crew to keep for cadet missions? And while I'm on this topic, is there a list anywhere of the 3* crew we need to keep for cadet missions?

It was out of date and I was unwilling to try and update it. There are have been other threads dedicated to this, but I cant spend the time to link then until after the thanksgiving holiday.

tytlal

kinda missing Duchamps in there, but othervise, great tool, thank you!!!

Peachtree Rex

guest_904700998684674 wrote: »

kinda missing Duchamps in there, but othervise, great tool, thank you!!!

Good catch. Will add when I am back at my computer

EDIT: Added

Chewable C++

Hi @Peachtree Rex. First, big thanks for maintaining this.

I have a request/suggestion: Please add conditional formatting to highlight the crew you own. The personal ranking feature is nice, but I prefer to see them highlighted in context than have to choose to show only them.

I've been modifying a copy of your sheet to do this, but it's quite cumbersome to have to do this again whenever there's a major update to the sheet.

It would look like this: