How to use RANK function ignoring only zero values
Requirement:
The user is trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. The user would like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function that the user has come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. E.G.
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
Solution:
=IF(D94>0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0))),IF(D94=0,0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0)))-(COUNTIF($D$94:$AY$94,"=0"))))
Obtained from the OzGrid Help Forum.
Solution provided by mfortier3.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to rank formula that returns an alpha rank if more than one value has the same rank |
| How to use RANK formula with multiple criteria |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
