J
JA
Is it possible to set the "rank" formula to overlook zeros?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
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)
An example might help.
Does this do what you want?
=IF(J1=0,"",RANK(J1,$J$1:$J$73,1)-1)
T
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))
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))
T
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))
=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))
A
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,"")
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,"")