Ranking - Excel 2003
 

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

Ranking - Excel 2003
 

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?

Ranking - Excel 2003
 

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?

Ranking - Excel 2003
 

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?

Ranking - Excel 2003
 

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

B2, copied down:

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

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

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


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