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. 1
Function Medianif(rcond As Range, _
2
scond As String, _
3
rmedian As Range) As Double
4
'Medianif calculates the median of all values in range rmedian
5
'if their corresponding values in range rcond are equal to
6
'comparison value scond.
7
'Ranges rcond and rmedian have to be of same size. They can be
8
'multi-range (entered as named ranges, for example) but then
9
'all corresponding areas have to have identical sizes, too.
10
'Reverse(moc.liborplus.www) V0.1 25-May-2008
11
Dim i As Long, j As Long
12
Dim b As Boolean
13
Dim rRes As Range
14
15
b = False
16
For i = 1 To rcond.Areas.Count
17
For j = 1 To rcond.Areas(i).Count
18
If rcond.Areas(i)(j).Value = scond Then
19
If b Then
20
Set rRes = Application.Union(rRes, rmedian.Areas(i)(j))
21
Else
22
Set rRes = rmedian.Areas(i)(j)
23
b = True
24
End If
25
End If
26
Next j
27
Next i
28
29
If b Then
30
Medianif = Application.WorksheetFunction.Median(rRes)
31
Else
32
Medianif = CVErr(xlErrNum)
33
End If
34
35
End Function
Copied!