Real Life Problem - Removing Outliers from a large dataset of continuous data

[kthomas9]

Hey there, I started a job recently and found myself working with data that has outliers. They need to be thrown out using microsoft access or microsoft excel. Here's the problem: there are truck drivers entering in their mileage every time they get gas at a pump. Almost all the data turns out to be accurate except most drivers have just 1 or 2 incorrect entries. For example: one truck driver will enter his mileage as 100,000 ; 100,250 ; 100,450 ; 10,600. The 10,600 comes from one occasion where he left out a digit as he manually recorded his mileage. We need this data to analyze the gas milage of these vehicles, but until we can find a way to remove outliers for each unique vehicle type, we cannott work with the data. Here's what I have found so far:
- The data is continuous
- It is symetrical (the outlier could be on the extreme low end or the high end of the data set)
- There shouldn't be more than 1 - 3 outliers for each unique vehicle in the fleet.

Are there any specific methods that will be best for this type of problem? More importantly, can these methods be implemented on Microsft Access or Excell? Thank You!!!

 

[CB]

1) Are you sure want to delete these data points? If you have a reasonable suspicion that the problem is just a missing zero, wouldn't it be better to verify this somehow and just correct the value?

2) If the mileage values tend to be reasonably predictable, and the problem really is restricted to missing or extra zeroes, then you may just need a rule that flags data points that are outside the range of plausible values. You would set the boundaries of this range based on your knowledge of what plausible mileage values are in this context.