database function (e.g., =DMAX()) for median, mode, etc. 
Author Message
 database function (e.g., =DMAX()) for median, mode, etc.

Someone asked if there were a way to mimic Excel's database functions
(DMAX, DMIN, DAVERAGE, DCOUNT, etc.) to return the median and mode of
records meeting various criteria. I wrote a partial solution, but can't
find the original post. Here it is if you're out there. The first
procedure just demonstrates the second.

Sub testExample()
    'Returns kurtosis of named database on active sheet in cell $A$1.
    With ActiveSheet
        .Range("$A$1").Value = DCUSTOM(.Range("database"), .Range
("field"), .Range("criteria"), 4)
    End With
End Sub

Function DCUSTOM(ByRef dBase As Range, ByRef fRng As Range, _
    ByRef cRng As Range, ByVal StatType As Integer) As Single
    'Returns various statistics on a database.
    'Works like =DCOUNT(), but has a fourth argument
    'designating type of statistic:
    '   1 = median
    '   2 = mode
    '   3 = skewness
    '   4 = kurtosis
    'Because the function changes cell values (a no-no),
    'this function can only be called via macro. Calling it
    'as a spreadsheet function will generate a #NAME? error.

    'To make this spreadsheet compatible, you'll need to
    'reinvent the DCOUNT function as a macro and then
    'skip the variable cell (vCell) modification.

    Dim vCol As Integer
    Dim recordKount As Long, _
        i As Long, _
        currKount As Long, _
        oldKount As Long, _
        j As Long
    Dim Kount As Long
    Dim wSht As Worksheet
    Dim vCell As Range, _
        vRng As Range, _
        rng As Range
    Dim testVal As Single, _
        dbValues() As Single, _
        prevVal As Single

    Set wSht = dBase.Parent
    With wSht
        'Locate variable cell
        With cRng
            Set vCell = .Find(what:=fRng.Value, _
                LookIn:=xlFormulas, lookat:=xlWhole).Offset(1, 0)
        End With
        'Find associated column in database.
        Set rng = .Range(.Cells(dBase.Row, dBase.Column), _
            .Cells(dBase.Row, dBase.Column + dBase.Columns.Count - 1))
        vCol = rng.Find(what:=fRng.Value, _
            LookIn:=xlValues, lookat:=xlWhole).Column
        Set rng = Nothing
        Set vRng = .Range(.Cells(dBase.Row + 1, vCol), _
            .Cells(dBase.Rows.Count + dBase.Row - 1, vCol))
        'Count records matching basic criteria.
        vCell.Value = ""
        recordKount = vRng.Rows.Count
        Kount = 0
        ReDim dbValues(1 To 1)
        oldKount = 0
        prevVal = -999999999
        'Loop through records.
        For i = 1 To recordKount
            'Find i-th largest value in database.
            testVal = Application.Small(vRng, i)
            'Test unique values only.
            If testVal > prevVal Then
                'Modify the variable cell entry.
                vCell.Value = testVal
                'Count records matching value.
                currKount = Application.DCount(dBase, fRng, cRng)
                If currKount > 0 Then
                    'Cumulative count.
                    Kount = Kount + currKount
                    ReDim Preserve dbValues(1 To Kount)
                    For j = oldKount + 1 To Kount
                        dbValues(j) = testVal
                    Next j
                    oldKount = Kount
                End If
                prevVal = testVal
            End If
        Next i
        'Return appropriate statistic.
        Select Case StatType
        Case 1
            DCUSTOM = Application.Median(dbValues)
        Case 2
            DCUSTOM = Application.Mode(dbValues)
        Case 3
            DCUSTOM = Application.Skew(dbValues)
        Case 4
            DCUSTOM = Application.Kurt(dbValues)
        End Select
        ReDim dbValues(0)
        Set dBase = Nothing
        Set fRng = Nothing
        Set cRng = Nothing
    End With
End Function

Posted via CNET

Wed, 18 Jun 1902 08:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Using Excel Mode and Median in a custom Access Function

2. MODE and MEDIAN functions?

3. Database functions: MEDIAN

4. Algorithm to calculate Median and Mode

5. calculation of mode and median

6. Mode and Median in Access

7. Calculating Mode & Median

8. Mean, Median, Mode

9. Mode, Mean and Median

10. Mode and Median domain calculation

Powered by phpBB® Forum Software © phpBB Group