Can I rank my data???

[Derrich Rodriguez]

In my spreadsheet, I figure a final score for this
particular analysis. I want to create a column that will
insert a number that directly realtes to the rank of that
particular row. For example,

Column Z has a set of numbers in it, and I want Column AA
to show the rank for the numbers in Column Z:
Z AA
4.5 2
3.1 3
6.7 1

I also wish to create an additional column that looks at
two parameters (Columns) to determine rank. For example,
Column A specifies a group, again Column Z is a
score...therefore Column AB should show the rank using
data from Columns A and Z:

A Z AB
Small 4.3 1
Medium 5.6 1
Large 9.8 2
Small 4.2 2
Large 10.1 1
Large 8.9 3

Thanks for taking the time read my long request.

[Derrich]

I finally figured out how to use the RANK function
successfully. HOWEVER, I am still struggling to figure out
how to rank using two paramaters (columns) as in the 2nd
example I provided.

>.
>

[Daniel.M]

Hi Derrich,

Q1:
In AA1: =RANK(Z1,$Z$1:$Z$3)
Copy to AA3

Q2:
in AB1:
=1+SUMPRODUCT(($A$1:$A$6=A1)*($Z$1:$Z$6>Z1))
Copy to AB6

Adapt to your ranges!

Regards,

Daniel M.

[Derrich R.]

Thaks, Daniel...makes sense. My "sumproduct" attempts
failed. I think this will definitely work.

>.
>

[Bernie Deitrick]

Derrich,

Below is a user defined function that does what you want. In your case, it would be used like (if your data extends 100 rows):

=RankIf(Z1,$Z$1:$Z$100,$A$1:$A$100,A1, False)

Copy it down to match, and you're done.

To use the function, copy it from here and paste it into a module in your workbook.

HTH,
Bernie
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 assign smaller values lower rank number, 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

"Derrich" <derrich....@frostbank.com> wrote in message news:01cf01c3367a$8c4b4900$a101...@phx.gbl...

[Derrich R]

Thanks. I'll give it a whirl.

>.
>

[Harlan Grove]

"Derrich Rodriguez" wrote...
..

>I also wish to create an additional column that looks at
>two parameters (Columns) to determine rank. For example,
>Column A specifies a group, again Column Z is a
>score...therefore Column AB should show the rank using
>data from Columns A and Z:
>
>A Z AB
>Small 4.3 1
>Medium 5.6 1
>Large 9.8 2
>Small 4.2 2
>Large 10.1 1
>Large 8.9 3

..

You don't need a user-defined function to do this. If you can accept Excel's
approach to ranking ties, and if the data above were in rows 1 to 6 in columns A
and Z, enter this formula in cell AB1

=SUMPRODUCT(($A$1:$A$6=A1)*($Z$1:$Z$6>Z1))+1

and fill AB1 down into AB2:AB6.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.