Find Median value in an array

[Kriskros07]

Hi,

I have an unsorted column of values which range between 0 and 100. Using VBA formula i need to return the median (in terms of sorted position) value. An added complexity is that i only wish to look at values in the range (column) which meet criteria against another column. e.g consider the following table where we wish to find the median value where column 1 = B

A 1
B 4
C 2
B 2
D 9
B 56

There are 3 values for B and the median would therefore be 4.

Any help would be greatly appreciated

 

[Robert Mika]

Hi,

I have an unsorted column of values which range between 0 and 100. Using VBA formula i need to return the median (in terms of sorted position) value. An added complexity is that i only wish to look at values in the range (column) which meet criteria against another column. e.g consider the following table where we wish to find the median value where column 1 = B

A 1
B 4
C 2
B 2
D 9
B 56

There are 3 values for B and the median would therefore be 4.

Any help would be greatly appreciated

=MEDIAN(IF(A2:A6="B",B2:B6))

Confrim Control+Shift+Enter

or vba:

Sub MedianIf()
    Selection.FormulaArray = "=MEDIAN(IF(R2C1:R6C1=""B"",R2C2:R6C2))"
End Sub

 

[Kriskros07]

Thanks for your reponse greatly appreciated!
Can i only use RC formula here or can i use absolute (paramaterised) references e.g. on another sheet?
I need to do further processing on the retured value. Is the array classed as a variant? will i need to convert it to output as a string value as i need to add a % sign