Conditional Rank

[iperlovsky]

I am trying to use the following function to rank part of an array based on
one condition, but the function returns the rank for the entire array. Any
suggestions?

RANK(IF((Calls!$A$1:Calls!$A$1000=Calls!A100),Calls!B100),Calls!$B$1:$B$1000))

Thanks,

IP

 

[Harlan Grove]

I am trying to use the following function to rank part of an array based on
one condition, but the function returns the rank for the entire array.  Any
suggestions?

RANK(IF((Calls!$A$1:Calls!$A$1000=Calls!A100),Calls!B100),Calls!$B$1:$B$1000))

I'm guessing you want the rank for Calls!B100 within those cells in
Calls!B1:B1000 corresponding to cells in Calls!A1:A1000 equal to Calls!
A100. If Excel would allow it, that'd be

=RANK(Calls!B100,IF(Calls!$A$1:Calls!$A$1000=Calls!A100,Calls!$B$1:$B
$1000))

However, Excel only supports range references as 2nd argument to RANK,
not arrays. You'd need to use something like

=SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)*(Calls!$B$1:$B
$1000>=Calls!B100))

if all cells in Calls!B1:B1000 contain numbers. If some could contain
text, e.g., contain formulas that could evaluate to "", try

=SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)*ISNUMBER(Calls!$B
$1:$B$1000)*(Calls!$B$1:$B$1000>=Calls!B100))

 

[iperlovsky]

Thanks, that is a clean way of getting there. How would I incorporate a
"correction factor" for tied ranks?

 

[Harlan Grove]

Thanks, that is a clean way of getting there. How would I incorporate a
"correction factor" for tied ranks?

....

If you don't need to worry about being sued for arbitrary ordering of
ties, use their original entry order. If all cells in Calls!B1:B1000
contain numbers, try

=SUMPRODUCT((Calls!$A$1:Calls!$A$1000=Calls!A100)
*(Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9>=Calls!B100-ROW(Calls!
B100)/1E9))

Adjust the 1E9 (Excel converts this to 1000000000) figure as
necessary. It should be large enough so that the row correction term
is smaller in absolute value than MIN(Calls!$B$1:$B$1000) but not so
large that it would be discarded due to Excel's 15 decimal digit
precision. For example, if 1000000000 were one of the values in Calls!
B1:B1000 and you try to add 1/1000000000 = 0.000000001 to it, the
value would remain 1000000000 since 1000000000.000000001 would require
19 decimal digits.

If there could be nonnumeric values in Calls!B1:B1000, you'll need to
use an array formula like

=SUM((Calls!$A$1:Calls!$A$1000=Calls!A100)*IF(ISNUMBER(Calls!$B$1:$B
$1000),
Calls!$B$1:$B$1000-ROW(Calls!$B$1:$B$1000)/1E9>=Calls!B100-ROW(Calls!
B100)/1E9))

 

[iperlovsky]

That works as expected. I had to use the array formula because the data set
contained non-numeric blank cells. Thanks, it really solved my rank issue.