Extreme figures skewing Avg figure

[ColinEssex]

Hi All

I've been puzzling over this on and off for a day and have searched the forum with no success.

I have a query that looks at average lengths of stay in a hospital for stroke patients. There are an odd 3 or 4 with extra long lengths of stay which are out of the norm and are skewing the average.

What would be the best way to automatically trim these extremes so that they are not included, bearing in mind that the high figures will be changing as time goes on.

Its not absolutely desperate but it would be nice to have a "with trim" and "without trim figure"

Thanks in anticipation for any help

Col

 

[Rich]

Wouldn't a simple Iif work?

 

[ColinEssex]

Hi Rich

Not quite with you ......sorry

Bear in mind that I'm not awake yet

How would you identify the "rogue" figures?

Col

 

[Rich]

I'm sure it's not going to be as simple as this but
Iif(DateDiff("d",[Start],[End])>10,10,DateDiff("d",[Start],[End])) the problem is of course deciding the "normal" average

 

[pono1]

Colin,

Alternatively, you could use the DStDev function to calc the standard deviation, using that as one measure of the data's reliability.

Regards,
Tim

 

[ColinEssex]

I think I see what you're getting at there Rich.

So with the IIf statement, you're saying that if the difference between the admission date and discharge date is >10 (or whatever) then call it 10 else its the proper length of stay.

With stroke patients - they sometimes come in and die after a day or so and others hang on for 300 or 400 days before they die or get discharged.

Perhaps I could try having a parameter in the query where the user types in a number and the query will exclude anything above that number or they can have "all" if they wish.

Thanks Rich

Col

 

[simongallop]

Or use the mode or median figure

 

[ColinEssex]

Thank you for your help all.

Rich's idea prompted me to use the parameter approach [Enter Max LOS you want included] and that'll keep it easy for the user.

I can "pretty" it up using a form and so on.

Thanks for your time.

Col

 

[neileg]

As others have suggested this is a statistics question, rather than a simple Access matter.

The average is always a crude measure when there is a wide distribution of values. The fact that a few values skew the average, does not stop it being the average!

As Harry suggests, the mode or the median may be a more appropriate measure, and have an understood statistical significance. The mode is the most frequently occuring value (the 'most popular value'); the median is the value of the occurance that splits the population exactly in half ( the 'mark of the halfway person').

 

[ColinEssex]

Sorry Neil - I forgot to say that I am also including the Mode and Median as well.

I shall also be adding a graph that'll show it too.

I appreciate your input.

Col