How can I run Percentile Analysis in Access to remove Outliers

[Guest]

I'd like to rank records (descending) by a numeric field. Once ranked, I want
to split the whole file into 100ths. Then by removng the top 1, 2 or 3% I can
see how this affects the average value and ensure that no outliers with
spuriously high values are included in further analysis.
I've been doing it 'manually' but there must be a better way ????
Thanks

 

[Dave]

I'd like to rank records (descending) by a numeric field. Once ranked, I want
to split the whole file into 100ths. Then by removng the top 1, 2 or 3% I can
see how this affects the average value and ensure that no outliers with
spuriously high values are included in further analysis.
I've been doing it 'manually' but there must be a better way ????
Thanks

i would probably make a form that when opened did a query for the whole
range, ran some vba to find the min/max, statistics, and filled some text
boxes with information for you to read. then have a control or two to
select the cutoff ranges... then a button that ran another query that
selected only records with values >min and <max from what you selected on
the form and populated another set of text boxes with the new statistics.

 

[Tom Wickerath]

Hi Alex,

You may want to calculate a median in addition to an average. If the values are very different,
then you have values that are not distributed in a normal fashion (ie. you have some outliers).
Be careful about just randomly eliminating any data from an analysis.

http://support.microsoft.com/search...&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a

If you are willing to spend the bucks, FMS has a nice add-in that can help you will all kinds of
statistical calculations.

http://www.fmsinc.com/products/statistics/index.html


Tom
________________________________


I'd like to rank records (descending) by a numeric field. Once ranked, I want
to split the whole file into 100ths. Then by removng the top 1, 2 or 3% I can
see how this affects the average value and ensure that no outliers with
spuriously high values are included in further analysis.
I've been doing it 'manually' but there must be a better way ????
Thanks

 

[MatNorth]

A simple straight-forward (no VBA or forms) method:

Think about using a couple of queries to identify the outliers (a query
for the top x% or number of records and the a query for the bottom...).
Work out the totals/sum and counts for these two queries and simple
deduct this from the average calculation for the total (with outliers).