average without low and high numbers 1

[barbola]

I need to average a bunch of numbers but take out the lowest and highest. Someone said they thought this was standard deviation but I tried using stdev and the number was not right.

Any ideas on how to do this?

 

[PHV]

Any chance you could post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barbola]

Oh, all I did in my query was show these columns:

(total of 1148 records)
Avg([Temperature]) which gave me 64.09
Min([Temperature]) was 15.3
Max([Temperature]) was 101.5
StDev([Temperature]) gave me 23.29

If I take out the low and the high, the average is a bit higher, 66.99 and that is taking out all 65 records that have the 15.3 for a low. If I only take out one record at 15.3 and the 101.5, the average is 64.10 (whoopie).

This is what they asked for, so I am assuming I need to take out all the records at the low temp to get the 66.99 as my result.

 

[PHV]

Again, could you please post your actual SQL code ?

 

[barbola]

I don't have any SQL code. It is just your basic Access query where you drag and drop fields into the grid.

But if you insist:

Select min(Temperature), max(Temperature), avg(Temperature),stdev(Temperature) from Data where
[Date] between #5/1/6# and #5/5/6#;

nothin special there.

I just don't know what calculations, functions, formulas whatever I can use to get what I need. I was asking because it doesn't look like stdev is what I need.

 

[PHV]

You may try this (in the SQL view pane):
SELECT Min(Temperature) AS SecondMin, Max(Temperature) AS SecondMax, Avg(Temperature) AS myAvg
FROM Data
WHERE [Date] Between #2006-05-01# And #2006-05-01#
AND Temperature <> (SELECT Min(Temperature) FROM Data WHERE [Date] Between #2006-05-01# And #2006-05-01#)
AND Temperature <> (SELECT Max(Temperature) FROM Data WHERE [Date] Between #2006-05-01# And #2006-05-01#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barbola]

Yes that should help. I'm wondering, do you think what I should do is take all the temperatures that are within the standard deviation of the median? Or would that give me the same result?

IE.
Median temp 75.3
Std Dev = 22.5

So take everything between 52.8 and 97.8?

I know, only the person who wants the report can answer that....but I am wondering, is that kinda what these kinda calculations are kinda for? Do I need to get an eddication on this?

 

[lespaul]

I don't think you're grasping what Standard Deviation is:

[tt]Wikipedia: it is a measure of the average distance of the data values from their mean. If the data points are all close to the mean, then the standard deviation will be low (closer to zero). If many data points are very different from the mean, then the standard deviation is high (further from zero). If all the data values are equal, then the standard deviation will be zero.[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[barbola]

I'll just do what I was told by the person who requested the report. I will take the lowest and highest out (using the code given to me above, thank you), and average the rest, and forget the word Standard Deviation was ever brought into the conversation. I don't think the person requesting the report knew what that meant either. My Stats course was 20 years ago.

thank you.