Prev: Why Won't Variance & Standard Deviation Work
Next: User Defined Query Function Not Working with Multiple Criteria
From: pat67 on 7 Apr 2010 10:58 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 7 Apr 2010 11:12 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 7 Apr 2010 15:35
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. |