Link to home
Start Free TrialLog in
Avatar of excel learner
excel learnerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of excel learner
excel learner
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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))

Open in new window