excel learner

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

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

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

Maybee this formulae helps ?

Maybee this formulae helps ?

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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,

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

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=""),"",COUN

regards, barry

ASKER

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

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

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))`

ASKER

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