formula for ranking statistics

B

btcdeb

I've been asked to provide a chart that will show where
each state stands compared to all others. This
information will be pulled from two columns, I'm assuming
in the datasheet.

The columns I'll need to use to create this standing are
columns in the datasheet:

K G
office visits Collections

The would like to see a ranking on this information by
state which is in column c.

Any assitance with a possible pivot table or what would be
the best way to rank by state and clinic the reimbusement
rate, will be appreciated.
 
B

Bernie Deitrick

btcdeb,

You need to use a User-Defined-Function (the code is below) which will
provide a ranking based on two columns rather than one. Copy the code,
paste it into a codemodule in your workbook, then use the function like this
to rank office visits:

=RankIf(K2,$K$2:$K$100,$C$2:$C$100,C2, False)

Of course, change the 100 to match your actual rows of data. Then copy the
formula down the column to get you complete list of rankings. Then you could
filter you list by state to see the relative rankings for that state.

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
 
Top