Since, unfortunately, the formulas do not work when exporting to openoffice, and I don't know if this might be the case for other spreadsheet programs - here's the old version with simple formulas, in case you encounter this problem:
Not sure if i should be worried or not that i have a 472.7386031% chance of finding a classic card i need in a pack. Is this intended or is it a bug? Should the percentage really be that high?
Not sure if i should be worried or not that i have a 472.7386031% chance of finding a classic card i need in a pack. Is this intended or is it a bug? Should the percentage really be that high?
You get 5 cards a pack - so if your collection is really fresh, ye.
The first pack you open will have 499% of containing new cards - that's 5 new cards. Small chance of duplicates in first batch though.
Rollback Post to RevisionRollBack
Worlds "bestest" collectors spreadsheet, download at your leisure:
Also, if you really want to break your brain - here's a fun one for you.
What's the chance that after getting 98 epic out of 100 epic cards, you are left missing 2x the same card, instead of 2 different cards?
See, it's easy using the previous naive interpretation, you're just choosing the last two cards out of a hundred, and there's 50 ways for those two to be a particular pair, and 100*98 ways for it to be a non pair, meaning the chances of having a pair left at the end are 50/9850=1/197. but if we account for the fact cards are rolled as one of fifty choices....
The first 49 cards (or middle or last 49, whatever) make no contribution to the probability, by the pigeon hole principle there's still one pair left open, let's pick those 49 so there's exactly one left open, and there's again fifty ways to select which pair this is. If we don't worry about what hapens to third plus cards, then there's 49/50 chance to get a repeat, then there's 48/49, then 47/48....etc, the end result is, there's a 1/50 chance that none of tthhe next 49 new cards are in the missing pair.
I haven't downloaded yours to check the formulas, but these 499% just don't make sense, so something must be inaccurate.
Below is a screen of my own Excel Sheet, below right is the TGT odd calculation. It shows 100%, and I didn't include any "trick" to reduce it. The 100,01 is a rounding error by Excel in the binomial calculation, and the # boxes are related to the binomial calculation aswell, they disappear as soon as I put in at least 1 card in my collection (top left).Looks like this when I put in that I have 2 rare and 10 commons, for example:
(Left: Only considering cards you don't have at all, Right: Considering cards that you don't have at all AND non-legendary cards you have only 1)
(The Avg Dust/Pack calculates the crafting value for cards you still need for a full collection with 2 of each non-legendary and the disenchant value for cards you already have 2 copies or 1 in case of legendaries)
I haven't downloaded yours to check the formulas, but these 499% just don't make sense, so something must be inaccurate.
Below is a screen of my own Excel Sheet, below right is the TGT odd calculation. It shows 100%, and I didn't include any "trick" to reduce it. The 100,01 is a rounding error by Excel in the binomial calculation, and the # boxes are related to the binomial calculation aswell, they disappear as soon as I put in at least 1 card in my collection (top left).Looks like this when I put in that I have 2 rare and 10 commons, for example:
(Left: Only considering cards you don't have at all, Right: Considering cards that you don't have at all AND non-legendary cards you have only 1)
(The Avg Dust/Pack calculates the crafting value for cards you still need for a full collection with 2 of each non-legendary and the disenchant value for cards you already have 2 copies or 1 in case of legendaries)
I keep saying this,
there's 5 cards in a pack.
499% simply means, 5 new cards from your first pack with a fresh collection, with a small chance of duplicates.
However I wouldn't mind the code for all that, especially the dust/pack seems very interesting.
Also, if you really want to break your brain - here's a fun one for you.
What's the chance that after getting 98 epic out of 100 epic cards, you are left missing 2x the same card, instead of 2 different cards?
Actually the chance to have 2 same cards in the end is high, if you take into account the nature of the game. I miss 5 rares from the classic set, and 4 of them are Flare and Gadgetzan Auctioneer, because I disenchanted them when they got nerfed and never found them since, haha. That's how I came across the issue. The spreadsheet tells me I have the same chance to find a new card in the classic set and gvg, which isn't true (rares especially influence the percentage a lot).
Dinaverg's right though, without weird situations like mine the data shouldn't favor one or the other set. But still, it's nice to see the actual chance to find a new card in the set. It was very inaccurate before, because consider this:
By some miracle you found exactly 1 copy of each of 100 rares, the chance to find a new one would appear to be half while in fact it would be the exact starting one. Of course there's no chance of that happening, but still, if in the end you're missing 5 different cards, which isn't unlikely, the chance shown would still be very wrong. So it's not a discrepancy of 1%.
Now it's fixed though, so thanks! I'll have to write off all the cards again, copying the missing parts seems like more work...
Also, if you really want to break your brain - here's a fun one for you.
What's the chance that after getting 98 epic out of 100 epic cards, you are left missing 2x the same card, instead of 2 different cards?
Actually the chance to have 2 same cards in the end is high, if you take into account the nature of the game. I miss 5 rares from the classic set, and 4 of them are Flare and Gadgetzan Auctioneer, because I disenchanted them when they got nerfed and never found them since, haha. That's how I came across the issue. The spreadsheet tells me I have the same chance to find a new card in the classic set and gvg, which isn't true (rares especially influence the percentage a lot).
Dinaverg's right though, without weird situations like mine the data shouldn't favor one or the other set. But still, it's nice to see the actual chance to find a new card in the set. It was very inaccurate before, because consider this:
By some miracle you found exactly 1 copy of each of 100 rares, the chance to find a new one would appear to be half while in fact it would be the exact starting one. Of course there's no chance of that happening, but still, if in the end you're missing 5 different cards, which isn't unlikely, the chance shown would still be very wrong. So it's not a discrepancy of 1%.
Now it's fixed though, so thanks! I'll have to write off all the cards again, copying the missing parts seems like more work...
Ye I had to write them off again also - which isn't so bad because I get to see the discrepancy now for myself, still having the old version.
But you're right, if there's 100 different rares, and you need 2 copies of each, if you happen to find 1 copy of each, when you get 100 rares, you still have a 100% chance to find a new rare in a pack. I'll post the difference in % for fun when I'm done updating my collection -again- :P
Rollback Post to RevisionRollBack
Worlds "bestest" collectors spreadsheet, download at your leisure:
So which ever's lower, still remains the lowest, there can be either a big or a small variation though.
I should maybe add that for GvG the lowest percentage, I miss 1 rare card, which I think is where your chances drop dramatically, whereas for Classic I still need 1 common and 14 rares. (Unleash because I dissed it when they nerfed it :D)
Rollback Post to RevisionRollBack
Worlds "bestest" collectors spreadsheet, download at your leisure:
There was another error that made the chance to find a new card wrong but now it's fixed.
For those who already copied it, the fix is really easy: just go to the yellow brackets above "CHANCE THAT A CLASSIC PACK HAS A CARD YOU'RE MISSING", and:
in the yellow bracket under "common", when you click it its value at the top must read
There was another error that made the chance to find a new card wrong but now it's fixed.
For those who already copied it, the fix is really easy: just go to the yellow brackets above "CHANCE THAT A CLASSIC PACK HAS A CARD YOU'RE MISSING", and:
in the yellow bracket under "common", when you click it its value at the top must read
I currently use your spreadsheet, or at least an old version of it and think it's brilliant, so firstly thank you very much.
Just wanted to ask, will you be making a new version, including the Grand Tournament cards?
If so, will I have to download a new spreadsheet and re-delete all collected cards, or not?
Either way I'm very grateful and thanks bhtrix.
Hey milk1, there is a new version up, and it is indeed already updated. I would advice downloading this new one and re-deleting your cards since a few things have changed that made it more accessible including a few bug fixes, name changes and the like (minor tweaks if you will) You could try to copy and paste the TGT card set into your collection, but you might need to update the formulas - if you know how to, this is certainly a good solution.
The reason I made it more accessible. and moved the total cards to the top is mainly so that in the future it will be a lot easier to simply copy the new expansion into your existing sheet. (e.g. you simply copy everything at the bottom into the bottom of your existing sheet and you should be good to go.) I also updated all formula's so they get their values from the top section, this way I only have to update those numbers and it automatically updates the rest of the sheet - again making it easier to add new content without having people to re-download and stripe their cards off again.
499% simply means, 5 new cards from your first pack with a fresh collection, with a small chance of duplicates.
The issue is the way it's worded, the number you give makes sense when you describe it that way, but the spreadsheet says it's the chance that a pack has a card that you're missing. The actual chance that a pack has a card that you're missing has to be between 0% and 100%, obviously it's impossible for any probability to be over 100%.
What I think it's actually reporting is 100x the "expected number of new cards per pack". If you don't have anything other than basic cards, you'd expect to get 4.99 new cards in your next pack. I think that's a better way to describe it.
Also, while I'm at it, one small typo correction: Frothing Berserker is spelled "Beserker" in the spreadsheet.
499% simply means, 5 new cards from your first pack with a fresh collection, with a small chance of duplicates.
The issue is the way it's worded, the number you give makes sense when you describe it that way, but the spreadsheet says it's the chance that a pack has a card that you're missing. The actual chance that a pack has a card that you're missing has to be between 0% and 100%, obviously it's impossible for any probability to be over 100%.
What I think it's actually reporting is 100x the "expected number of new cards per pack". If you don't have anything other than basic cards, you'd expect to get 4.99 new cards in your next pack. I think that's a better way to describe it.
Also, while I'm at it, one small typo correction: Frothing Berserker is spelled "Beserker" in the spreadsheet.
Thanks! fixed the typo.
Rollback Post to RevisionRollBack
Worlds "bestest" collectors spreadsheet, download at your leisure:
Dark Wisper instead of Dark Whisper
Since, unfortunately, the formulas do not work when exporting to openoffice, and I don't know if this might be the case for other spreadsheet programs - here's the old version with simple formulas, in case you encounter this problem:
https://docs.google.com/spreadsheets/d/1GhqNiBv453OozFX3Fts6orc3wH5sfMmmkNPCjM7nfoE/edit#gid=191287612
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Thanks!
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Not sure if i should be worried or not that i have a 472.7386031% chance of finding a classic card i need in a pack. Is this intended or is it a bug? Should the percentage really be that high?
[url=http://signaturecraft.us/][img]http://signaturecraft.us/signatures/eJxzyyxKVUgqLcorVkgqykzPKFHIzFMoyUhVSEksyubyLIHKpZal5kEVpBYplGekghUVpRYrJCoUF4CUBmTkp-ZlVphbGnO556co-KcphCcWcbkApRR0FZyA6n1Ti5IzUnO4jExN4dgQABU6KQY~.png[/img][/url]
You get 5 cards a pack - so if your collection is really fresh, ye.
The first pack you open will have 499% of containing new cards - that's 5 new cards. Small chance of duplicates in first batch though.
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
See, it's easy using the previous naive interpretation, you're just choosing the last two cards out of a hundred, and there's 50 ways for those two to be a particular pair, and 100*98 ways for it to be a non pair, meaning the chances of having a pair left at the end are 50/9850=1/197. but if we account for the fact cards are rolled as one of fifty choices....
The first 49 cards (or middle or last 49, whatever) make no contribution to the probability, by the pigeon hole principle there's still one pair left open, let's pick those 49 so there's exactly one left open, and there's again fifty ways to select which pair this is. If we don't worry about what hapens to third plus cards, then there's 49/50 chance to get a repeat, then there's 48/49, then 47/48....etc, the end result is, there's a 1/50 chance that none of tthhe next 49 new cards are in the missing pair.
I haven't downloaded yours to check the formulas, but these 499% just don't make sense, so something must be inaccurate.
Below is a screen of my own Excel Sheet, below right is the TGT odd calculation. It shows 100%, and I didn't include any "trick" to reduce it. The 100,01 is a rounding error by Excel in the binomial calculation, and the # boxes are related to the binomial calculation aswell, they disappear as soon as I put in at least 1 card in my collection (top left).Looks like this when I put in that I have 2 rare and 10 commons, for example:
(Left: Only considering cards you don't have at all, Right: Considering cards that you don't have at all AND non-legendary cards you have only 1)
(The Avg Dust/Pack calculates the crafting value for cards you still need for a full collection with 2 of each non-legendary and the disenchant value for cards you already have 2 copies or 1 in case of legendaries)
Arena Leaderboard EU - September 2018: #47 (@7.77 Wins Average)
I keep saying this,
there's 5 cards in a pack.
499% simply means, 5 new cards from your first pack with a fresh collection, with a small chance of duplicates.
However I wouldn't mind the code for all that, especially the dust/pack seems very interesting.
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Actually the chance to have 2 same cards in the end is high, if you take into account the nature of the game. I miss 5 rares from the classic set, and 4 of them are Flare and Gadgetzan Auctioneer, because I disenchanted them when they got nerfed and never found them since, haha. That's how I came across the issue. The spreadsheet tells me I have the same chance to find a new card in the classic set and gvg, which isn't true (rares especially influence the percentage a lot).
Dinaverg's right though, without weird situations like mine the data shouldn't favor one or the other set. But still, it's nice to see the actual chance to find a new card in the set. It was very inaccurate before, because consider this:
By some miracle you found exactly 1 copy of each of 100 rares, the chance to find a new one would appear to be half while in fact it would be the exact starting one. Of course there's no chance of that happening, but still, if in the end you're missing 5 different cards, which isn't unlikely, the chance shown would still be very wrong. So it's not a discrepancy of 1%.
Now it's fixed though, so thanks! I'll have to write off all the cards again, copying the missing parts seems like more work...
Ye I had to write them off again also - which isn't so bad because I get to see the discrepancy now for myself, still having the old version.
But you're right, if there's 100 different rares, and you need 2 copies of each, if you happen to find 1 copy of each, when you get 100 rares, you still have a 100% chance to find a new rare in a pack. I'll post the difference in % for fun when I'm done updating my collection -again- :P
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Well, my chances changed from:
23.52% to 42.1%
and
17.78% to 21.54%
So which ever's lower, still remains the lowest, there can be either a big or a small variation though.
I should maybe add that for GvG the lowest percentage, I miss 1 rare card, which I think is where your chances drop dramatically, whereas for Classic I still need 1 common and 14 rares. (Unleash because I dissed it when they nerfed it :D)
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
On the top of the spreadsheet you added a quicklook, where you list every class and below them "Uncommon". Shouldn't that be "Neutral"?
Yes! Thanks! fixed it.
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
There was another error that made the chance to find a new card wrong but now it's fixed.
For those who already copied it, the fix is really easy: just go to the yellow brackets above "CHANCE THAT A CLASSIC PACK HAS A CARD YOU'RE MISSING", and:
in the yellow bracket under "common", when you click it its value at the top must read
=COUNTUNIQUE(E46:E59,E63:E75,E79:E91,E95:E107,E111:E123,E127:E139,E143:E155,E159:E171,E175:E187,E191:E271)/95*357.11
which has two mistakes: E46 should be E47 and the divided number near the end, 95 should be 94.
In the yellow bracket under "rare", change D46 to D47 and 82 to 81.
and in the yellow bracket under "epic", change C46 to C47 and 38 to 37.
This error was only in the classic pack set, everything else is correct.
Thanks!
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Cool! thanks bro!
Missed that one! It's fixed now!
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
Hey milk1, there is a new version up, and it is indeed already updated. I would advice downloading this new one and re-deleting your cards since a few things have changed that made it more accessible including a few bug fixes, name changes and the like (minor tweaks if you will)
You could try to copy and paste the TGT card set into your collection, but you might need to update the formulas - if you know how to, this is certainly a good solution.
The reason I made it more accessible. and moved the total cards to the top is mainly so that in the future it will be a lot easier to simply copy the new expansion into your existing sheet. (e.g. you simply copy everything at the bottom into the bottom of your existing sheet and you should be good to go.) I also updated all formula's so they get their values from the top section, this way I only have to update those numbers and it automatically updates the rest of the sheet - again making it easier to add new content without having people to re-download and stripe their cards off again.
If you use a version for openoffice I suggest you download v2 old, since the new formulas do not work in openoffice, unfortunately.
https://docs.google.com/spreadsheets/d/1GhqNiBv453OozFX3Fts6orc3wH5sfMmmkNPCjM7nfoE/edit?usp=sharing
For excell or others you might try v2 new, it has updated formula's for figuring out which pack is the best pack to buy. These formulas are somewhat more accurate. I haven't tested the new formulas in other programs, so I do not know if they work!
https://docs.google.com/spreadsheets/d/1rZobj5ALRs_7X4tH40Sx1g2h5QexptLifeeobAjorJw/edit?usp=sharing
If you plan on saving a copy on google docs itself, you should definitely get the regular v2.
I hope this helps!
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
The issue is the way it's worded, the number you give makes sense when you describe it that way, but the spreadsheet says it's the chance that a pack has a card that you're missing. The actual chance that a pack has a card that you're missing has to be between 0% and 100%, obviously it's impossible for any probability to be over 100%.
What I think it's actually reporting is 100x the "expected number of new cards per pack". If you don't have anything other than basic cards, you'd expect to get 4.99 new cards in your next pack. I think that's a better way to describe it.
Also, while I'm at it, one small typo correction: Frothing Berserker is spelled "Beserker" in the spreadsheet.
Thanks! fixed the typo.
Worlds "bestest" collectors spreadsheet, download at your leisure:
version 2.1 with a big thanks to Rayman001:
https://docs.google.com/spreadsheets/d/1dX1-sQD3UVNvk5_AfjtIMzaydii4TXDfnlKVvB_Px20/edit#gid=191287612
I can also report that the formulas on the new version don't work in Excel 2013 either.