Getting Rank formula to exclude zero and blank cells

I need a column of data to be ranked (from lowest value to highest value). Here are some of the details:
1. The data is a column made up of distances to a certain target, ie, whomever gets the closest will rank #1 and is the winner.
2. That column of data is also the result of a formula from another column (I don't think this detail matters.)
3. I need the range to increase as the number of participants in the contest increase, ie, 5 are ranked when there are 5 participants, 6 are ranked with 6 participants, etc.
3. I need the ranking to not count blanks or zeros. (This is difficult when the participants don't go in the order that they were registered, so #5 participant may not go until the 20th turn, or #15 participant may not even have a result for this contest but might only be in the next contest, etc. So when I skip ones that don't have results to submit, and then go on to the next one, I need for that ranking formula to increase the range only by the number of cells that have actual values.

Here is a brief example. The first one would be if only 2 people had gone already.
Participant Name ........................ Total Dist (in inches).........................Rank
1. Joe................................................. 2.5..........................................1
2. Sam..................................................3...........................................2

Then, if the next participant missed his turn (but could take it later), then I need the rankings to not include him until he took his turn.
Participant Name................................Total Dist (in inches).....................Rank
1. Joe ............................................................2.5................................ 2
2. Sam ..........................................................3 ...................................3
3. Bob .......................................................... ___ (blank)
4. James .......................................................1....................................1 (See how the rankings changed? The guy who used to be in 1st place moved down to 2nd place after the 3rd guy entered his distance.)

Any other info needed?
Thanks. Really.

So sorry for long delay in replying. I know that isn't helpful to not know how your formula worked or not. I thank you for that formula and it did work for ranking my competitors. As each participant was added, the ranking changed accordingly. The formula even worked if someone skipped their turn and the next person went, so in other words, skipping a row of data did not effect the ranking and the formula did not use that blank row in the calculation of rank at all!
The only thing that I wish you could change is that as new data was added in each row, in my example it would be a distance, the formula column would fill in the cells corresponding to no data as last in the ranking. To be clearer, if I had 10 participants, and only 2 had gone, the column would rank the first two correctly as 1 or 2, then the rest of the cells in that column would be 3,3,3,3,3,3,3.
I would like for them not to show that number.

Hi,

Just try below formula to avoid Zero Value & Blank auto not getting in Range.




=IF(B2<>0,RANK(B2:B4,$B$2:$B$4),"")

By

Vengato

Here is the attached file with examples of the problems. I think one of the problems might be the way I formatted the columns.
The problems I have are thus:
The first example seems to be correct, but in the 2nd example,
1. when I changed Betty's distance to 0, why did the 0 disappear?. Her distance was her actual entry, ie, she hit the target right on the dot.
BUT ALSO in the 2nd example,
2. when I changed Betty's distance to 0, why did the "non-participants" get ranked as #1 as well? They did not have a zero distance but rather, they had not had their turn yet, ie, a blank for a value.
AND lastly for the 2nd example,
3. why did the rest of the participants with actual values get ranked starting at 2nd place?
AND THEN, in the last example,
4. when I changed Betty's distance to 0 in that one, the same thing happened with all the other ones who had not yet participated, but the ones who did have actual values were decreased in rank as if all the others had zero distances?

Thanks.

Good Grief! Just saw your last post before my above reply. Sry. But I did try your formula and it doesn't work. But I really think that the problem is with formatting so let me see if you can help me figure it out using the attached file above and we'll go from there if you don't mind.