Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK doesn't work . . . I gotta know why
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK doesn't work . . . I gotta know why
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. "andy62" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK doesn't work . . . I gotta know why
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 -- Biff Microsoft Excel MVP "Sheeloo" <Click above to get my email id wrote in message ... 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. "andy62" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK doesn't work . . . I gotta know why
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" wrote: 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 -- Biff Microsoft Excel MVP "Sheeloo" <Click above to get my email id wrote in message ... 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. "andy62" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK doesn't work . . . I gotta know why
=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. -- Biff Microsoft Excel MVP "Sheeloo" <Click above to get my email id wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "Sheeloo" <Click above to get my email id wrote in message ... 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. "andy62" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Why doesn't RANK(1,{2,1}) work? | Excel Worksheet Functions | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |