RANK Function

mikechambers

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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You mean?

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

Where A1:A6 contains the list

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

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.

Try

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

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.

Replies
1
Views
105
Replies
3
Views
255
Replies
5
Views
124
Replies
5
Views
127
Replies
3
Views
99

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back