The Excel RANK.EQ function
returns the statistical rank of a given value, within a supplied array of
values. If there are duplicate values in the list, these are given the same
rank.
The Rank.Eq function is new in
Excel 2010, and so is not available in earlier versions of Excel. However, the
function is simply an updated version of the Rank function, which is available
in earlier versions of Excel.
Syntax
The syntax for the RANK
function in Microsoft Excel is:
=RANK.EQ( number,
array, [order] )
Parameters or Arguments
number
The number to find the rank
for.
array
A range or array of numbers to
use for ranking purposes.
order
An optional argument that
defines whether the supplied ref array should be ordered in ascending or
descending order.
The [order] argument can have
the value 0 or 1, meaning:
0 - use
descending order
1 - use
ascending order
If the [order] argument is
omitted, it will take the default value of 0 (i.e. descending order). Any
non-zero value is treated as the value 1 (i.e. ascending order).
Rank.Eq Function Examples
The following spreadsheet
shows four examples of the Excel Rank.Eq Function, used to calculate the rank
of values within the array {1, 11, 8, 9, 2, 5, 9}.
Formulas:
A
|
B
|
|
1
|
1
|
=RANK.EQ(
5, A1:A7 )
|
2
|
11
|
=RANK.EQ(
5, A1:A7, 1 )
|
3
|
8
|
=RANK.EQ(
9, A1:A7 )
|
4
|
9
|
=RANK.EQ(
8, A1:A7 )
|
5
|
2
|
|
6
|
5
|
|
7
|
9
|
Results:
A
|
B
|
|
1
|
1
|
5
|
2
|
11
|
3
|
3
|
8
|
2
|
4
|
9
|
4
|
5
|
2
|
|
6
|
5
|
|
7
|
9
|
Note that, in the above examples:
► In
cells B1, B3 & B4, the [order] argument is omitted. Therefore, the Rank.Eq
function uses the descending array, 11, 9, 9, 8, 5, 2, 1.
► In
cell B2, the [order] argument is equal to 1. Therefore, the Rank.Eq function
uses the ascending array, 1, 2, 5, 8, 9, 9, 11.
► The
supplied array contains two values equal to 9, which occupy positions 2 and 3
when the array is ranked in descending order. In this case, the Rank.Eq function
returns the rank 2 for the value 9 (see the example in cell B3), and the next
value, 8, has rank 4 (see cell B4).
RANK.EQ & RANK.AVG Functions
The Rank.Eq and Rank.Avg functions
are both new to Excel 2010. The difference between these two functions occurs
when there are duplicates in the list of values. The Rank.Eq function returns
the lower rank, whereas the Rank.Avg function returns the average rank.
For example, in the list of
values 4, 5, 5, 6 (in ascending order), the value '5' occupies the 2nd and 3rd
positions. Therefore, when calculating the rank of the value 5:
-
RANK.EQ returns the rank 2
-
RANK.AVG returns the rank 2.5
Both the Rank.Eq and the
Rank.Avg function return a rank of 4 for the value 6.
Reference:
follow us on http://arivilm.blogspot.in
like
us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment