Using Rank in an Array Formula

  • Thread starter Thread starter Ricardo Dinis
  • Start date Start date
R

Ricardo Dinis

Hi,

I'm using the Rank function in an Array Formula, but after several
tries I always got #VALUE! Error.

What I'm trying to get is the rank of a value (e.g. 4) in subset of a
list of values (e.g. {4;3;2;1} where A1:A7="a").

A B
1 a 4
2 a 3
3 a 2
4 a 1
5 b 9
6 b 8
7 b 7

={RANK(B2,IF(A1=A1:A7,B1:B7),0)} => #VALUE!

Anyone as a hint to this problem?

Thanks in advance,
Ricardo Dinsi
 
Another approach without an array:-

With your data in A1 - B7 as below put this in C1

=SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1

The formula will give the rank of B1 for the group in A1
The formula is dragable

Mike
 
Another approach without an array:-

With your data in A1 - B7 as below put this in C1

=SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1

The formula will give the rank of B1 for the group in A1
The formula is dragable

Mike
Click to expand...

It works fine. Thank you very much.

Can you explain me what does the '--' operator before conditions or
give me a link? I google it and i can't find it :(
 
Converts Boolean constants (TRUE, FALSE) to numbers (1,0)
Have a look at: J.E McGimpsey's sitehttp://mcgimpsey.com/excel/formulae/doubleneg.html

It is odd that the formula =RANK(3,{1;2;3;4;5}) fails when Help states that
RANK works with an array or reference to an list of numbers
best wishes
Click to expand...

Thanks. It'll be very handy!
 
Another approach without anarray:-

With your data in A1 - B7 as below put this in C1

=SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1

Theformulawill give therankof B1 for the group in A1
Theformulais dragable

Mike











- Show quoted text -
Click to expand...

What if you have two entries the same at equal posistions, can you add
a bit to make the first in the list one and the other second?
 
What if you have two entries the same at equal posistions, can you add
a bit to make the first in the list one and the other second?- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry, that isn't very cear. If it ends up the that there are two
values the same, they will have equal rank. Can the formula be made
to stop this from happening? So if there are two entries that are
3rd, one is made to be 4th?
 
Back
Top