RANK function

[Gwill1983]

Hi all,

The rank function is driving me insane!!

I am trying to add a rank column to my table, but to ignore the 0 values, not have duplicate ranks and show the lowest value (which isn't 0) as 1. So far, I have managed to do everything except for getting it to show the lowest value as 1. I would imagine it will be an easy formula tweak for an expert, but I am baffled!

So.... I have put the rank column in M and the total value that I am ranking is in column R. The formula that I currently have in my first rank cell (R4) is:

=IF(R4=0,"",RANK(R4,IF($R$4:$R$55>0,$R$4:$R$55,0))+COUNTIF($R$4:R4,R4)-1)

This shows the correct rank, doesn't duplicate rankings, and ignores the zeros, but I cannot get it to show the lowest value (that isn't 0) as required.

Can anybody help?

Thanks

Chris

 

[steve the fish]

When you say 0 do you mean the cells are empty or actually show 0? Are there any negative values?

 

[Fluff]

Maybe
=IF(R4=0,"",RANK(R4,IF($R$4:$R$55>0,$R$4:$R$55),1)+COUNTIF($R$4:R4,R4)-COUNTIF(R$4:R$55,0)-1)

 

[steve the fish]

Or this in case of the negatives:

=IF(R4<>0,RANK(R4,$R$4:$R$55,1)+COUNTIF($R$4:R4,R4)-((R4>0)*COUNTIF($R$4:$R$55,0))-1,"")

 

[Gwill1983]

Maybe
=IF(R4=0,"",RANK(R4,IF($R$4:$R$55>0,$R$4:$R$55),1)+COUNTIF($R$4:R4,R4)-COUNTIF(R$4:R$55,0)-1)

This worked perfectly! Out of interest, what was I doing wrong??

 

[Fluff]

The Zero values were still getting ranked, so you need to subtract the number of Zeros from the rank value

 

[Gwill1983]

Hi all,

I have only just spotted that this is not returning a #1 in the rank column.

Can anybody help me to understand why?

Thanks

Chris