Statistics and Ebay!

Hi there, I have a problem that I have been mulling over for a few days now, I last did statistics in Uni over 10 years ago, and that was just one unit, and before ebay started.

I collect coin sets from the Australian mint, using ebay's "completed listing" feature, I can find out what the coin sets have been selling for over the 15 days or so and it is easy enough to use Excel to find out the mean price over the last fortnight.

My main problem is the outliers, a few auctions go unnoticed by the public and the sets sell very cheaply with no competition, a few people haven't done their homework and use the "buy it now" option and pay well above the mean.

These all skew the data, I can easily manually remove any obvious inconsistencies and I have used the TRIMMEAN function in excel to cut out the top and bottom values once they have been sorted.

All these methods work and with the small number of listings it probably won't effect the final figure much but I would like to know the most accurate way of automatically cutting out the most obvious outliers to produce a more accurate mean price for a fair auction (one that everyone has an equal opportunity to pay their maximum price in the auction).

I though some way of chosing a middle value, finding out how most of the values differ from that value and from each other and ignoring all values that differ by a larger margin and finaly working out the mean from all the remaining values.

Not sure how to tell that to Excel though, any ideas?


Actually you don't need to trim your data. Use Median() to find the exact middle point of your data.

This value has half the data above and below this value. Quite handy. Another function you can use is quartile(data,then 1,2 or 3). 1 gives you a data point that has 25% below, 75% above, 2 is the median as mentioned before, and 3 gives a value that has 25% of purchases above and 75% below.

how would the median be affected by a number of outliers, eg a couple of people paying more than they should? Would the median rise significantly and skew the data?

I will have a play with the quartile function, not come across that before, it will help me decide how much to bid on auctions that I don't need to win but wouldn't mind getting if the price was right.

Cheers again

Jislizard, That's the whole point of the median, it simply finds the middle data point. So, unless you have a huge number of lows or highs, it is quite resistant to outliers.

i.e. Consider the set S={1,2,3,4,5,6,7,8,9} Here, the median is 5, because it equally divides the set.

Then consider S={-1000,2,3,4,5,6,7,8,90000} The median is still 5, because it equally divides the set. So you can see, if you have a large number of "reasonable" prices to start out with, a few outliers hardly skews the median.