Smart Average
From MTG Studio
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 | [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 | [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 se tof 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
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 (gaddlord@mtgstudio.com)