April 25th, 2005
Computing the rank order of entries in an array of data is needed in many applications.
There are a number of builtin OOo functions that deal with the ranking and ordering of data. We will look at four - RANK, PERCENTRANK, PERCENTILE, and LARGE
In the example below, we see two variants of RANK wherein the entries can be ordered in descening or ascending order. The first two arguments of RANK are the value to be ranked and the array that contains the data. By default, with these two arguments, the entries will be ranked in descending order. The optional third argument - when set to 1 - ranks the entires in ascending order.
The PERCENTRANK function assigns a percentage value to a given value based on a 100% range between the minumum and maximum values of the specified range. If you don’t format these results as a percentage value, the output from PERCENTRANK will lie betwen 0 and 1.
The PERCENTILE function accepts a percentage value as input and returns the percentile of data values in an array. A percentile returns the scale value for the array which goes from the smallest (%age=0) to the largest value (%age=1) of the array. The 50% percentile of an array is the same as the MEAN.
Posted in Function Tips | 1 Comment »
