WorksheetFunction.Rank_Eq Method
Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Namespace: Microsoft.Office.Interop.Excel
Assembly: Microsoft.Office.Interop.Excel (in Microsoft.Office.Interop.Excel.dll)
Syntax
'Declaration
Function Rank_Eq ( _
Arg1 As Double, _
Arg2 As Range, _
Arg3 As Object _
) As Double
'Usage
Dim instance As WorksheetFunction
Dim Arg1 As Double
Dim Arg2 As Range
Dim Arg3 As Object
Dim returnValue As Double
returnValue = instance.Rank_Eq(Arg1, Arg2, _
Arg3)
double Rank_Eq(
double Arg1,
Range Arg2,
Object Arg3
)
Parameters
Arg1
Type: System.DoubleNumber - The number whose rank you want to find.
Arg2
Type: Microsoft.Office.Interop.Excel.RangeRef - An array of, or a reference to, a list of numbers. Non-numeric values in reference are ignored.
Arg3
Type: System.ObjectOrder - A number that specifies how to rank the number.
Return Value
Type: System.Double
Remarks
If the order is 0 (zero) or omitted, Microsoft Excel ranks the number as if the reference was a list sorted in descending order.
If the order is any non-zero value, Microsoft Excel ranks the number as if the reference was a list sorted in ascending order.
Rank_Eq gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
For some purposes you might want to use a definition of rank that takes ties into account. In the previous example, you would want a revised rank of 5.5 for the number 10. To do this, add the following correction factor to the value returned by Rank_Eq. This correction factor is appropriate both for the case where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).
Correction factor for tied ranks=[COUNT(ref) + 1 – RANK_EQ(number, ref, 0) – RANK_EQ(number, ref, 1)]/2.In the following example, RANK_EQ(A2,A1:A5,1) equals 3. The correction factor is (5 + 1 – 2 – 3)/2 = 0.5 and the revised rank that takes ties into account is 3 + 0.5 = 3.5. If number occurs only once in ref, the correction factor will be 0, since Rank_Eq would not have to be adjusted for a tie.