Smart Average - gaddlord/mtg.studio GitHub Wiki
Problem: How to determine the average price for a set of card reprints where the prices have big dispersity and high standard variation coefficient?
Solution: Eliminate extreme values by providing "Allowed % deviation" coefficient and taking the average of the reduced "in-range" subset of prices.
| Param Index | Parameter | Value | Description |
|---|---|---|---|
| P1 | Allowed % deviation | 10 | This is a parameter which can be used to determine the desired precision of the the calculation. The less number of card prices available the higher this % should be. For 1000 Terror cards this can be as low as 1%. For 5 terror prices it should be around 50%. |
| P2 | http://en.wikipedia.org/wiki/Median Median of all values | 4.92 | The Median value (this is a Statistician's term). =MEDIAN(A1:A18) |
| P3 | Calculated min price threshold | 4.428 | This is the MINIMUM price above which the card qualifies for being considered correct in price. =P2 - (P2 * P1 / 100) |
| P4 | Calculated max price threshold | 5.412 | This is the MAXIMUM price below which the card qualifies for being considered correct in price. =P2 + (P2 * P1 / 100) |
| P5 | http://en.wikipedia.org/wiki/Standard_variance Standard Deviation | 7294.41231856017 | Really high due to extreme values like 1.25 and 1000, 31000. Being so we can not use directly average of the whole set of prices. =STDEV(A10:A27) |
| Index | Price in $ | Take into consideration =IF(AND(A10>$P3,A10<$P4),1,0) | Prices left after eliminating extreme values =A1*B1 |
|---|---|---|---|
| A | B | C | |
| 1 | 1.25 | 0 | 0 |
| 2 | 3.57 | 0 | 0 |
| 3 | 3.45 | 0 | 0 |
| 4 | 3.87 | 0 | 0 |
| 5 | 3.95 | 0 | 0 |
| 6 | 3.45 | 0 | 0 |
| 7 | 4.75 | 1 | 4.75 |
| 8 | 4.84 | 1 | 4.84 |
| 9 | 5.75 | 0 | 0 |
| 10 | 5 | 1 | 5 |
| 11 | 5.05 | 1 | 5.05 |
| 12 | 6.12 | 0 | 0 |
| 13 | 66.13 | 0 | 0 |
| 14 | 0 | 0 | 0 |
| 15 | 10.15 | 0 | 0 |
| 16 | 33.5 | 0 | 0 |
| 17 | 1000 | 0 | 0 |
| 18 | 31000 | 0 | 0 |
| . | FINAL SMART AVG PRICE: | This is the average price with extreme values eliminated =AVERAGEIF(C1:C18, ">0") | 4.91 |
If we wish to use the following control point:
| Number of Cards | % Deviation |
|---|---|
| 1 | 50 |
| 50 | 30 |
| 100 | 15 |
| 200 | 5 |
| 500 | 2 |
| 1000 | 1 |
| 2000 | 0.5 |
The % Deviation is calculated using polynomial "% deviation" = 2.553 * "card count" ^2 - 27.561 * "card count" + 74.9
Similarity for card condition is calculated using this mapping table:
| . | Unknown | Sealed | BrandNew | Mint | NearMint | Fine | Good | Fair | Poor | Misprint | Signed | Foreign | Graded |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Unknown | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Sealed | 0 | 20 | 17 | 15 | 10 | 7 | 5 | 2 | 1 | 0 | 0 | 0 | 0 |
| BrandNew | 0 | 17 | 20 | 17 | 15 | 10 | 7 | 5 | 2 | 0 | 0 | 0 | 0 |
| Mint | 0 | 15 | 17 | 20 | 17 | 15 | 10 | 7 | 5 | 0 | 0 | 0 | 0 |
| NearMint | 0 | 10 | 15 | 17 | 20 | 17 | 15 | 10 | 7 | 0 | 0 | 0 | 0 |
| Fine | 0 | 7 | 10 | 15 | 17 | 20 | 17 | 15 | 10 | 0 | 0 | 0 | 0 |
| Good | 0 | 5 | 7 | 10 | 15 | 17 | 20 | 17 | 15 | 0 | 0 | 0 | 0 |
| Fair | 0 | 2 | 5 | 7 | 10 | 15 | 17 | 20 | 17 | 0 | 0 | 0 | 0 |
| Poor | 0 | 1 | 2 | 5 | 7 | 10 | 15 | 17 | 20 | 0 | 0 | 0 | 0 |
| Misprint | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | 0 | 0 | 0 |
| Signed | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 |
| Foreign | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 |
| Graded | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 |
Those materials are for internal use only and should not be used, quoted or republished without the explicit consent of the author ([email protected])