[AccessD] Trimmean Function in Excel - How to in Access

Julie,

For some reason posts like this intrigue me...but my results aren't always 
pretty...I think I have what you described.  The trick is getting VBA in 
access to do exactly what the function in excel does...
1.  Count records
2.  multiply by your percent
3.  rounds DOWN to number divisible by 2.
4.  divides by 2
5.  takes this number of records off the top and bottom
6.  averages the rest

for example:  (from the EXCEL help)

TRIMMEAN(array,percent)
Array   is the array or range of values to trim and average.

Percent   is the fractional number of data points to exclude from the 
calculation. For example, if percent = 0.2, 4 points are trimmed from a data 
set of 20 points (20 x 0.2), 2 from the top and 2 from the bottom of the 
set.

Remarks

·If percent < 0 or percent > 1, TRIMMEAN returns the #NUM! error value.
·TRIMMEAN rounds the number of excluded data points down to the nearest 
multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 
points. For symmetry, TRIMMEAN excludes a single value from the top and 
bottom of the data set.

Below is what I came up with to mimic this logic:  I used a table with 3 
fields containing...Record ID, some number, Name(subcategory)...so I could 
get the MEAN for grouped by Name.
**************************************
Dim Per
Per = 0.2
Dim db As Database
Set db = CurrentDb()
    Dim GroupRst As Recordset
    Set GroupRst = db.OpenRecordset("SELECT Table1.Name FROM Table1 GROUP BY 
Table1.Name;", DB_OPEN_DYNASET)
    Dim GroupName As Field
    Set GroupName = GroupRst!Name
    Do Until GroupRst.EOF
        Dim MyRst As Recordset
        Set MyRst = db.OpenRecordset("SELECT Table1.id, Table1.Field1, 
Table1.Name FROM Table1 WHERE (((Table1.Name) = '" & GroupName & "')) ORDER 
BY Table1.Field1;", DB_OPEN_DYNASET)
        Dim List As Field
        Set List = MyRst!Field1
        Dim Counter
        Counter = MyRst.RecordCount
        Dim Trim
        Dim MaxTrim
        Dim Div
        If InStr(1, Int(Counter * Per) / 2, ".") = 0 Then
            Trim = Int(Counter * Per) / 2
            MaxTrim = Counter - Int(Counter * Per) / 2 + 1
            Div = Counter - Int(Counter * Per)
        Else
            Trim = (Int(Counter * Per) - 1) / 2
            MaxTrim = Counter - (Int(Counter * Per) - 1) / 2 + 1
            Div = Counter - Int(Counter * Per) + 1
        End If
        Dim ListSum
        ListSum = 0
        Dim Num
        Num = 0
        Do Until MyRst.EOF
        Num = Num + 1
        If Num > Trim And Num < MaxTrim Then
            ListSum = ListSum + List
        Else
        ListSum = ListSum
        End If
        MyRst.MoveNext
        Loop
        MsgBox "The TRIMMEAN for " & GroupName & "is " & ListSum / Div
    GroupRst.MoveNext
    Loop
*******************************************

I hope this helps.  Please let me know if you have any questions...or I can 
send a sample db offline.

Thanks,

Mark A. Matte