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.

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.

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.

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.

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.

You're welcome!

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.