Any way to have a dynamic range for ranking, based on criteria?

[S Davis]

Hello,

Is there a way to shift the range for the rank function according to
criteria?

Example: Rank a given number within a range of other cells that are
alike. For example, rank all numbers where the value in column A is
XYZ. The formula will need to be dynamic so that once it hits row 5, it
would now rank everything where the value in column A is ABC.
Essentially, somehow the range needs to examine a list of data and then
identify all circumstances that are alike, and return a cell location.
It may make things easier to know that my data is in sequential order,
ie column A sorted least to greatest. However, if this were to change,
the ranking function must still work.

There will always be 12 instances of the value in column A. So, 12
XYZs, 12 ABCs, etc. Maybe it would be easier to return the first
greatest value of all XYZs, then the second, etc?

Example of how the data may look:
A......B......C(The rank that should be returned)
XYZ...5......3
XYZ...2......2
XYZ...7......4
XYZ...1......1
ABC..1......1
ABC..8......3
ABC..3......2
123...11.....2
123...20.....3
123...5.......1

Thanks for your help, and congratulations if you can understand what Im
trying to say
-S

 

[bplumhoff]

Hello,

Search for Bernie's function Rankif in this newsgroup.

HTH,
Bernd

 

[S Davis]

Thanks, will do!
-S

 

[S Davis]

Sad to report that the information I am trying to rank is spread across
more than one column.

Right now Im using this formula:

=SUMPRODUCT(--(A:A=A4),--(N4<B:B))+1

.... can this be modified to take into account C:C, D, and E:E, so
that A4 is ranked out of the results of all of those columns?

 

[S Davis]

Can this VB code (courtesy of Bernie) be modified to accept 2 or more
ranges?

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

-S

 

[Domenic]

Assuming that for each row you'd like the sum of the row to be ranked
against the sum of each row, try...

=SUMPRODUCT(--($A$2:$A$10=A2),--(SUM(B2:E2)<SUBTOTAL(9,OFFSET($B$2:$E$10,
ROW($B$2:$E$10)-ROW($B$2),0,1))))+1

Note that SUMPRODUCT does not accept whole column references.

Hope this helps!

 

[S Davis]

Thanks Domenic,

I regrettingly rearranged my entire workbook to put those columns in
need of ranking beside each other. Its a little more confusing to look
at, but it now at least works (can set all 4 columns to one range) and
I can summarize on another sheet with simple references (=).

Thanks for all the help everyone.
-S

 

[Domenic]

You're very welcome! I didn't realize that the columns were not next to
each other. It may be possible to rank based on your original layout,
however it may not be as efficient. If you'd like to try it, post back
with the location of the ranges involved.