RANK function

  • Thread starter Thread starter Sheri
  • Start date Start date
S

Sheri

Hello and thanks for the help. I have a spreadsheet with
numbers that I need to rank. The problem is that they are
not in consecutive cells. The RANK function seems to only
work when I can use a range of consecutive cells. Is there
a way to use non-consecutive cells with the RANK function?
 
If your data is in the range A1:A10, try something like:

=COUNTIF($A$1:$A$10,">"&$A1)+1

to get the rank of A1, etc.
 
Sheri said:
Hello and thanks for the help. I have a spreadsheet with
numbers that I need to rank. The problem is that they are
not in consecutive cells. The RANK function seems to only
work when I can use a range of consecutive cells. Is there
a way to use non-consecutive cells with the RANK function?
Click to expand...

Unfortunately, no, and none of the work-alikes would work on multiple area
ranges either. If user-defined functions would be acceptable, here's a
2-stage approach.

First the udf.


Function mkarray(b As Long, ParamArray a() As Variant) As Variant
'Copyright (C) 2001, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
Const MAXLEVEL As Long = 10 'catch too many recursive calls
Static level As Long 'keep track of recursive calls
Dim n As Long, k As Long, i As Long
Dim rv As Variant, x As Variant, y As Variant, z As Variant, t As
Variant

'I'm overloading the error handler to catch no more memory
'conditions, improper base (b) argument, and too many
'recursive calls. Laziness, so sue me.
On Error GoTo ErrorHandler
level = level + 1

If (b <> 0 And b <> 1) Or level > MAXLEVEL Then Err.Raise -1

k = b
n = UBound(a) - LBound(a) + b + 1 'ensures n > b initially
ReDim rv(b To n)

For Each x In a
If IsArray(x) Then
For Each y In x
If IsArray(y) Then
t = mkarray(b, y) 'RECURSE!

If IsError(t) Then Err.Raise -1

For Each z In t
If k = n Then
n = 2 * n
ReDim Preserve rv(b To n)
End If

rv(k) = z
k = k + 1
Next z
Else
If k = n Then
n = 2 * n
ReDim Preserve rv(b To n)
End If

rv(k) = y
k = k + 1
End If
Next y
Else
If k = n Then
n = 2 * n
ReDim Preserve rv(b To n)
End If

rv(k) = x
k = k + 1
End If
Next x

ReDim Preserve rv(b To k - 1)
mkarray = rv

ErrorHandler:
On Error Resume Next

If Err.Number > 0 Then
mkarray = CVErr(xlErrNull)
Err.Clear
End If

level = level - 1
End Function


Then use this in a formula like

=SUMPRODUCT(--(A1<mkarray(1,($A$1:$A$7,$A$14:$A$20))))+1

to get the rank of A1 in ($A$1:$A$7,$A$14:$A$20) ranked from largest to
smallest.
 
Rank works OK for non-consecutive cells,
just remember to set parentheses around
the range:

=RANK(C3, (C3:D12, F4:F19, K5:M5))
 
Back
Top