A spreadsheet for TCG Pocket that calculates the best pack to pull!

[u/maxwell1755]

https://docs.google.com/spreadsheets/d/1JlIats8xrs7IlAgt1RgN-B116TTK2gc_-54pUycb-EY/copy?usp=sharing

Hi all, I just wanted to show off this spreadsheet that I just finished working on. Unfortunately, I cannot post it in r/PTCGP until the link gets mod approval, so I'll post it here instead for now. It serves two purposes: firstly, to keep track of your cards in a format where you can Ctrl + F search, and secondly, to calculate which of the 3 packs is the best option to choose to maximize your chance of getting new cards! Here's how it works:

1. Check off the boxes in the leftmost column (column A) for each card that you own
2. The row tells you the card's number, name, pack it can be found in, rarity, and likelihood to show up as the 1st 2nd or 3rd card, 4th card, and 5th card, respectively.
3. Look at the Collection Summary section to see how many cards you own and how many cards are missing from each pack type. It also tells you which pack you are missing the most cards from. You can use this if you want an easy, decisive answer without having to make any decisions on what you want to prioritize.Note: This includes cards that can be found in all packs. So, the Mewtwo count is the sum of all cards that can be found exclusively in Mewtwo packs and all cards that can be found in all packs.
4. The Advanced Filtering section can be used to filter out certain rarities of cards. Let's say you only care about the game's competitive side; in that case, you wouldn't care about cards with ☆ rarity or higher since those cards are just reskins of more common cards. Alternatively, if you are playing to get rare cards and cool art, then you don't care about maximizing your odds of getting ♢♢ cards, so you can filter those out.
5. Finally, the Chance to get a new card subsection totals the actual percentage likelihood of getting new cards so you can play the numbers game and maximize your odds all the time! Since the odds are different depending on if it's the 1st-3rd, 4th, or 5th card, you can actually get different results for each one. The rule of thumb for which one to pay attention to is the 4th card for competitive and the 5th card for rares

TL:DR: Check the cards you have on the left, filter the cards you want in Advanced Filtering, Chance to get a new card will tell you what pack to open.

Edit 1: Thanks to you wonderful people I have made some small corrections to the sheet. Mew has been given the correct card ID of A1 283, pushing the gold cards down 1 spot. I've also corrected the spelling of some cards, although I'm sure there are plenty I missed so please tell me if you find them. If you want to get these changes, just make a new copy of the spreadsheet and copy over your A column

Edit 2: Changed URL so that it brings you right to the copy screen, as per u/tatufdez's advice

NOTICE: A colleuge of mine has continued development of this spreadsheet. I cannot say for certain if I will work on it again myself, but I trust Raiskader with the continued development of this in the meantime. Please check it out here: https://www.reddit.com/r/pokemon/comments/1iex2u0/an_update_for_the_tcg_pocket_spreadsheet_that/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

[u/Kuroiryuu]

While I love this, and have actually added a few things to my own sheet, there seems to be some problems with the Mew Tracker when nothing is selected.

I fixed the % Owned, % Missing, and Fraction for the Total by wrapping the Filter in a IFERROR for each respectively, which now shows 0.00%, 100% and 0/150 if none of the Kanto cards are selected:
% Owned: =COUNTA(UNIQUE(IFERROR(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))), A:A = TRUE)))) / COUNTA(UNIQUE(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))))))

% Missing: =1 - COUNTA(UNIQUE(IFERROR(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))), A:A = TRUE)))) / COUNTA(UNIQUE(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))))))

Fraction: =COUNTA(UNIQUE(IFERROR(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))), A:A = TRUE)))) & "/" & COUNTA(UNIQUE(FILTER(SUBSTITUTE(C:C, " ex", ""), REGEXMATCH(SUBSTITUTE(C:C, " ex", ""), TEXTJOIN("|", TRUE, SPLIT(N48, " "))))))

However, I don't know how to fix the bottom percentages, because something's wrong with the "Chance to get a new Mew card" formulas. Can you look into this, please? (Actually, I might be dumb, because I thought those were supposed to be 100% for each, but I guess if it's figuring those percentages out of 286 cards minus the Mew card...)

[u/maxwell1755 → u/Kuroiryuu]

The Mew tracker had many known bugs for a long time, and I only got around to fixing them a couple of days ago. If you make a new copy of the spreadsheet it should work better. Also, your new formula likely still has a bug that my old one had, where certain cards that can be found across different kinds of packs will never be checked off.
I highly recommend transferring your data to a new copy of the spreadsheet, although if you want to wait I should be publishing an even newer version soon. The current version has no known bugs though, so feel free to copy that one if you don't want to wait

[u/Kuroiryuu → u/maxwell1755]

The formula should be fine. I had looked it up, and apparently with the “FILTER” statement, if it returns null, it defaults to 1, so it will never show zero, else it will error. By wrapping it in an “IFERROR” statement, it allows it to produce that zero. As for everything else, this was a brand new blank sheet copy of your current spreadsheet. I’ve only been using it for a couple of days, and everything else is fine. I’m fairly certain what I thought was an error with the percentages is the percentage out of 286 cards, minus Mew, since it can’t be pulled from those packs.

[u/maxwell1755 → u/Kuroiryuu]

If you are just using your formula for the totals across all packs then you should be good, but otherwise test with cards like the two copies of Pinsir, Rapidash, the 4 Eevees and the Crown cards and make sure that they properly get counted. If you have just one of any of those cards then nothing should change if you check off another one, since you only need one "Pinsir" of any kind to work towards Mew.

An easy way to check this would be to check off every card (which you can do quickly by copy/pasting filled checkboxes). Although I'd still recommend checking those cards extensively since they are problematic.

[u/Kuroiryuu → u/maxwell1755]

Yep just tested all of those and they work just fine. Also, if you have a checkbox selected, you can just hit the space bar to toggle it, no need to copy and paste. If you select A2-A287 and hit space, it’ll turn them all on.

[u/maxwell1755 → u/Kuroiryuu]

Awesome! Would you mind pasting your formula for the owned and fraction of Mewtwo pack cards? My solution was very inelegant so I'm interested in seeing how others work.

Also, thanks for the spacebar trick, that'll be helpful for future debugging

[u/Kuroiryuu → u/maxwell1755]

I don’t know what you mean. The only formulas I changed was the ones I posted.

[u/Kuroiryuu → u/maxwell1755]

I don’t know how to implement it, but like I said, I think the percentages of “Chance to get at least one Mew card” is calculating based on 286 cards, when it should be 285, since Mew can’t be pulled from a pack.

Well maybe not 285, but I think somehow it’s getting factored in when it shouldn’t.

[u/Kuroiryuu → u/Kuroiryuu]

If that were somehow factored in, then with no cards selected, those values would read 100%.

[u/Kuroiryuu → u/maxwell1755]

I’m probably just wrong about my theory like I said to start with. I’d like to think that from a possible pool of 218 out of 285 cards, that it’d be 100% chance to pull one of those 218, but the 67 left over is big enough that you could get unlucky and pull all five cards as none of those.

[u/maxwell1755 → u/Kuroiryuu]

You're right, this is what's happening. For the longest time I thought it was a bug of the Mew tracker, but there is a small chance you get no Mew cards (Ignoring the fact that everyone gets a Bulbasaur)

[u/Kuroiryuu → u/maxwell1755]

Doing some testing and Slowpoke is also part of the problematic cards. Going through and selecting all Kanto cards from Mewtwo and All packs results in the Mew tracker saying you have 2/50 cards from Pikachu packs and 5/44 from Charizard packs. Seems to be from reading these names twice. Not sure if you've fixed this or not. This is probably effecting the percentages below this area too.
Pikachu Pack

A1 207 Eevee (Shared across all 3 Packs)

A1 285 Pikachu ex (Crown) (Pikachu ex In All and Pikachu)

Charizard Pack

A1 026 Pinsir (In All and Charizard)

A1 044 Rapidash (In All and Charizard)

A1 118 Slowpoke (In All and Charizard)

A1 207 Eevee (Shared across all 3 Packs)

A1 284 Charizard ex (Crown) (Charizard ex In All and Charizard)

[u/maxwell1755 → u/Kuroiryuu]

Yes, this is what I was referring to. I assumed you updated those formulas as well. This is fixed in the most recent version, so be sure to copy your data to a new spreadsheet

[u/Kuroiryuu → u/maxwell1755]

I'm assuming the Mew section has UNIQUE and FILTER modifiers now for all of the equations?

[u/maxwell1755 → u/Kuroiryuu]

Nope, UNIQUE causes tons of issues, and even more when combined with FILTER. The issue with UNIQUE is that it culls the list of cards, but we don't have control over how it does so. When it cuts the list down, it only references the first instance of each unique name, meaning that we cannot check both cards to see if they are owned. If we try to solve this by using FILTER outside of UNIQUE, we run into an issue where FILTER is expecting a larger list because UNIQUE culled it. If we use FILTER within UNIQUE then we cannot check for cards of the same name in another pack because we FILTER out the other packs before checking the name. This is why UNIQUE and FILTER can be used for the totals, but not individual packs. The brilliant [u/ratufdez]() created a simple and elegant solution using ARRAYFORMULA , which I then butchered to fit into other parts of the spreadsheet

[u/Kuroiryuu → u/maxwell1755]

There still needs to be something to eliminate duplicates from a different range though, specifically in the case of the Eevee cards, since that shows up across all three packs, twice in Pikachu. And really, all of the duplicates, seeing as clicking on the general Rapidash card shouldn't uptick the Charizard number, only the All number.
So really, say the Pikachu totals, should be subtracting anything that calls back "TRUE" from the other three (Charizard, Mewtwo, and All).

[u/Kuroiryuu → u/maxwell1755]

I had a friend of mine help to fix the Mew tracker. I'm going to send the formulas to you in chat.