I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

MedianIf

Example: [Newsgroup Microsoft.Public.Excel.Programming 8-Mar-2009 22:14] Well, I have a list of data from an experiment I did which includes subjects, different stimuli and reaction time to the stimuli (the stimuli comes in numbers). Each subject had a number of stimuli, and he had to repeat the same thing 10 times. for example: subject A had 100 stimuli, from which 10 stimuli where number 1, another 10 where number 2 and so on... The list of the data is a big array that includes the following:
Column A - the subject's name (in a coded form), Column B is the stimulous number, and Column C is the reaction time to the stimulous. I'm searching for a formula (i don't care if the solution is in a form of a formula or VBA), that would return the median of the reaction time to a certain stimuli for a specific subject. Something in the form of 'if Subject = A' and 'Stimuli = 1" then return the median of the reaction times.

MedianIf_Example

Function Medianif(rcond As Range, _
    scond As String, _
    rmedian As Range) As Double
'Medianif calculates the median of all values in range rmedian
'if their corresponding values in range rcond are equal to
'comparison value scond.
'Ranges rcond and rmedian have to be of same size. They can be
'multi-range (entered as named ranges, for example) but then
'all corresponding areas have to have identical sizes, too.
'Reverse(moc.liborplus.www) V0.1 25-May-2008
Dim i As Long, j As Long
Dim b As Boolean
Dim rRes As Range

b = False
For i = 1 To rcond.Areas.Count
    For j = 1 To rcond.Areas(i).Count
        If rcond.Areas(i)(j).Value = scond Then
            If b Then
                Set rRes = Application.Union(rRes, rmedian.Areas(i)(j))
            Else
                Set rRes = rmedian.Areas(i)(j)
                b = True
            End If
        End If
    Next j
Next i

If b Then
    Medianif = Application.WorksheetFunction.Median(rRes)
Else
    Medianif = CVErr(xlErrNum)
End If

End Function

Pfreq you can find here.

Sulprobil   Get it done   Contact   Disclaimer   Download