Compute 95th percentile - alternative

[redhawk87]

I was wanting to figure out the 95th percentile of a set of unordered data. I dont want the statistical value but the actual data value at the 95th spot. For instance, if I have 100 data points (lets say A1:A100) and they were ordered, then the 95th percentile I would be interested in would be whatever is in cell 95. I know if the data was ordered, this would be easy. I could figure out the address by just doing a simple calculation: FLOOR(total_number_of_elements * 0.95). I could then just look in that particular box, say A95. The total Number of elements is not known before hand and the exact location of data is not known either. For instance, here is my formula for calculating the MAX:

=MAX(IF(C1:C120=51,IF(I1:I120="1min",J1:J120)))

The data is contained between J1 and J120 but I only want to find the max or 95th on a certain subset of the data. For 95th, I want to find the "95th" element in this same set. I do not want to use the PERCENTILE function because that gives me the statistical percentile number which I am not interested in.

[vandan_tanna]

for 95% percentile value of data in column J you could do

=SMALL(J:J,CEILING(0.95*COUNTA(J:J),1))

[redhawk87]

Thanks! I actually ended up using the LARGE command but SMALL would work too. I ended up finding those functions shortly after posting, sorry.