RANK values above 0
Hi
I've been pondering this for about an hour using various permutations of RANK and IF function but I am at a loss.
I need to rank the following but exclude anything less than or equal to zero so I should be left with 5 positive numbers ranked 1 to 5. There does not seem to be a RANKIF function, unless I'm missing something.
Many thanks for your help in advance.
0.00
0.00
0.14
0.55
0.52
-0.20
-0.69
-0.26
0.92
0.80
-0.33
-0.48
-0.76
-0.17
-0.76
Assuming your data start in A2, use this in B2, copied down.
It's an array formula.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
=IFERROR(LARGE(IF($A$2:$A$16>0,$A$2:$A$16),ROWS($B$2:B2)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Try this (data in A1:A15)
in B1 and copy down
=IF($A$1:$A$15>0,COUNTIF($A$1:$A$15,">=" &A1),"")
ok, my apologies I should have mentioned that the cell that the ranking formula looks at contains numbers from a vlookup or index match so the cells do not technically contain the data, if that makes any sense. I can't do the ranking on the original because it comes from a data dump and while the columns don't move I don't want to include all the data.
It shouldn't matter that the numbers are the results of other formulas.
If this is your data:
0.00
0.00
0.14
0.55
0.52
-0.20
-0.69
-0.26
0.92
0.80
-0.33
-0.48
-0.76
-0.17
-0.76
Show us what results you expect.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
It seems to be working now, not sure why it went a bit funny. Many thanks
Just out of curiosity how would you change the formula to reverse the ranking order? I tried swapping the >= to <= but that didn't work, it came up with even larger numbers.Originally Posted by JohnTopley
Largest to Small:
=IF(A1<=0,"",COUNTIF($A$1:$A$16,">"&A1)+1)
Small to Largest:
=IF(A1<=0,"",COUNTIFS($A$1:$A$16,">0",$A$1:$A$16,"<"&A1)+1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks