Calculating number of times one "team" beats another in Excel

user_14643367 0
Viewed 120

(I use the term "teams" generically here because the entirety of this question rests on ranking, and it seemed to be the most intuitive language to describe my problem.)

In a league of 30 teams, each day only 8 teams play. The results for those teams are ranked ordinally from 1 to 8 for the day. This continues "forever", so that additional results must be recorded every day.

Example after 4 days: enter image description here

I want to calculate a single number to describe the relationship between two teams. For instance, given the example, the value (in a 2d table) that describes the relationship of Ace to Get is 1. Ace beat Get twice and Get beat Ace once (2-1).

I have been messing with Sumproduct, Match, and Index to get get values, which I could calculate using many extra tables, but I may need to add "teams" on the fly, and I do not know how large the pool of teams will become. Because of this, I was hoping to be able to use a single formula in the 2d relationship table. The results of that table, looking at just day 1 and day 2 given the previous example, are: enter image description here

Is there a direct formula I can use to calculate the results to populate that table?

excel
1 Answers

You can try following formula:

=IF($A11<>B$10;
   SUMPRODUCT(
     IF(MMULT(($B$1:$I$1)*($B$2:$I$3=$A11);ROW($1:$8)^0)
       <MMULT(($B$1:$I$1)*($B$2:$I$3=B$10);ROW($1:$8)^0);
     1;
     -1)
     *(((MMULT(--($B$2:$I$3<>$A11);ROW($1:$8)^0)=8)
        +(MMULT(--($B$2:$I$3<>B$10);ROW($1:$8)^0)=8))
       =0));
"")

Copy right and down.

enter image description here

user_121888610
Related