Ranking range of cell with value only

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

 

[Max]

Try:

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

 

[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

 

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

Then use this formula:

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

Biff

 

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Max]

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

 

[Biff]

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

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

Biff

 

[Aladin Akyurek]

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

 

[Max]

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

 

[Domenic]

Actually, the following would suffice...

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