averaging specific data

P

pat67

Hi, I want to average data but I need to exclude the highest value. in
other words I have 18,20, 25,32, 10. I want to average 10, 18, 20 and
25 eliminating 32. Any ideas?
 
V

vanderghast

SELECT AVG(myfield)
FROM somewhere
WHERE myField < DMAX("myField", "somewhere")


Someone may replace DMAX with a sub query.

Note that if the max, say 32, occurs twice or more, all its occurrence are
removed. If you only want to remove one occurrence, keeping the other
instances, use:


SELECT (SUM(myfield) - MAX(myfield) ) / ( COUNT(*) - 1)
FROM somewhere



Vanderghast, Access MVP
 
P

pat67

SELECT AVG(myfield)
FROM somewhere
WHERE myField < DMAX("myField", "somewhere")

Someone may replace DMAX with a sub query.

Note that if the max, say 32, occurs twice or more, all its occurrence are
removed. If you only want to remove one occurrence, keeping the other
instances, use:

SELECT (SUM(myfield) - MAX(myfield) ) / ( COUNT(*) - 1)
FROM somewhere

Vanderghast, Access MVP






- Show quoted text -

Thanks. I actually came up with the same exact query you have second.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top