Rank Function with array inputs

[Jacophile]

Hi, can someone tell me why this returns a #Value! error?

{=RANK(MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)),IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1),1)}

I just used the even row selector as an example.

MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)) returns 203 and IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0}
when you press F9.

If I change the range to exclude the non-numeric value I get the same error (it shouldn't matter according to the help file as non-numeric values are ignored).

Thanks in advance!

 

[Jacophile]

Hi, I thought I should help a couple of other guys before bumping this.

Can any one help me understand the problem?

 

[shemayisroel]

Hi,

Kind of hard to know what happening without some sample data, could you kindly post some...

The below are some good working examples on the RANK function, hope this may help.

http://www.xldynamic.com/source/xld.RANK.html

&

Search rank http://www.cpearson.com/excel/Download.htm

 

[Jacophile]

Hi, thanks for your interest, the data can pretty much be anything for example the data in F3:F9 is

<table x:str="" style="border-collapse: collapse; width: 108pt;" width="144" border="0" cellpadding="0" cellspacing="0"><col style="width: 54pt;" span="2" width="72"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 54pt;" x:num="" width="72" align="center" height="17">3</td> <td class="xl66" style="border-left: medium none; width: 54pt;" x:num="" width="72">191</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" x:num="">47</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" x:num="">146</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" x:num="">203</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">7</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:bool="FALSE">FALSE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">8</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="border-top: medium none; height: 12.75pt;" x:num="" align="center" height="17">9</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

and E1 is blank, it can be anything other than a number so that the RANK function will ignore it.

{=RANK(MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)),IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1),1)}

and as stated in my original post, the blue part and the red part return what I would expect but the green outer part returns an error
All I am trying to do is find the Rank of the highest number in the virtual array.

And Thanks for the links but they say nothing about RANK in an array formula...

 

[Aladin Akyurek]

What is the expected rank of the highest number in the sample you provided?

 

[Jacophile]

Well, since IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0} and the order argument in the RANK function is non-zero, the rank of the maximum value in the list would be 3.

Bear in mind that this is a dramatically simplified data set for the sake of posting. The actual data is obviously much longer but the principal is the same.

 

[Jacophile]

Sorry, make that 6 counting the ties...

 

[Aladin Akyurek]

Well, since IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0} and the order argument in the RANK function is non-zero, the rank of the maximum value in the list would be 3.

Bear in mind that this is a dramatically simplified data set for the sake of posting. The actual data is obviously much longer but the principal is the same.

What happened to 191? Shouldn't the outcome equal 4?

BTW, RANK() does not operate on array objects.

 

[Jacophile]

What happened to 191? Shouldn't the outcome equal 4?

The formula excludes odd numbered rows and no, its six because of the ties for zero.

BTW, RANK() does not operate on array objects.

That seems to contradict the help which states:

<link href="mkmsitstore:msohlp11.chm::/html/office10.css" type="text/css" rel="stylesheet"><script language="Javascript" src="mkmsitstore:msohlp11.chm::/html/sExpCollapse.js" type="text/javascript"></script><script language="JavaScript" src="mkmsitstore:msohlp11.chm::/html/inline.js" type="text/javascript"></script><link href="mkmsitstore:msohlp11.chm::/html/office10.css" type="text/css" rel="stylesheet"><script language="Javascript" src="mkmsitstore:msohlp11.chm::/html/sExpCollapse.js" type="text/javascript"></script><script language="JavaScript" src="mkmsitstore:msohlp11.chm::/html/inline.js" type="text/javascript"></script>RANK(number,ref,order)
Number is the number whose rank you want to find.
Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

(bold italics are mine)

Is there a general refference somewhere that describes the array behaviour of excel functions? I have been searching for some time but can't find such a thing.

 

[Aladin Akyurek]

The formula excludes odd numbered rows and no, its six because of the ties for zero.

You mean 3, as you had stated previously...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=64 height=17 x:num>191</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num x:fmla="=SUM(IF(MOD(ROW(A1:A7)-ROW(A1),2)=0,IF(ISNUMBER(A1:A7),IF(A1:A7<MAX(A1:A7),1))))+1" x:arrayrange="C1">3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=64 height=17 x:num>47</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=64 height=17 x:num>146</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=64 height=17 x:num>203</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>FALSE</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

H1:

Control+shift+enter, not just enter:

=SUM(IF(MOD(ROW(F3:F9)-ROW(F3),2)=0,IF(ISNUMBER(F3:F9),IF(F3:F9 < MAX(F3:F9),1))))+1
<?XML:NAMESPACE PREFIX = MAX(F3 /><MAX(F3:F9),1))))+1< p>

...

Is there a general refference somewhere that describes the array behaviour of excel functions? I have been searching for some time but can't find such a thing.

Not sure that there is a single place covering the subject/issue, but the ms newsgroup worksheetfunctions should count some relevant postings. There are also some around here, I guess.
</MAX(F3:F9),1))))+1<>