The Excel RANK.AVG function
returns the statistical rank of a given value, within a supplied array of
values. If there are duplicate values in the list, the average rank is
returned.
The Rank.Avg function is new
in Excel 2010, and so is not available in earlier versions of Excel.
Syntax
The syntax for the RANK.AVG
function in Microsoft Excel is:
=RANK.AVG( 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.Avg Function Examples
The following spreadsheet
shows four examples of the Excel Rank.Avg Function, used to calculate the rank
of values within the array {1, 11, 6, 9, 2, 5, 9}.
Formulas:
|
|
A
|
B
|
|
1
|
1
|
=RANK.AVG(
5, A1:A7 )
|
|
2
|
11
|
=RANK.AVG(
5, A1:A7, 1 )
|
|
3
|
8
|
=RANK.AVG(
9, A1:A7 )
|
|
4
|
9
|
=RANK.AVG(
8, A1:A7 )
|
|
5
|
2
|
|
|
6
|
5
|
|
|
7
|
9
|
|
Results:
|
|
A
|
B
|
|
1
|
1
|
5
|
|
2
|
11
|
3
|
|
3
|
8
|
2.5
|
|
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.Avg
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.Avg 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.Avg
function returns the rank 2.5 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