Rank function ignoring only zero values

  • Hello,


    I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?


    =IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))


    Thanks.

  • Re: Rank function ignoring only zero values


    Take your sample worksheet and place this where d5 starts and copy across the entire row. Should give you what you're looking for.


    =IF(D4>0,(IF(D4=0,0,RANK(D4,$D$4:$AY$4,0))),IF(D4=0,0,(IF(D4=0,0,RANK(D4,$D$4:$AY$4,0)))-(COUNTIF($D$4:$AY$4,"=0"))))


    Assuming that works, this should work then on your actual sheet...


    =IF(D94>0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0))),IF(D94=0,0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0)))-(COUNTIF($D$94:$AY$94,"=0"))))


    Have to say this stumped me for a while. Enjoyed it.
    Let me know if it works for you please.

  • Re: Rank function ignoring only zero values




    That did work thanks!

  • Hi Rowddawg,


    Thanks for the formula. In my spreadsheet it is ignoring the zeros but the ranking numbering only commences once the number of zeros has been factored in. For example, if there are two cells containing zero the ranking commences at 3. With five cells containing a 0 the ranking commences at 6. Is there a way to make the lowest score bar zero have a ranking of one?


    Many thanks

  • Thanks Tom,


    Please see below:


    [TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.001

    [/td]


    [td]

    6

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.100

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.000

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.000

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.000

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.000

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    0:00:00.000

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [/TABLE]


    I was expecting B1 to be 1 and B2 equal to 2 but the five 0s have been factored into the ranking order.


    Is there something that needs to be added to the forula to avoid this?


  • From where you post that data???