Hi -

I have a large dataset with a column (call it column A) that calculates a certain ratio for different financial securities. I am now trying to divide the numbers up into deciles and calculate the median of the figures within each decile. As a second step I am trying to calculate the median of a different column (call it column B) corresponding to the deciles determined from column A.

The Array function I build looks like this:

Step 1:
{=MEDIAN(IF(($A$7:$A$778>PERCENTILE($A$7:$A$778,10%))*($A$7:$A$778<=PERCENTILE($A$7:$A$778,20%)),$A$7:$A$778))}

The problem is that some of the numbers in column A are exactly the same. The formula above determines whether each data point belongs into the 2nd decile based on the second decile cut-off value. Since I have multiple values that exactly match the cut-off value, I average more numbers in decile 1 than in decile 2 for example.

However what I want is to simply divide the data set up into 10 different buckets based on their rank and then average within each bucket. So decile 1 might be (0,1,2,2,2,4) and decile 2 might be (4,4,5,6,7,8) such that each decile has the same amount of data points.

I tried this: {=MEDIAN(IF(PERCENTRANK($A$7:$A$778,$A$7:$A$778)<10%,$A$7:$A$778))}

but percentrank or rank does not seem to work in a array function?

Any help would be highly appreciated.