I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two classes (East and West I've called them) of data within my dataset and want to rank them both separately (but stick to the rules above). So, if I had seven entries, 3 of them West and 4 of the East, I want West to have ranking 1,2,3 based on all the values that lie in that subset and East would have ranking 1,2,3,4. Can you explain what your formula is doing so I can understand how to apply your answer better in the future.
Effectively I'm asking what formula needs to go in achieve my result.
Cheers
Paul
There are a few related ways to do this, most involving
SUMPRODUCT
. If you don't like the solution below and would like to research other ways/explanations, try searching for "rankif".The function looks up the
Class
andValue
columns and, for every value in those columns, returns aTRUE
or 1 if the currentClass
is a match AND if itsValue
is larger than the currentValue
,False
or 0 if otherwise. TheSUM
adds up all these 1s, and the1+
is for decoration. Remember to enter as an array formula using Ctrl+Shift+Enter before dragging down.I used the array formula and
SUM
above to explain, but the following also works and might even be faster since it's not an array formula. It's the same idea, except we hijackSUMPRODUCT
's ability to spit out a single value from an array.=1+SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8>B2))
EDIT
To extend the rank-if, you could add more subsets to rank by multiplying more conditions:
You can also easily add tiebreakers by adding another
SUMPRODUCT
to treat the ties as an additional subset:The first
SUMPRODUCT
is the 'base rank', while the secondSUMPRODUCT
is tiebreaker #1.