In Access Help for SQL it states
You can also use the PERCENT reserved word to return a
certain
percentage of records that fall at the top or the bottom of
a range
specified by an ORDER BY clause. Suppose that, instead of
the top 25
students, you want the bottom 10 percent of the class:
SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;
I am sure with some more sophisticated help from others on
the list have
a better suggestion than what is to follow. If I had to
have it in the
next half hour I would delete the top 2.5% and then get the
next 97.5%
of the remaining file.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
On Behalf Of MEDIA/Bowman, John C. "Chris"
Sent: Friday, July 28, 2006 3:36 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: how to exclude highs and lows in a group of
values
Just a thought, but couldn't you use the PERCENTILE or
PERCENTILERANK
function to provide a rudimentary filtering option? You
could then make
a query in Access using the between function. That would
throw out the
outliers.
________________________________
From: MS Excel General Q & A List on behalf of Phil
Rosenkranz
Sent: Fri 7/28/2006 2:44 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: how to exclude highs and lows in a group of values
Please excuse the cross-post to both the Excel and Access
groups.
I have query, which returns a large group of start dates and
end dates
and I've calculate the number of days the job took. I need
a way to
analyze this group and only use the middle 95%. That is to
exclude the
extra high and extra low values. I also want to exclude
null values.
Some of these dates show the same start date and end date,
instead of
reporting a value of 0 for this record, I would like it to
report 1.
I'm not sure of how to begin this. I prefer to use Access,
because it
will be entered into an Access report, but can also use
Excel if it's
the best tool for this job.
Any help would be great!
Thanks in advance
Phillip Rosenkranz
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|