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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ricardo Dinis" wrote in message
...
On 10 Dez, 13:29, Mike H wrote:
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" wrote:
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

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 :(