ExcelBanter - View Single Post

Basenji

Thank you for the explanations.

"T. Valko" wrote:

1.What is the purpose of the dashes in front of the range?

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

2.Why is "1" added at the end?

In essence, the formula is counting how many numbers are less than n.
Consider this example:

5 rank = 1
10 rank = 2

When ranking 5 there are no numbers less than 5 so the rank would 0 but
that's not a valid rank so we add 1 to get a rank of 1.

When ranking 10 there is 1 number less than 10 so the rank would be 1 but we
already have a rank of 1 so we add 1 to get a rank of 2.

--
Biff
Microsoft Excel MVP

"Basenji" wrote in message
...
Thank you for your assistance as it met the need. A couple of questions:
1. What is the purpose of the dashes in front of the range?

2. Why is "1" added at the end?

Thank you.

"T. Valko" wrote:

Try this:

=IF(B2=0,"",SUMPRODUCT(--(B$2:B$100),--(B$2:B$10<B2))+1)

--
Biff
Microsoft Excel MVP

"Basenji" wrote in message
...
In column A is the name of the accout; column B, minutes, column c, the
rank
where the lowest number of minutes is ranked number 1. Any accounts
with
zero
minutes should not be ranked or should have the highest rank (low
minutes
are
good; zero minutes means no rank)

Col A Col B Col C
Fred 69 8
Chris 0
North 41.4 6
Alex 52 7
Larry 0
South 0
Olaf 0
Merry 0

I have tried a combination of if and rank functions, such as this one:
=IF(A2=0," ",(RANK(a2,$a$2:$a$10,1))), but the zero minutes is causing
the
North account to show a rank of 6 when it should have a rank of 1. Any
asstance would be appreciated. Thank you.