Captain Idol's Polestar Analysis Sheet 1.1

Captain Idol

Hello and welcome all to CIPAS (thanks @JiveDutch for bringing these acronyms to my attention).

This is the first iteration of my Polestar Analysis Sheet. In this you can select Polestars and the sheet will give you a list of all crew that can be pulled from that pool. Also there is a search function too should you want to find a specific crew to try and target.
To use simple copy the sheet with File -> Copy (don't ask for access) and play to your hearts content. Enjoy!

Captain Idol's Polestar Analysis Sheet: https://docs.google.com/spreadsheets/d/1Hov3w3C6qOwQFO9e8O-xYbSPveEFnUzGxT4eJ_xchNQ/edit#gid=1474243727

Mururoa

My godness !
I still didn't understood what was that 'polestar' about and you already made a sheet.
Kudos !

NoNameNamer

This is very useful, thank you!

Here is a very large spreadsheet listing all the best possible polestar combos for each retrievable crew that could be useful in conjunction to CIPAS™.
Best polestar combos by crew

All but 76 crew can be uniquely identified with 1-4 polestars, some easier than others. These are the hardest to snipe:


For data analysis fiends, there's additional information for each polestar such as pool size, number of golds, number of top 100 voyagers in it, number of crew rated at BB tier 5 or better, number of crew with a stat boosting collection, number of crew with a difficult to complete collection, with percentages relative to the pool size for all of these numbers. You can create a filtered view to sort by these metrics however you want.

There are some interesting finds, e.g. if you still need many mirror universe gold cards, the terran empire polestar can be more useful than the actual mirror universe polestar. It only has 57% of the mirror crew but 69% of all the mirror golds.

[update] I''ve added a set of probabilities and a new table with the easiest combinations possible that guarantee gold crew.

Below you can see:
- chance of pulling a constellation with that polestar
- avg chance of pulling the polestar from a constellation that has it
- avg chance of pulling the polestar from a random constellation (entire set)


Easiest 2-3 polestar combos that guarantee gold:

Captain Idol

NoNameNamer wrote: »

This is very useful, thank you!

Here is a very large spreadsheet listing all the best possible polestar combos for each retrievable crew that could be useful in conjunction to CIPAS™.
Best polestar combos by crew

All but 76 crew can be uniquely identified with 1-4 polestars, some easier than others. These are the hardest to snipe:


For data analysis fiends, there's additional information for each polestar such as pool size, number of golds, number of top 100 voyagers in it, number of crew rated at BB tier 5 or better, number of crew with a stat boosting collection, number of crew with a difficult to complete collection, with percentages relative to the pool size for all of these numbers. You can create a filtered view to sort by these metrics however you want.

There are some interesting finds, e.g. if you still need many mirror universe gold cards, the terran empire polestar can be more useful than the actual mirror universe polestar. It only has 57% of the mirror crew but 69% of all the mirror golds.

This is amazing. I'll stick a link to it in the sheet.

JiveDutch

Mururoa wrote: »

My godness !
I still didn't understood what was that 'polestar' about and you already made a sheet.
Kudos !

If you substitute Polestar into " Trait Token" in your mind ... and Constellation into "Trait Set" it seems to make alot more sense ... ( at least it does for me) ...

joshurtree

I've merged some of the stuff I was doing into the sheet. Can be found at
docs.google.com/spreadsheets/d/1R6i9PNGbL_KkyFL2avVI_C-NKB1z3dNXavxhkHaIB7c

The main difference is the addition of drop chances for each Polestar plus the average amount of Constellations you'll need to open before you get it. Feel free to add it to the main sheet if you wish.

Captain Idol

joshurtree wrote: »

I've merged some of the stuff I was doing into the sheet. Can be found at
docs.google.com/spreadsheets/d/1R6i9PNGbL_KkyFL2avVI_C-NKB1z3dNXavxhkHaIB7c

The main difference is the addition of drop chances for each Polestar plus the average amount of Constellations you'll need to open before you get it. Feel free to add it to the main sheet if you wish.

Oh that is quite cool! I'll integrate it next time I get a chance to and give you a credit.

Captain Idol

joshurtree wrote: »

I've merged some of the stuff I was doing into the sheet. Can be found at
docs.google.com/spreadsheets/d/1R6i9PNGbL_KkyFL2avVI_C-NKB1z3dNXavxhkHaIB7c

The main difference is the addition of drop chances for each Polestar plus the average amount of Constellations you'll need to open before you get it. Feel free to add it to the main sheet if you wish.

Now incorperated your drop chances into the sheet. 1.1 available so delete and re-copy your existing sheets.

Ward Serpentine

Love the new sheet. I added a Data Validation to the Name Search on my sheet, as typing the info in is case sensitive.

Ward Serpentine

One thing I noticed, if you put Klingon in as a trait, non-klingons show up (Mirror Garak). Probably because he has a trait called Klingon-Cardassian Alliance.



It's minor, since Mirror Garak is the only one with KCA that doesn't also have Klingon.

Captain Idol

Ward Serpentine wrote: »

One thing I noticed, if you put Klingon in as a trait, non-klingons show up (Mirror Garak). Probably because he has a trait called Klingon-Cardassian Alliance.



It's minor, since Mirror Garak is the only one with KCA that doesn't also have Klingon.

Yeh there are a couple of things I need to tweak like that. I might change my "contains" queries to "matches" to get something a bit more specific and see if that works.
Also searching will not find jah'Sepp or Ba'ul due to the comma so I need to work in a few REGEX commands somewhere.

RaraRacing

Hah ... I see that @NoNameNamer did the maths for me ... thanks! I had started writing a reply yesterday but hadn't finished the post, was too tired.

- Soji Asha is the "easiest" 5* to get a hold of - too bad she's immortal already.
- Riker Odan is #2 - don't own him
- A duo of Spock's ... Kolinahr and Seeking Spock are tied 3rd - 1/5 and don't own.

This means that these will be the most likely "default" options when Quantum starts to reach maximum and I have no specific option to go after.

I have to say, not a bad selection of three crew really ... a Spock with MED, decent Riker and decent (for events) older Spock that I already own.

Ditto

Captain Idol wrote: »

Hello and welcome all to CIPAS (thanks @JiveDutch for bringing these acronyms to my attention).

This is the first iteration of my Polestar Analysis Sheet. In this you can select Polestars and the sheet will give you a list of all crew that can be pulled from that pool. Also there is a search function too should you want to find a specific crew to try and target.
To use simple copy the sheet with File -> Copy (don't ask for access) and play to your hearts content. Enjoy!

Captain Idol's Polestar Analysis Sheet: https://docs.google.com/spreadsheets/d/1Hov3w3C6qOwQFO9e8O-xYbSPveEFnUzGxT4eJ_xchNQ/edit#gid=1474243727

Hey .. great work on this .. finding it handy . however, i do find 1 troublesome issue ... in the cell to search for crew (A13), it's case sensitive ..
For example:
"Age of Sail Troi" finds nothing.
"Age Of Sail Troi", however, works.
Not knowing what your captilized, or not is tricky Any chance to make that a case insensitive search?

Ward Serpentine

Ditto wrote: »

Captain Idol wrote: »

Hello and welcome all to CIPAS (thanks @JiveDutch for bringing these acronyms to my attention).

This is the first iteration of my Polestar Analysis Sheet. In this you can select Polestars and the sheet will give you a list of all crew that can be pulled from that pool. Also there is a search function too should you want to find a specific crew to try and target.
To use simple copy the sheet with File -> Copy (don't ask for access) and play to your hearts content. Enjoy!

Captain Idol's Polestar Analysis Sheet: https://docs.google.com/spreadsheets/d/1Hov3w3C6qOwQFO9e8O-xYbSPveEFnUzGxT4eJ_xchNQ/edit#gid=1474243727

Hey .. great work on this .. finding it handy . however, i do find 1 troublesome issue ... in the cell to search for crew (A13), it's case sensitive ..
For example:
"Age of Sail Troi" finds nothing.
"Age Of Sail Troi", however, works.
Not knowing what your captilized, or not is tricky Any chance to make that a case insensitive search?

You can fix this by right clicking on the cell, select Data Validation and enter the values as follows:

Ward Serpentine

I think that some of the math is off on the drop calculations. I was comparing a single trait odds crew (Bem) and found that the sheet shows the odds of getting the Pandronian trait at 0.01961 when the calculated odds are 0.0145 (1/862 * 1/8) I started digging into the JoshCalc page and noticed that the total retrievable crew value is off by one, meaning that the import sheet has one more crew than is in the pool. Also, since the Rarity and Skill polestars don't drop in the same Constellation as Traits, those numbers are skewed too. The odds of any skill should be 1/6 and rarity should be 1/5.

NoNameNamer

Getting the percentages right is tricky because of how WRG designed this. However, you can check my sheet above for accurate numbers.

Portal crew is retrievable. However, constellations exist only for 4* and *5 crew. Not all of those crew constellations are for crew available in the portal or retrievable. EV Suit Tuvok is listed twice as retrievable crew, double the chances. Not all non-portal 4* and 5* crew have a constellation yet. Then there are also some available trait polestars which don't have a constellation that drops them but are listed in the UI when you check unowned polestars.

Here are some numbers:

Polestars: 199

• 11 aren't part of any constellation: allasomorph, benzite, elaysian, kalandan, kataan, lurian, nacene, nasat, pakled, tamarian, zibelian

Constellations: 699

• 320 super rare crew constellations
• 377 legendary crew constellations
• 1 rarity constellation
• 1 skill constellation

Retrievable crew: 862

• 26 commons
• 52 uncommons
• 87 rares (EV Suit Tuvok twice)
• 319 super rares (missing Siranna)
• 377 legendaries

Ditto

Ward Serpentine wrote: »

Ditto wrote: »

Captain Idol wrote: »

Hello and welcome all to CIPAS (thanks @JiveDutch for bringing these acronyms to my attention).

This is the first iteration of my Polestar Analysis Sheet. In this you can select Polestars and the sheet will give you a list of all crew that can be pulled from that pool. Also there is a search function too should you want to find a specific crew to try and target.
To use simple copy the sheet with File -> Copy (don't ask for access) and play to your hearts content. Enjoy!

Captain Idol's Polestar Analysis Sheet: https://docs.google.com/spreadsheets/d/1Hov3w3C6qOwQFO9e8O-xYbSPveEFnUzGxT4eJ_xchNQ/edit#gid=1474243727

Hey .. great work on this .. finding it handy . however, i do find 1 troublesome issue ... in the cell to search for crew (A13), it's case sensitive ..
For example:
"Age of Sail Troi" finds nothing.
"Age Of Sail Troi", however, works.
Not knowing what your captilized, or not is tricky Any chance to make that a case insensitive search?

You can fix this by right clicking on the cell, select Data Validation and enter the values as follows:

Sweet!! That's handy(ier) Thanks!!

Ward Serpentine

NoNameNamer wrote: »

[*] 319 super rares (missing Siranna)
[/list]

Siranna is currently not in the portal. But the duplicate EV Tuvok is odd. I'm not sure that rare and below constellations don't exist, but according to the notes Shan put out every portal crew in the game should have (or eventually have) a constellation. I'm not looking forward to the day that I get Glinn Damar with his single trait.

NoNameNamer

Ward Serpentine wrote: »

Siranna is currently not in the portal. But the duplicate EV Tuvok is odd. I'm not sure that rare and below constellations don't exist, but according to the notes Shan put out every portal crew in the game should have (or eventually have) a constellation. I'm not looking forward to the day that I get Glinn Damar with his single trait.

She is in the portal as of yesterday.
The game has no knowledge of 1-3* crew constellations at this time.

joshurtree

I made a faulty assumption when doing the calculations which would only hold if all Constellations were the same size. It is still roughly true for most cases as Constellation sizes are all within an order of magnitude. I have adjusted my sheet to account for this and also the fact that only 4-5* crew are being used as Constellations. The later seems to have a much larger effect on the numbers.

One oddity is that it only finds 187 Polestars (not 188). All the other numbers match up with what NoNameNamer found.

Veterinary Phlox

This has been super helpful in figuring out which polestars to set aside for my wishlist crew.

Saru

This is all I see. What an I doing wrong?

IceCat

Saru wrote: »

This is all I see. What an I doing wrong?

If that is @NoNameNamer 's sheet, I believe they may have taken it down in response to the recent ban fiasco. Unfortunately, they've walked away from the game, and seem to have taken their resource with them.

NoblemanDK

IceCat wrote: »

Saru wrote: »

This is all I see. What an I doing wrong?

If that is @NoNameNamer 's sheet, I believe they may have taken it down in response to the recent ban fiasco. Unfortunately, they've walked away from the game, and seem to have taken their resource with them.

And that is something i just dont understand. They are not hurting TP by doing so, but only players that did benefit(And appreciated) from their outstanding job, and then the work they put into it is just wasted

IceCat

NoblemanDK wrote: »

IceCat wrote: »

Saru wrote: »

This is all I see. What an I doing wrong?

If that is @NoNameNamer 's sheet, I believe they may have taken it down in response to the recent ban fiasco. Unfortunately, they've walked away from the game, and seem to have taken their resource with them.

And that is something i just dont understand. They are not hurting TP by doing so, but only players that did benefit(And appreciated) from their outstanding job, and then the work they put into it is just wasted

Respectfully, I can understand both your point, and also see it from their perspective.

If I was leaving in such a bitter way, I don't know if I would want my hard work kicking around after. Particularly if my work was indirectly benefiting TP, by way of making players have more options to spend.

If their work is wasted, well, it was theirs to waste.

Veterinary Phlox

It's painful, for the community and likely the creator, to remove a resource like this. But a living document becomes a liability if it isn't kept updated. Some parts of this would have remained relatively static until the portal updates, but every new trait added shifts some of these stats.

IceCat

Veterinary Phlox wrote: »

It's painful, for the community and likely the creator, to remove a resource like this. But a living document becomes a liability if it isn't kept updated. Some parts of this would have remained relatively static until the portal updates, but every new trait added shifts some of these stats.

That's another detail I hadn't considered. Good point.

Either way, whether NNN left their sheet up or not, the main point still remains that this nonsense pushed them into leaving in the first place. To me, that is far more important that a sheet.

This is a real person who not only put all that work into it, but also got burned enough by TP to quit. Let's please not lose sight of that.

Ditto

Hey .. just tried searching for "Augment Commander O'Brien" .. I think something's going wacky with the quotes in the name ?





And confirming he is in there: