Ranking range of cell with value only
 

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027
View this thread: http://www.excelforum.com/showthread...hreadid=468118

Try:

=IF(AND(ISNUMBER(B5),B5<0),RANK(B5,$B$5:$B$14,1), "")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" wrote
in message ...

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile:
http://www.excelforum.com/member.php...o&userid=27027
View this thread: http://www.excelforum.com/showthread...hreadid=468118

That still doesn't exclude 0's.

Rank is not a very flexible function!

Maybe use a helper column. Enter this in C5 and copy down to C14:

=IF(B5=0,"",B5)

Then:

=IF(C5="","",RANK(C5,C$5:C$14,1))

Copy down.

OR, maybe something *CRAZY*

If the numbers entered *AREN'T* used in any other calculations (other than
the RANK) instead of entering a zero, enter an uppercase letter O.

Biff

"Max" wrote in message
...
Try:

=IF(AND(ISNUMBER(B5),B5<0),RANK(B5,$B$5:$B$14,1), "")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" wrote
in message ...

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile:
http://www.excelforum.com/member.php...o&userid=27027
View this thread:
http://www.excelforum.com/showthread...hreadid=468118

Hit send before I was done:

OR, maybe something *CRAZY*

If the numbers entered *AREN'T* used in any other calculations (other than
the RANK) instead of entering a zero, enter an uppercase letter O.

Then use this formula:

=IF(COUNT(B5),RANK(B5,B$5:B$14,1),"")

Biff

"Biff" wrote in message
...
That still doesn't exclude 0's.

Rank is not a very flexible function!

Maybe use a helper column. Enter this in C5 and copy down to C14:

=IF(B5=0,"",B5)

Then:

=IF(C5="","",RANK(C5,C$5:C$14,1))

Copy down.

OR, maybe something *CRAZY*

If the numbers entered *AREN'T* used in any other calculations (other than
the RANK) instead of entering a zero, enter an uppercase letter O.

Biff

"Max" wrote in message
...
Try:

=IF(AND(ISNUMBER(B5),B5<0),RANK(B5,$B$5:$B$14,1), "")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr"
wrote
in message ...

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile:
http://www.excelforum.com/member.php...o&userid=27027
View this thread:
http://www.excelforum.com/showthread...hreadid=468118

That didn't work. It did not rank the cell. If zero is entered is should
rank the cell as either 1 or the highest number. But instead it leaves
it blank.


Max Wrote:
Try:

=IF(AND(ISNUMBER(B5),B5<0),RANK(B5,$B$5:$B$14,1), "")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr"
wrote
in message
...

I am ranking set of numbers (# of days worked) in a 10 cell column
but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten
cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.


--
cardingtr

------------------------------------------------------------------------
cardingtr's Profile:
http://www.excelforum.com/member.php...o&userid=27027
View this thread:
http://www.excelforum.com/showthread...hreadid=468118
:( :(


--
cardingtr
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027
View this thread: http://www.excelforum.com/showthread...hreadid=468118

Try...

=IF(B5<"",SUMPRODUCT(--($B$5:$B$14<""),--(B5$B$5:$B$14))+1,"")

Hope this helps!

In article ,
cardingtr
wrote:

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.

Sorry, I probably mis-interp'ed what you wanted.
See Biff's and Domenic's suggestions ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" wrote
in message ...

That didn't work. It did not rank the cell. If zero is entered is should
rank the cell as either 1 or the highest number. But instead it leaves
it blank.

Don't pay any attention to my suggestion, then.

I thought the OP wanted to exclude ranking 0's.

Biff

"Max" wrote in message
...
Sorry, I probably mis-interp'ed what you wanted.
See Biff's and Domenic's suggestions ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" wrote
in message ...

That didn't work. It did not rank the cell. If zero is entered is should
rank the cell as either 1 or the highest number. But instead it leaves
it blank.

=IF(N(B5),RANK(B5,$B$5:$B$14),"")

cardingtr wrote:
I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day.
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B $14,1))

Thanks.

"Biff" wrote:
I thought the OP wanted to exclude ranking 0's.

That's what I thought. But the converse is true,
as confirmed by the OP's response.
The OP wants to rank zeros, but not blanks

Don't pay any attention to my suggestion, then.

And why not <g ? Thought both your suggestions
and Domenic's worked for what the OP confirmed was wanted

And Aladin's suggestion
(with the rank order corrected to give an ascending sort):
=IF(N(B5),RANK(B5,$B$5:$B$14,1),"")

seems to yield the same returns as mine ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Actually, the following would suffice...

=IF(B5<"",RANK(B5,$B$5:$B$14,1),"")

In article ,
Domenic wrote:

Try...

=IF(B5<"",SUMPRODUCT(--($B$5:$B$14<""),--(B5$B$5:$B$14))+1,"")

Hope this helps!