Ranking range of cell with value only

C

cardingtr

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.
 
B

Biff

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
 
B

Biff

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.
Click to expand...

Then use this formula:

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

Biff
 
C

cardingtr

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.
 
D

Domenic

Try...

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

Hope this helps!
 
M

Max

Sorry, I probably mis-interp'ed what you wanted.
See Biff's and Domenic's suggestions ..
 
B

Biff

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

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

Biff
 
M

Max

Biff said:
I thought the OP wanted to exclude ranking 0's.
Click to expand...

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.
Click to expand...

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 ..
 
Top