Removing certain numbers from a ranked column

[j0SAND]

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

Scores are recorded in column K and using a formula in column L, these scores are to be changed to "positions" ie 1st, 2nd, 3rd and so on where the lowest scores get highest position.

The column K goes from K9 to K100

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

My question is how do I get either a blank instead of a "1" or better still, a "WD" for withdrawn

Can a formula to be used ?

Many thanks,

PS. I have posted this question in the excel 365 part of the forum and have just found out mine was a freebie and the "Free" has just run out and I can no longer use it. I now have Excel 2007....lol

I live in Germany so I believe that the commas and semi colons are a problem.....IF(K9=0;"";COUNTIFS($K$9:$K$100;">0";$K$9:$K$100;"<"&K9)+1 was the formula I thought would work but it does not

[vba_php]

your description was not clear. per your words:

My question is how do I get either a blank instead of a "1" or better still, a "WD" for withdrawn

are you wanting a formula or code? because if the former, it's as simple as an if() function.

[j0SAND]

sorry for the misunderstanding,

instead of the figure "1" being given in column L9 after it seeing a zero in column K9, I would like to see either a blank or better still "WD" for withdrawn

A formula to put it cell L9 to change the "zero" to either a blank in L9 or the letters "WD"

The formula that i quoted was good in excel 365 but that has gone. it does not work in excel 2007

Thanks for your time

[JeteMc]

I am not entirely sure that I understand the question, however if you are looking for a 2007 version substitution for the formula in post #1 try:

Please Login or Register  to view this content.

If that is not what you want then please utilize the instructions in the banner at the top of the page to upload an .xlsx file that illustrates your request.
Let us know if you have any questions.

[j0SAND]

hey and thx.....it works

why are the formulas different betwen 365 and 2007 ?

[JeteMc]

I had thought that COUNTIFS was introduced with the 2010 version of Excel, however looking at the "Excel Functions: Compatibility Reference (2010, 2007 & 2003)" it seems to have been introduced with the 2007 version, so I am not sure why the formula in post #1 is not working for you. Perhaps if we could see a sample if the spreadsheet we would be in a better position to help troubleshoot.
That said, Your Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.