Set up ideas please

[excelakos]

Ok recently i read an article about how could you rate a team's, player's performance. The method takes in account only wins. Wins of team in question, wins of this team's opponents, wins of team's opponents opponents.
So it somehome weights a team's wins against who it has played against directly & indirectly.
In example if i want to have an idea which team has the best performance after round 9 i calculate team's win ratio. Lets say 5/9 wins=0,555
Then i calculate the average win ratio of those 9 opponents. Lets say 0,7. And then the average of those opponents opponents. Lets say 0,6. So the team's in question form weights 0,555+0,7+0,6 divided by 3=0,618

If another team has also 5/9 but opponents' average was 0,6 and their opponents' 0,45 this team's form would weight 0,535 which represents a more realistic form cause 2nd team has managed same win ratio as the first but against a poorer group of opponents directly & indirectly. So the numbers give a fair ranking.

Any ideas how could someone use excel to produce this rankings as simple & fast as possible?

Thank you in advance

 

[cyrilbrd]

Hi, it would go down to how your raw data is organized, the simpler the better.
reporting could be done via formulae or pivot table for your convenience.

 

[excelakos]

Hi, it would go down to how your raw data is organized, the simpler the better.
reporting could be done via formulae or pivot table for your convenience.

Cyrilbrd thank you for your post. Please have a look at following.
Well I wonder if following can even be done. I alter a little bit the theory and I split team performance into home & away separately.
My raw data should be Home Team-Away Team- Result (1=win 0,5=draw 0=defeat).
What I want to have is when I filter a home team in pivot table 1, then I get automatically the rest calculations (in other 2 separate pivot tables) so as to at once be given the home team’s calculation.
Analysis of home team, home form calculation.
1)Win % of home team.
2)Win % of home team’s opponents on their away games
3)Win % of opponents’ opponents on their home games.
In other word a team has played against “n” opponents at home, these opponents have played away against “z” teams and those z teams have played at home with “r” teams.
So from the raw data I should get a pivot table with home team column filtered (only the home team in question) and next to it I shall get all the opponents.
Now how could I get a 2^nd pivot table that automatically filters the away teams’ column with the away teams shown on pivot 1?
Lastly how could I get a 3^rd pivot with home team column filtered with the home teams of pivot 2?

I hope I made myself clear. If vba should be used please note a vba amateur is 10000 more experienced than me.
Are there any relevant posts in the forum?

Thank you.

 

[Brian from Maui]

Just about every sports have some sort of rankings/ratings. They use strenght of schedule, RPI ratings etc. etc. You might want to Google/Bing and see what kind of algorithms are used to rank teams in each sport.