Re: formula for ranking statistics

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bernie Deitrick (_at_)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 13:40:45 -0400

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

"btcdeb" <anonymous@discussions.microsoft.com> wrote in message
news:911e01c485f8$8335c7e0$a401280a@phx.gbl...
> 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 data***.
>
> The columns I'll need to use to create this standing are
> columns in the data***:
>
> 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.

Quantcast