Conditional Rank (or rather, Conditional Range)

[downwitch]

I'm looking for a way to grab a rank in a single column where the set
of values ranked from--the second argument in the RANK wks function--
is conditional. Say I had this data

Name Height Age Rank
Bill Short 55 1
Mary Short 45 2
Tom Tall 16 3
Rufus Short 21 3
Henrietta Tall 39 1
Henry Tall 38 2

I want to be able to do this in a single-column, array-type formula:

IF(Height="Short",RankAgeAmongShortPeople,RankAgeAmongTallPeople)

Is this possible, or do I have to use several columns to get there?
(Chip's excellent page on ranking does not seem to cover this to me
http://www.cpearson.com/excel/rank.htm)

Thanks in advance.

 

[Guest]

With your posted data list in A1:C7
Name Height Age Rank
Bill Short 55
Mary Short 45
Tom Tall 16
Rufus Short 21
Henrietta Tall 39
Henry Tall 38

Try this:
D2: =SUMPRODUCT(--(C2<($B$2:$B$7=B2)*$C$2:$C$7))+1
Copy that formula down through D7

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[downwitch]

Yes, this works. I'm trying to understand it a little better,
though... I can see how it verifies that the height is in the
appropriate group, and then evaluates the age in the C-column range
via array position, but why the less than sign in C2<B range? What
does the "--" do exactly? Why does it evaluate to 0 for first place
(necessitating the 1)?

And what if I wanted to reverse the rank?

Sorry for the detail questions, but I have to apply this to some much
more complex criteria, and my hunting hasn't dug up a good explanatory
reference on sumproduct, array, and rank thus far.

Thanks for the help.

 

[Guest]

Hi, DW

Regarding:
D2: =SUMPRODUCT(--(C2<($B$2:$B$7=B2)*$C$2:$C$7))+1

This section: ($B$2:$B$7=B2) determines which Height categories to include
in the calculation. It returns 1 for each $B$2:$B$7 cell that matches B2.
Non-matches equate to zero. In the posted data, that section returns this
array {1,1,0,1,0,0}

(Technically, it returns boolean values (TRUE/FALSE). but when arithmetic
operations are applied to TRUE/FALSE values in Excel, they are converted to 1
and 0, respectively)

That array is multiplied times the Ages in $C$2:$C$7
So....{1,1,0,1,0,0} x {55,45,16,21,39,38}
={1,1,0,1,0,0} x {55,45,16,21,39,38}
={1x55,1x45,0x16,1x21,0x39,0x38}
={55,45,0,21,0,0}

The Age in C2 is then compared to each value in that array. Each age
greater than C2 returns 1, all others return 0.

The final array summed by SUMPRODUCT is
={0,0,0,0,0,0}
Totalling 0 (indicating that NO values are smaller than C2). 1 is added to
adjust the value.

The formula in D3 works with this net array
={1,0,0,0,0,0}
Which totals to 1......plus 1 is a rank of 2

Does that help?

(Time pressure is preventing me from addressing your Reverse Rank question
right now...sorry)

***********
Regards,
Ron

XL2002, WinXP

 

[downwitch]

Yes, that's a very clear and concise explanation, thank you, and I've
been puzzling through the way the function works in the meantime
myself.

A couple other helpful links on the subject, for others who might
happen along:

thorough exp. of SUMPRODUCT here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

good discussion of double unary ("--") use in sumproduct:
http://www.eggheadcafe.com/software/aspnet/29685974/understanding-the-objecti.aspx
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Still chipping away at the reverse (some would say inverse) rank, any
other pointers appreciated.

 

[downwitch]

OK here's a reverse/inverse rank formula on the same data:

=SUMPRODUCT(--($B$2:$B$7=$B2);--($C2>$C$2:$C$7))+1

Hard to read, but if col B becomes a named range called Heights, and
col C becomes a named range called Ages, it's clearer:

=SUMPRODUCT(--($B2=Heights);--($C2>Ages))+1

It's very close to Ron's formula upthread, so his explanation still
applies. You can't just reverse the sign in his formula however,
because then every instance gets counted without using the condition,
because his formula multiplies the conditions together. So the first
array argument to filter by height limits the resultset, then the
second just counts each instance of the current-row age exceeding the
minimum age.

It's probably easier for people who think in matrices more than I do
to follow SUMPRODUCT, but when it returns to English I do all
right .

Thanks again for the help.

 

[downwitch]

Oh sorry, that ";" should be a "," if you're not on a French OS...

=SUMPRODUCT(--($B$2:$B$7=$B2),--($C2>$C$2:$C$7))+1
=SUMPRODUCT(--($B2=Heights),--($C2>Ages))+1