RANK Function

[mikechambers]

How can I use the rank function, but get it to ignore zero value cells or negative values?

 

[NBVC]

You mean?

=IF(A1>0,RANK(A1,$A$1:$A$6),"")

Where A1:A6 contains the list

 

[mikechambers]

duh. sometimes you try to look at the complex solutions first, when the easy one is just starring you in the face. Thanks!

 

[mikechambers]

Ok, wait. Actually I'm not as dumb as I thought. Using what was described does not work. Let's say you have a range of ten numbers. 2 of them are negative, 3 of them are 0. If you use the formula by NBVC, it will not show a ranking for the ones that are less than or equal to zero. But it will still rank out of all ten numbers. I need it to realize there are only 5 numbers it should be using for ranking purposes, therefore giving ranking numbers of 1 thru 5. Using NBVCs formula, it will just not see rankings 1 thru 5, and it will start with number 6, assuming I am doing an asscending rank order which I am.

 

[barry houdini]

Try

=IF(A1>0,SUMPRODUCT(--(A$1:A$6>0),--(A$1:A$6<=A1)),"")

 

[mikechambers]

That works perfectly! Now I just have to research it so I understand why it works, especially since you didn't use the rank function. Thanks.