From: pat67 on
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?
From: vanderghast on


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


"pat67" <pbuscio(a)comcast.net> wrote in message
news:628a69bf-a5a2-44aa-ada6-fbb90e8e0cb1(a)i16g2000vbm.googlegroups.com...
> 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?

From: pat67 on
On Apr 7, 11:12 am, "vanderghast" <vanderghast(a)com> wrote:
> 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
>
> "pat67" <pbus...(a)comcast.net> wrote in message
>
> news:628a69bf-a5a2-44aa-ada6-fbb90e8e0cb1(a)i16g2000vbm.googlegroups.com...
>
>
>
> > 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?- Hide quoted text -
>
> - Show quoted text -

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