In this post, we will detail about the function for calculating the percentile rank of a given number in a ordered dataset. The function to use in Excel for this purpose is PERCENTILERANK(array, x, significance). Quoting Excel help:
Returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set.One important aspect of the calculation mentioned in the Excel help page is the following:
If x does not match one of the values in array, PERCENTRANK interpolates to return the correct percentage rank.It is this interpolation method where CB differs from Excel. First off, just like there is no standard definition of percentile, there is no standard definition of percentile rank either. All the formulas available in the literature yield similar results, but not the exact same. In CB, to calculate the interpolation, we use the 1-based index of the values just below and above x in the sorted version of the original array. We do not know how Excel carries out the calculation, but in our tests, final Excel result can differ substantially from CB result.