[RESOLVED] Mode and Median function

[exc05]

I want to know how I can create Mode and Median functions with VBA, without using WorksheetFunction.


mode

median

in this form :

Public function median(.....) as ...

median formula

End function

[Spooman]

exc05

You mention VBA. This is the VB6 forum.

If you meant VB6, then this forum should be OK.
Where is your data stored?
,, Excel?
,, Access?
,, Other?

If, however, you did mean VBA, then this thread should be moved.
Where is your data stored?
,, Excel?
,, Access?
,, Other?
Do you want to create a macro, or do you have a button to trigger the function?

EDIT:

BTW, if you are asking what they (ahem) mean, then

median
.. if ODD number of values, the middle one
.. if EVEN number of values, the average if the middle 2 values

mode
.. the value that appears most frequently

Wiki has a good discussion on these statistical measurements.

Spoo

[vb6forever]

Here's median:

Private Function Median(x() As Single, n As Integer) As Single
'this function calculates the median of a vector
 
'Determine Median
    If n Mod 2 = 0 Then
        Median = 0.5 * (x(n / 2) + x((n + 2) / 2))
    Else
        Median = x((n + 1) / 2)
    End If

End Function

[vb6forever]

Here's median:

Private Function Median(x() As Single, n As Integer) As Single
'this function calculates the median of a vector
 
'Determine Median
    If n Mod 2 = 0 Then
        Median = 0.5 * (x(n / 2) + x((n + 2) / 2))
    Else
        Median = x((n + 1) / 2)
    End If

End Function

[Shaggy Hiker]

Thread moved to Office Development, which is the right place for VBA posts.

[westconn1]

i found some information for mode here http://www.vbforums.com/showthread.p...xcel-VB-editor
see post #5

[IkkeEnGij]

a possible solution for mode, based on the counting sort algo (i am a big fan of the counting sort)

have a form with a command button

Private Sub Command1_Click()
    ReDim TheArray(1 To 1000)
    Dim I As Long
    For I = 1 To 1000
        TheArray(I) = Int(Rnd * 100) '0 to 99
    Next
    Doit
    'Cls
    Print ModeString
End Sub

have a module

Public TheArray() As Long
Public ModeString As String

Sub Doit()
    Dim CountArray() As Long
    'find the lowest and highest number in TheArray
    Dim Low As Long
    Dim High As Long
    Dim TheCount As Long
    Dim I As Long
    Low = TheArray(LBound(TheArray))
    High = Low
    For I = LBound(TheArray) To UBound(TheArray)
        If TheArray(I) < Low Then Low = TheArray(I)
        If TheArray(I) > High Then High = TheArray(I)
    Next
    'create the counting sort array
    ReDim CountArray(Low To High)
    For I = LBound(TheArray) To UBound(TheArray)
        CountArray(TheArray(I)) = CountArray(TheArray(I)) + 1
    Next
    'find the highest number in CountArray
    High = CountArray(LBound(CountArray))
    For I = LBound(CountArray) To UBound(CountArray)
        If CountArray(I) > High Then High = CountArray(I)
    Next
        
    For I = LBound(CountArray) To UBound(CountArray)
        Debug.Print I, CountArray(I)
    Next

    'what are the values for high ?
    ModeString = ""
    For I = LBound(CountArray) To UBound(CountArray)
        If CountArray(I) = High Then ModeString = ModeString & I & ","
    Next
    Debug.Print "The Mode = " & ModeString
    Debug.Print "================="
End Sub

[exc05]

i can calculate mode and median simply in excel


but what i want is to customize a function for median and mode with vba editor to calculate mode and median like excel

[westconn1]

customize a function for median and mode

did you check out the mode function i linked to?

median would just need to sort the array of values and return the middle one (or two /2), mode could return an array if there are several numbers with the same count

[exc05]

Thank you Bro, I've solved it