The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can determine the rank of largest (i.e. top sales) values as well as smallest (fastest time) values, which is controlled with the optional order argument.
Syntax
The syntax for the RANK
function in Microsoft Excel is:
=RANK( 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 Function Examples
The following spreadsheet
shows four examples of the Excel Rank Function used to calculate the rank of
values within the simple set {1, 11, 6, 9, 2, 5, 9}.
Formulas:
A
|
B
|
|
1
|
1
|
=RANK( 5, A1:A7 )
|
2
|
11
|
=RANK( 5, A1:A7, 1 )
|
3
|
8
|
=RANK( 9, A1:A7 )
|
4
|
9
|
=RANK( 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
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 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 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).
Reference:
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment