Using Rank in an Array Formula

[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

 

[Mike H]

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

 

[Ricardo Dinis]

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

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

 

[Mike H]

Ricardo,

I'm glad it solved your problem, thanks for the feedback. The -- or double
unary converts true/false to 1/0 to see it in action enter this formula

=(--(A1="Dog"))
will return 0 unless A1 contaiins Dog

For a more professional explanation have a look here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Mike

 

[Bernard Liengme]

Converts Boolean constants (TRUE, FALSE) to numbers (1,0)
Have a look at: J.E McGimpsey's site
http://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

 

[Ricardo Dinis]

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

Thanks. It'll be very handy!

 

[matthew.webb]

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 -

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?

 

[matthew.webb]

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 -

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?