As the title indicates, I would like to rank data in a given range. However, the "Rank.EQ" function does not rank number sequentially if duplicates are present. The result ends up being 1,2,3,4,4,4,7,8,9,10... instead of 1,2,3,4,5,6,7,8,9,10... I have tried several work arounds, but I can't seem to wrap my head around how to get them to work properly.
here are some examples of various codes I have tried...
Example 1:
=SUM(1*(A3<$A$3:$E$7))+1+IF(ROW(A3)-ROW($A$3)=0,0,SUM(1*(A3=OFFSET($A$3,0,0,INDEX(ROW(A3)-ROW($A$3)+1,1)-1,1))))
Example 2:
=RANK(A3,$A$3:$E$7,0)+COUNTIF($A$3:A3,A3)-1
The code in the first example does not work properly when negatives are present in the range of data.
The code in the second example does not work properly when the range of data is extended to multiple columns.
I have attached the excel spreadsheet for your viewing. As you will see, My end goal is to conditionally format the TOP FIVE results (either top FIVE most negative or top FIVE most positive). The highlighted cells must also be greater than 500 or -500 depending on what report i run on the given data range. This concept will be applied to larger spreadsheets to highlight the top most "material" deviance's.
This was a simple task using the AND function and RANK function, that is until i realized i could potentially run into issues with duplicates! I really could use some help on this!
Bookmarks