Smart Average

From MTG Studio

Jump to: navigation, search

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

Polynomial for Percent Deviation.png

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)

Personal tools