Ranking - Excel 2003

[JA]

Is it possible to set the "rank" formula to overlook zeros?

[tjtjjtjt]

What do you mean by overlook zeroes?
An example might help.
Does this do what you want?
=IF(J1=0,"",RANK(J1,$J$1:$J$73,1)-1)
--
tj


"JA" wrote:

> Is it possible to set the "rank" formula to overlook zeros?

[tjtjjtjt]

Did you ever find a solution?
This one ignores all zeroes in the test I performed.
=IF(COUNTIF($A$1:$A$40,0)>0,RANK(A1,$A$1:$A$40,1)-COUNTIF(A1:A40,0),RANK(A1,$A$1:$A$40,1))
--
tj


"JA" wrote:

> Is it possible to set the "rank" formula to overlook zeros?

[tjtjjtjt]

I missed a spot--didn't make the second countif contain absolute references.
=IF(COUNTIF($A$1:$A$40,0)>0,RANK(A1,$A$1:$A$40,1)-COUNTIF($A$1:$A$40,0),RANK(A1,$A$1:$A$40,1))
--
tj


"tjtjjtjt" wrote:

> Did you ever find a solution?
> This one ignores all zeroes in the test I performed.
> =IF(COUNTIF($A$1:$A$40,0)>0,RANK(A1,$A$1:$A$40,1)-COUNTIF(A1:A40,0),RANK(A1,$A$1:$A$40,1))
> --
> tj
>
>
> "JA" wrote:
>
> > Is it possible to set the "rank" formula to overlook zeros?

[Aladin Akyurek]

If A2:A8 consists of 0's, empty cells, and positive numbers:

B2, copied down:

=IF(A2>0,RANK(A2,$A$2:$A$8),"")

The following expensive formula should yield the same ranking as the
foregoing:

=IF(A2>0,SUMPRODUCT(--($A$2:$A$8>A2),--ISNUMBER($A$2:$A$8))+1,"")


JA wrote:
> Is it possible to set the "rank" formula to overlook zeros?