Rank If

[danrosey]

I've got two columns: Names and Values. The Names repeat, but the value are all (mostly unique). I want to rank the values, but only against identical names.


For example, if I had the following set:


Apple 10

Apple 20

Pear 40

Orange 10


The ranking result would return:


2 Apple

1 Apple

1 Pear

1 Orange


I've tried using the following formulas, but it doesn't want to return the rank given the title - only the rank as a part of the whole data set.


=RANK.EQ(IF(B4=$B$4:$B$1102,D4,"N/A"),$D$4:$D$1101,0) and

=SUMPRODUCT(($B$4:$B$1048576=B4)*(D4>$D$4:$D$1101))+1

Where Column B contains Names, and Column D contains values.


Any thoughts?

 

[Luke M]

How about something like this:

=SUMPRODUCT(($B$4:$B$1102=B4)*($D$4:$D$1102>=D4))-SUMPRODUCT((B4:B$1102=B4)*(D4:D$1102=D4))+1

Everything after the - symbol is to handle ties.


Do note that array sizes need to be the same. In both of your formulas, I noticed that you have different sized arrays, and that will cause problems.

 

[sdsurzh]

Hi Danrosey,


Adding to the Luke Excel Ninja formula as per your request column A value should be added to the forumla.


=SUMPRODUCT(($B$4:$B$1102=B4)*($D$4:$D$1102>=D4))-SUMPRODUCT((B4:B$1102=B4)*(D4:D$1102=D4))+1&" "&B4


Thanks,

Suresh Kumar S

 

[danrosey]

Luke, Suresh - thnaks fr the notes.


Rank is working well except in the case of a tie.


=SUMPRODUCT(($C$4:$C$1048576=C4)*($E$4:$E$1048576>E4))-SUMPRODUCT((C4:$C$1048576=C4)*(E4:$E$1048576=E4))+1


I've got a tie value that is resulting like so:


Rank, Name, Value

0 Apple 10

1 Apple 8

2 Apple 7

2 Apple 6

3 Apple 6

5 Apple 1


In this case I'd prefer the rank to return 0, 1, 2, 3, 4, 5 - I can't figure out why: A) Excel is giving the rank of "2" to both values 7 and 6, B) why excel completely skips rank "4."


Thoughts?

 

[Luke M]

Oops, looks like just need to change the greater than symbol in first half of formula. How's this?

=SUMPRODUCT(($C$4:$C$1048576=C4)*($E$4:$E$1048576>=E4))-SUMPRODUCT((C4:$C$1048576=C4)*(E4:$E$1048576=E4))+1


returns 1,2,3,4,5,6 now but hopefully that's not too big an issue.

 

[danrosey]

Luke - perfect. Now if only I could get my excel to stop crashing when I try to run this for a few thousand lines...

 

[Luke M]

I would HIGHLY recommend seeing if you can change the 1048576 to something much smaller, perhaps 5000? That's where the strain is coming from. Each array is currently doing over a million calculations, and the whole formula has over 4 million. =O