Ranking a number outside of range's values?

[pompair]

Hi

I have in A1:A5 numbers:

7
4
9
1
2

If I write =RANK(7;A1:A5;1) the result is rightly 4. This is the rank
for the number 7 in this range.

But what I'd like to do is to find the rank for some number outside of
the range, like 3 for example, how would I do it?

I mean, trying something like: =RANK(3;A1:A5;1) results in #N/A

Any ideas?

 

[T. Valko]

Try this:

=SUMPRODUCT(--(3>A1:A5))+1

Or:

B1 = 3

=SUMPRODUCT(--(B1>A1:A5))+1

Biff

 

[pompair]

Thanks, it just works!

 

[T. Valko]

You're welcome!

Biff