Rank within Class

[Carl Cunningham]

I would like to rank the value of class within the
class. The following formula appears correct when I
drill down to the if "array", but rank sees the full
Value column. Why is the array from the if not being past
to the RANK function?

When entered as an array formula (D2), it goes #value.
Can RANK be use in an array formula?

(-100 used to get other data out of the way.)


=RANK(B2,(IF(A2=$A$2:$A$8,$B$2:$B$8,-100)))

Class Value Disired Actual
a 2 1 #VALUE!
b 1.5 2 4
c 3 1 2
a -3 3 6
b -5 3 7
a 0.1 2 5
b 20 1 1

 

[Bernie Deitrick]

Carl,

You need to use a user-defined-function. Copy the code below and follow the
instructions in the comments.

HTH,
Bernie
MS Excel MVP

Function RankIf(RankCell As Range, _
RankRange As Range, _
CritRange As Range, _
Criteria As Variant, _
Optional DescOrder As Boolean = True) As Integer

'=RankIf(A1,$A$1:$A$10,$B$1:$B$10,"A", True)
'
'Where A1 has the number to be ranked,
'A1:A10 has the numbers against which A1 is to be ranked
'B1:B10 have the criteria
'"A" is the criteria (which can also be a Cell reference)
'True means smaller values get lower rank numbers, False would be the
opposite
'
'The function can be copied just like a regular function, and will return 0
'if the number to be ranked doesn't meet the criteria.

Dim i As Integer
Dim myRange As Range
On Error GoTo notRanked
For i = 1 To CritRange.Count
If CritRange(i) = Criteria Then
If myRange Is Nothing Then
Set myRange = RankRange(i)
Else
Set myRange = Union(myRange, RankRange(i))
End If
End If
Next i
RankIf = Application.WorksheetFunction.Rank(RankCell, myRange, DescOrder)
Exit Function
notRanked:
RankIf = 0
End Function

 

[Daniel.M]

Hi,

=1+SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8>B2))

Regards,

Daniel M.

 

[Carl Cunningham]

Much Thanks! I really appreciate clean simple formulas!

 

[hrlngrv - ExcelForums.com]

Bernie Deitrick wrote..

You need to use a user-defined-function. Copy the code below an
follow the instructions in the comments

..

As has already been shown, this doesn't require a udf. RANK could b
replaced by COUNTIF or SUMPRODUCT