Removing certain numbers from a ranked column

[j0SAND]

I want to remove " 1 " from a ranked column.

Scores are recorded in column A and using the formula " =RANK(A3;$A$2:$A$17:1) in column B3, these scores are change to "positions" in column B where the lowest scores get highest position.

The problem is that when no score is recorded, a zero is put in column A. This equates to the lowest score and in column B, its given a position of " 1 ".

Is there a better formula to use or edit the formula that I have used.

Many thanks from a complete newbie.

[Pete_UK]

Change your formula to this in B3:

=IF(A3=0,"",RANK(A3,$A$2:$A$17,1))

(changes in red), then copy down. If you want to retain the 1 for the lowest valid number (and adjust others accordingly), you can use this:

=IF(A3=0,"",RANK(A3,$A$2:$A$17,1)-(COUNTIF(A:A,0))*1)

You may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

[j0SAND]

Hi there and thx for the quick reply however I have tried your suggestion with and without the changes to colons and semi colons, and neither seems to work.
I place your formula into cell A3 and then drag down but it only drags the formula down.

Perhaps it would be better to adjust the formula that produces the "0" in column A

[Glenn Kennedy]

In B3, copied down.

=IF(A3=0,"",COUNTIFS($A$3:$A$17,">0",$A$3:$A$17,"<"&A3)+1)

See sheet for ; and , punctuation...

[Pete_UK]

I did say to change your formula in B3. You only had numbers in column A of your sample file - no formulae.

Pete

[j0SAND]

Hi Glen, thx for your reply and answer.

This has certainly got rid of all the unwanted zeros. I first copied your text formula....did not work so then I open your attachment and used that formula.

Why are some with colons and others semi colons ?

Kindest regards
John

[Glenn Kennedy]

It's because you are German. You use a decimal comma, we use a decimal point. You use a ; as an argument separator in Excel, we use a , So there are major differences across the EU. UK and IRL use . and , everyone else uses , and ;

It is a reason why I do NOT like pasting formulae into the body of the thread, But we get criticised if we do not show formulae in our replies....

You just can't win...

Thanks for the feedback. Keep safe, keep well.