Monday, April 13, 2015

Simplifying Excel Ranking Functions: RANK(), RANK.AVG(), RANK.EQ(), PERCENTILE(), PERCENTILE.INC(), PERCENTILE.EXC(), QUARTILE(), QUARTILE.INC(), QUARTILE.EXC()

Simplifying Excel Ranking

Functions: RANK(),

RANK.AVG(), RANK.EQ(),

PERCENTILE(),

PERCENTILE.INC(),

PERCENTILE.EXC(),

QUARTILE(), QUARTILE.INC

(), QUARTILE.EXC()

 

Simplifying RANK(), RANK.EQ(), and RANK.AVG()

Ranking functions RANK(), RANK.EQ(), and RANK.AVG() return the rank of a data value within a data set. Prior to Excel 2010 RANK() was the sole ranking function. In Excel 2010 and beyond RANK() will still work but its functionality has been duplicated by a new ranking function RANK.EQ(). In addition, another useful ranking function RANK.AVG() has been added.

The format of each of these functions is as follows:

RANK(value to be ranked, data range, [order])

RANK.EQ(value to be ranked, data range, [order])

RANK.AVG(value to be ranked, data range, [order])

The value to be ranked is a specific number or value is a data set that will be ranked. The data range represents the set of values that will provide the basis for the ranking. The order is an optional value. Setting the order to 0 or leaving it blank will rank data in descending order, i.e., the highest value in the data range will be assigned a rank = 1. Setting the order to 1 will rank data in ascending order, i.e., the lowest value in the data range will be assigned a rank = 1.

The difference between RANK.EQ() and RANK.AVG() is how tied data values within the data set are ranked. RANK.EQ() assigns the lowest rank of the tied values to all of the tied values. RANK.AVG() assigns the average rank of the tied values to all of the tied values.

RANK.AVG() is useful when performing certain nonparametric tests in Excel. Several nonparametric tests require that data be ranked with tied data values being assigned the average rank of the tied values as is done by RANK.AVG(). These nonparametric tests include the Mann-Whitney U Test, the Wilcoxon Signed-Rank Test, and the Friedman Test.

The functionality of the RANK() function is shown as follows. The raw data that is being ranked has already been sorted in order to more easily convey the functionality of the RANK() function.

Ranking Functions in Excel - RANK() Example
(Click On Image To See a Larger Version)

The functionality of the RANK.EQ() function is shown in the following diagram. RANK.EQ() is equivalent to RANK() and should now be used in its place. Note that tied data values are all assigned the lowest rank of the tied values. The raw data that is being ranked has already been sorted in order to more easily convey the functionality of the RANK.EQ() function.

Ranking Functions in Excel - RANK.EQ() Example
(Click On Image To See a Larger Version)

The functionality of the RANK.AVG() function is shown in the following diagram. Note that tied data values are all assigned the average rank of the tied values. There is no equivalent to this function in versions of Excel prior to 2010. The raw data that is being ranked has already been sorted in order to more easily convey the functionality of the RANK.AVG() function.

Ranking Functions in Excel - RANK.AVG() Example
(Click On Image To See a Larger Version)

 

Simplifying PERCENTILE(), PERCENTILE.INC(), and PERCENTILE.EXC()

Percentile functions PERCENTILE(), PERCENTILE.INC(), and PERCENTILE.EXC() return the value at a given percentile in relation to a given data set. For example, given the data set {1, 3, 5, 13, 15} in cells A2:A6, PERCENTILE(A2:A6,0.3) = 3.4. This means that the value 3.4 occupies the 30th percentile of the given data set.

An example of the use of the percentile functions would be to determine which test scores within a group of test score are at or above the 95th percentile of the group. The percentile functions would return that value that would occupy that 95th percentile of the group.

The percentile functions are typically used to establish a threshold of acceptance or failure.

Prior to Excel 2010 PERCENTILE() was the sole percentile function. In Excel 2010 and beyond PERCENTILE() will still work but its functionality has been duplicated by a new percentile function PERCENTILE.INC(). In addition, another percentile function PERCENTILE.EXC() has been added.

The format of each of these functions is as follows:

PERCENTILE(data range, percentile)

PERCENTILE.INC(data range, percentile)

PERCENTILE.EXC(data range, percentile)

The percentile can be any number between 0 and 1.

The value to be ranked is a specific number or value is a data set that will be ranked. The data range represents the set of values that will provide the basis for the ranking. The order is an optional value. Setting the order to 0 or leaving it blank will rank data in descending order, i.e., the highest value in the data range will be assigned a rank = 1. Setting the order to 1 will rank data in ascending order, i.e., the lowest value in the data range will be assigned a rank = 1.

Both PERCENTILE.EXC() and PERCENTILE.INC() and its equivalent PERCENTILE() first rank the values in the given data set from 1 (assigned to the lowest value in the data set) to n (assigned to the highest value in the data set). The rank K is then calculated. The main difference between is the method of calculating K as shown in the following Excel algorithms for these functions. If K is not an integer value, linear interpolation between the nearest values in the data set to determine the value that would occupy the given percentile.

Another difference between the percentile functions is that PERCENTILE.EXC only works if k is between 1/n and 1-1/n, where n is the number of elements in array. PERCENTILE.INC works for any value of k between 0 and 1.

The Excel algorithm for equivalent functions PERCENTILE() and the equivalent PERCENTILE.INC() is as follows:

Ranking Functions in Excel - Excel Algorithm for PRECENTILE() and PERCENTILE.INC()
(Click On Image To See a Larger Version)

The Excel algorithm for PERCENTILE.EXC() is as follows:

Ranking Functions in Excel - Excel Algorithm for PERCENTILE.EXC()
(Click On Image To See a Larger Version)

 

Simplifying PERCENTRANK(), PERCENTRANK.INC(), and PERCENTRANK.EXC()

PERCENTRANK(), PERCENTRANK.INC(), and PERCENTRANK.EXC() are the inverse functions of PERCENTILE(), PERCENTILE.INC(), and PERCENTILE.EXC().

PERCENTRANK(), PERCENTRANK.INC(), and PERCENTRANK.EXC() return the percentile that a given data value has in relation to set of data values.

PERCENTILE(), PERCENTILE.INC(), and PERCENTILE.EXC() return the data value that would occur at a given percentile in relation to a set of data values.

The following diagram shows the relationship of the PERCENTRANK functions to the PERCENTILE functions. In each of these cases the percentile is the 30th percentile.

Ranking_Percentrank_600
(Click On Image To See a Larger Version)

 

Simplifying QUARTILE(), QUARTILE,INC(), and QUARTILE.EXC()

The quartile functions are special cases of the percentile functions. The equivalent functions are as follows:

QUARTILE(Data range, 0) = PERCENTILE(Data range, 0) = MIN(Data range)

QUARTILE(Data range, 1) = PERCENTILE(Data range, .25)

QUARTILE(Data range, 2) = PERCENTILE(Data range, .5) = MEDIAN(Data range)

QUARTILE(Data range, 3) = PERCENTILE(Data range, .75)

QUARTILE(Data range, 4) = PERCENTILE(Data range, 1) = MAX(Data range)

 

QUARTILE.INC(Data range, 0) = PERCENTILE.INC(Data range, 0)

QUARTILE.INC(Data range, 1) = PERCENTILE.INC(Data range, .25)

QUARTILE.INC(Data range, 2) = PERCENTILE.INC(Data range, .5)

QUARTILE.INC(Data range, 3) = PERCENTILE.INC(Data range, .75)

QUARTILE.INC(Data range, 4) = PERCENTILE.INC(Data range, 1)

 

QUARTILE.EXC(Data range, 0) = PERCENTILE.EXC(Data range, 0)

QUARTILE.EXC(Data range, 1) = PERCENTILE.EXC(Data range, .25)

QUARTILE.EXC(Data range, 2) = PERCENTILE.EXC(Data range, .5)

QUARTILE.EXC(Data range, 3) = PERCENTILE.EXC(Data range, .75)

QUARTILE.EXC(Data range, 4) = PERCENTILE.EXC(Data range, 1)

The Excel algorithm for equivalent functions QUARTILE() and the equivalent QUARTILE.INC() is as follows:

Ranking Functions in Excel - Excel Algorithm for QUARTILE() and QUARTILE.INC()
(Click On Image To See a Larger Version)

The Excel algorithm for QUARTILE.EXC() is as follows:

Ranking Functions in Excel - Excel Algorithm for QUARTILE.EXC()
(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!

2 comments:

  1. easy excel rank
    very helpful article
    thanks

    ReplyDelete
  2. Not really much of a formula user in excel because I only know the basics. But with these tips on how to simplifying excel ranking functions, it will be more easy for me to generate reports. Glad I came across this view original source.

    ReplyDelete