Rank in ascending order in Excel 2003

[Basenji]

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.

 

[T. Valko]

Try this:

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

 

[Basenji]

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]

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.

 

[Basenji]

Thank you for the explanations.

See this:

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


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.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you for the explanations.