Hello Myra,
The correct code for the UDF is IMHO:
Function MEDIANIF(ByVal rgeCriteria As Range, _
ByVal sCriteria As String, _
ByVal rgeMaxRange As Range) As Single
Dim iconditioncolno As Integer
Dim inumberscolno As Integer
Dim lrowno As Long
Dim lmatch As Long
Dim arsngvalues() As Single
Dim sngmedian As Single
Dim bsorted As Boolean
iconditioncolno = rgeCriteria.Column
inumberscolno = rgeMaxRange.Column
ReDim arsngvalues(rgeCriteria.Rows.Count)
For lrowno = 1 To rgeCriteria.Rows.Count
If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1,
iconditioncolno).Value = sCriteria Then
lmatch = lmatch + 1
arsngvalues(lmatch) = rgeCriteria.Parent.Cells(rgeCriteria.Row
+ lrowno - 1, inumberscolno).Value
End If
Next lrowno
ReDim Preserve arsngvalues(lmatch)
Do
bsorted = True
For lrowno = 2 To lmatch
If arsngvalues(lrowno - 1) > arsngvalues(lrowno) Then
sngmedian = arsngvalues(lrowno - 1)
arsngvalues(lrowno - 1) = arsngvalues(lrowno)
arsngvalues(lrowno) = sngmedian
bsorted = False
End If
Next lrowno
Loop Until bsorted = True
If lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch + 1) / 2)
If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +
arsngvalues(1 + lmatch / 2)) / 2
End Function
If you enter in sheet2:
A1:
1000
B1 (as array formula!):
=MEDIAN(IF(Sheet1!$A$3:$A$520=Sheet2!A1,Sheet1!$B$3:$B$520,""))
C1:
=MEDIAN(Sheet1!B3:B10)
D1:
=medianif(Sheet1!$A$3:$A$520,Sheet2!A1,Sheet1!$B$3:$B$520)
E1 (as array formula!):
=MEDIAN(IF(LEFT(Sheet1!$A$3:$A$520,3)="100",Sheet1!$B$3:$B$520,""))
Then cells B1:E1 should all show the correct result 0.622604106. E1
gives you an example how to calculate a median of 100*. For 10* you can
use LEFT(...,2)="10", for example.
Have fun,
Bernd
10="d"),E1:E10))