Rank based on two Criteria
I have created a sheet based on different formulas from Cell B3:AY8. The output of these calculations is displayed in range BA4:BG8. (sheet attached)
Now I want that the output as appeared in Range BA4:BG8 should be automatically Ranked based on Points & then on NRR. Means, in case two teams have equal points then the one with better NRR gets better Rank and Output range sorted accordingly in descending order.
Last edited by leo73pk; 06-28-2010 at 11:34 AM.
not sure whether + or - nrr is best do for tie break on +ve best based on cols BF/BG
=RANK(BF5,$BF$5:$BF$8)+SUMPRODUCT(--(BF5=$BF$5:$BF$8),--(BG5<$BG$5:$BG$8))
or for -nrr best
=RANK(BF5,$BF$5:$BF$8)+SUMPRODUCT(--(BF5=$BF$5:$BF$8),--(-1*BG5<$BG$5:$BG$8*-1))
have a look at this i did the ranking in colAZ based on +ve best then changed your table slightly
=RANK(AX5,$AX$5:$AX$8)+SUMPRODUCT(--(AX5=$AX$5:$AX$8),--(AY5<$AY$5:$AY$8))
and
=INDEX($AM$5:$AM$8,MATCH(ROW(A1),$AZ$5:$AZ$8,0),1)
Last edited by martindwilson; 06-28-2010 at 03:02 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Martin,
Since +NRR is better, therefore your formula is fine and its works as well and ill get back to further extend it on the sheet. As regard syntax of your formula is concern i'll appreciate if you could explain that you use -- sign what does that mean
not sure whether + or - nrr is best do for tie break on +ve best based on cols BF/BG
=RANK(BF5,$BF$5:$BF$8)+SUMPRODUCT(--(BF5=$BF$5:$BF$8),--(BG5<$BG$5:$BG$8))
or for -nrr best
=RANK(BF5,$BF$5:$BF$8)+SUMPRODUCT(--(BF5=$BF$5:$BF$8),--(-1*BG5<$BG$5:$BG$8*-1))
have a look at this i did the ranking in colAZ based on +ve best then changed your table slightly
=RANK(AX5,$AX$5:$AX$8)+SUMPRODUCT(--(AX5=$AX$5:$AX$8),--(AY5<$AY$5:$AY$8))
and
=INDEX($AM$5:$AM$8,MATCH(ROW(A1),$AZ$5:$AZ$8,0),1)
-- changes true/false to 1/0 so calculations can be done on the results
see
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
Martin
Thanks for your prompt response. One more thing i would like to ask in the index formulae you used a (Match(ROW(A1), While A1 is a blank cell. why this particular reference cell is used (first cell of a sheet).
row(a1)=1or even row( z1)=1 so that just generates 1
so dragged down becomes row(a2) =2
just saves having to increment 1,2,3,4 manually
Thanks martin. the formula proposed by you works fine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks