Phil Rosenkranz wrote:
>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.
As a start, here's a UDF that returns the interquartile
range, but it could
easily be modified to find the middle 95% ... just
substitute
0.025 & 0.975 in the appropriate places. Then you'd
know the upper
& lower bounds & could create a query based on those
values. Not
clear whether you're working with numbers or dates, but
this formula
can deal with either. Please feel free to modify or perhaps
incorporate
something like this in a macro:
Function InterQ(Instring As Range) As String
If IsDate(Instring.Cells(1).Value) Then GoTo HandleDates
InterQ = Int(Application.Percentile(Instring, 0.25)) &
"-" &
Int(Application.Percentile(Instring, 0.75))
Exit Function
HandleDates:
InterQ = Format(Application.Percentile(Instring, 0.25),
"mm/dd/yyy")
& "-" &
Format(Application.Percentile(Instring, 0.75),
"mm/dd/yyy")
End Function
------------------------------------------------------------
--------------
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
|