Rank for range with two criterion - hard

[Mark]

Hi,

I'd like show my problem with rank by two criterion in
table below:

Name Feature Data Rank_of_Name_and_Feature
A 1 2.5 3
A 1 3.5 2
A 0 6.3 2
A 1 6.5 1
A 0 6.8 1
A 1 1.2 4
etc...

How create rank (diminishing count Data) for the same Name
(col1) and Feature (col2)?

My data can't be sorted!!
Any help in VBA or function (excel 2k) will be appreciated

Regards
Mark

 

[Guest]

Hi
try:
=SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7>=C2))

Note: may not give you the expected results if you have ties in your data

 

[Mark]

Hi Frank,
What target do you want achieve by function sumproduct?
I'd like calculate position in column
Rank_of_Name_and_Feature count column Data for e.g. Name A
and Feature d and separately for Name A and Feature u.

I a bit changed my table.:

Name Feature Data Rank_of_Name_and_Feature
A d 2.5 3 (what function ?)
A d 3.5 2 (what function ?)
A u 6.3 2 (what function ?)
A d 6.5 1 (what function ?)
A u 6.8 1 (what function ?)
A d 1.2 4 (what function ?)
etc...

Function RANK operate only for range, not for range take
into account Name and Feature.

Is there any solution for VBA or function?

Best Regards
Mark

 

[Chris Rae]

It's not a perfect solution as it involves hard-coding a maximum value for
your data, but assuming the below are headerless columns A to C, the answer
in column D could be

{=MATCH(C1,SMALL(IF(A$1:A$6=A1,IF(B$1:B$6=B1,C$1:C$6,99)),ROW(A$1:A$6)),FALSE)}

This basically sorts the numbers manually, after stripping out any that
weren't matching the Name or Feature of that particular row. The ROW() part
is just to get a (1,2,3...} array that we can pass to SMALL() to get a
sorted list. You need a separate entry of the array formula for each line.

It's a little on the calculation-intensive side, but at least doesn't
involve any VBA.

Hope that helps,

Chris