RANK Function

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
How can I use the rank function, but get it to ignore zero value cells or negative values?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You mean?

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

Where A1:A6 contains the list
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
duh. sometimes you try to look at the complex solutions first, when the easy one is just starring you in the face. Thanks!
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
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.
 

mikechambers

Active Member
Joined
Apr 27, 2006
Messages
397
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.
 

Forum statistics

Threads
1,136,878
Messages
5,678,306
Members
419,754
Latest member
LordEddard

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top