# Ranking formulae where there blank rows or rows with 0 value

Dear experts,

Please provide a formula in column c which will look in range column A and ignore cells with either ""(no value) or 0 and then ascertain the largest and number it as 1 and second largest as 2, results expected is put in column b.

Kindly help.

Thank you
Ranking-formulae.xlsx
excel learner

Dear experts,

Column could have negative numbers as well. Hence by logic the largest number will have the highest ranking.

That is if column A had only two numbers -110 and -10000. -10000 will get rank 2 nd -110 will get rank 1.

That is if column A had only three numbers 1,  -110 and -10000. -10000 will get rank 3 nd -110 will get rank 1 and 1 will get rank 1.

Kindly help.

thank you
If I understand well, your formulae is correct  but need to ignore blank cell or 0 value ?

Maybee this formulae helps ?

``=IF(OR(A5="";A5=0);"";RANK(A5;\$A\$2:\$A\$8)+COUNTIF(A\$2:A5;A5)-1)``
barry houdini

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Barry,

Thank you for the response.

One question, I have not seen you active in last few months or my questions were not interesting.

Can i request a small twist to the original question.

Can we modify the formula to give the smallest negative value the rank of 1 and the max positive value the last rank.

Kindly help.

Thank you,
Hello Excellearner. Yes, I haven't been as active at EE as I was in the past, still around, though....

Do you just want to reverse the ranks from previously? If so then just change the ">" to a "<", i.e.

=IF(OR(A2=0,A2=""),"",COUNTIFS(A\$2:A\$8,"<>",A\$2:A\$8,"<>0",A\$2:A\$8,"<"&A2)+COUNTIF(A\$2:A2,A2))

regards, barry

Barry,

Thank you for the formula.

As usual, it was brilliant.

Help me understand when you have time.

Welcome back and please do not go away.

Hi Lazyfolk,

With due respect, I have tried yours, but it did not work.

Do you want to try an alternative approach.

Thank you
You were right, my formulae failed with zeroes when table contain negative values

This version reduce the rank for negative values by the number of zeroes found in column A

``=IF(OR(A2="";A2=0);"";RANK(A2;\$A\$2:\$A\$12)+COUNTIF(A\$2:A2;A2)-1-IF(A2<0;COUNTIF(\$A\$2:\$A\$12;0);0))``