RANK doesn't work . . . I gotta know why

[andy62]

I am using Ragdyer's solution to assign a rank based on score (col BH) among
matching items (col BF):

=SUMPRODUCT(($BF$5:$BF$291=BF8)*(BH8<$BH$5:$BH$291))+1

But what's buggin me is why RANK is malfunctioning for this application:

=RANK(BH5,IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291))

Instead of returning the rank of BH5 among only matching items (when all the
terms in column BF match the current term), I am getting a RANK of the items
as if there were no subarray produced by referencing column BF. The IF
statement seems to be producing an array of values (when BF matches) and
"FALSE" (when BF does not match). If the RANK function does not even see all
those non-matching values, how could it be working (malfunctioning)?

Bueller? . . . Bueller?

TIA

 

[Sheeloo]

RANK function DOES see all those non-matching values....

The array within RANK() consists of matching values and FALSE... as pointed
by you... it gets and array of 287 elements instead of only matching values
as expected by you.

 

[T. Valko]

The short answer is that RANK doesn't handle arrays.

If you properly entered the formula as an array you'd get #VALUE! errors.

If you don't enter the formula as an array and its entered on the same row
as the data you're referencing it'll return a number which may or may not be
correct but the RANK formula doesn't make sense:

=RANK(BH5,IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291))

BF5 does equal BF5 so BH5 is ranked against BH5:BH291

As you copy the formula down the column each instance of the IF logical test
will evaluate as TRUE:

BF6 does equal BF6 so BH6 is ranked against BH5:BH291
BF7 does equal BF7 so BH7 is ranked against BH5:BH291
BF8 does equal BF8 so BH8 is ranked against BH5:BH291
etc
etc

 

[Sheeloo]

Biff,

Pl. correct me if I am wrong... I am not very comfortable with
IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291) type of arguments passed in place of an
array like $BF$5:$BF$291...

What I understood is that
=RANK(BH5,$BF$5:$BF$291)
would find the rank of BH5 in the range ignoring any text values or blanks...

So if the above range contained 10 numbers, 9 greater than one and BH5
contained one RANK will return 10...

Now with
=RANK(BH5,IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291))

IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291) returns an array of values from BH where
BF matches BF5 and FALSE where it does not..

So it is essentially an array of size 287 so RANK will return the position
of BH5 in this array which is different from the result of the first formula..

 

[T. Valko]

=RANK(BH5,IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291))

IF(BF5=$BF$5:$BF$291,$BH$5:$BH$291) returns an array
of values from BH where BF matches BF5 and FALSE where it does not..
So it is essentially an array of size 287 so RANK will return the
position of BH5 in this array which is different from the result
of the first formula..

We first need to establish that the above formula is an array formula. For
it to be properly processed it must be array entered.

Now, with that in mind, yes, the IF function does return the array that you
describe. However, RANK doesn't handle arrays so this causes the result to a
#VALUE! error if the formula is properly entered as an array.

If you normally enter the formula on the same row that is being referenced
the formula will return a number but this number may not be correct
following the logic of the IF function.

This works due to what's called the implict intersection rule. It works like
I described in my other reply. The IF logical test will *always* be TRUE but
since the formula, being an array formula, is not being processed as an
array it bascially works as though the IF function isn't even there.