How to compare common opponents

[Jimmy Walker]

I have been lurking for a while, reading a lot of great questions and answers! I could use some help that is a little above my excel pay grade:

I am looking to compare two or more college wrestlers against common opponents. Results are posted in a dual-meet format so:

125 lbs wrestler 1 dec. wrestler 2 (5-1)
and so on through 10 weight classes.

So a given wrestler may have a record of 30 wins and losses, across multiple dual meets and tournaments. I want to see how their next opponent did against similar opponents.

I have all the data of wins and losses.

How could I format this so that I could type in the two wrestlers names, and a chart (or something) would pop up and show how each of them did against common opponents including each other?

I have worked on this for a while, but need some fresh ideas! Maybe there is a video about this topic (I have not found one that specific to sports)? Any help is appreciated!

JW

 

[Rijnsent]

Hi JW,
you could e.g. pick up the last x matches of both wrestlers (or the last n months) and show them as a spark chart, see e.g. https://www.vertex42.com/blog/help/excel-help/sparklines-in-excel.html
I hope you do have the data in a table with columns or is it just text?
Cheers,
Koen

 

[Jimmy Walker]

Koen:
Thank you for your response! Yes, the data is in column form. That spark chart is really interesting. Not sure I had seen those before. I like the visual impact they give.

My original question was a bit more basic than though. I had been looking for how to find the duplicates in the first place and record the differences in the wrestlers outcomes. I think I may have solved that, but would be interested in how others might do it (as I may be over-complicating things!).

Thank you!

JW

 

[Rijnsent]

Hi JW,
got ya. What I would do with this question is probably build myself some macros to extract the right information. With your data in a base table, using the macros to filter that table, copy-paste those results to a temporary result page. Based on that temp result page I would make a sheet with two dropdowns for the players and a button to run the macro. In that report/overview sheet I would show per player over a period the results with a win-lose spark chart and maybe 3 small lists of the last 6 matches of each contestant plus the (max) last 6 matches of an individual. Maybe if I would want to go all out I'd try to calculate a rating (like the ELO rating for chess players) per wrestler.
Hope that helps,
Koen

 

[Jimmy Walker]

Koen:

I like the ranking, elo idea quite a bit. I understand elo and how it is calculated. I think if I assumed all wrestlers to be equal at the beginning of the season, and assigned them all the same elo, I could then play out the rankings from there. But since they aren't equal at the beginning of the season, and the elo system would assign more to a wrestler who won over someone who is better than them, how would i go about doing that? Or would that not be a good idea, as I don't have any data from the past. Only data from this year.

If it isn't much trouble, could you link to an example of making an excel elo system from scratch? Maybe one based on multiple levels (Like Rating "E" being lowest, to rating "A" being highest)?

You have been a lot of help!

JW

 

[Rijnsent]

Hi JW,
a bit of googling does the trick (don't have an ELO calculator present), e.g.
https://boardgamegeek.com/filepage/98469/elo-generator
https://spreadsheetsolving.com/sports_league_power_ratings/
Or at this forum some related posts:
https://www.mrexcel.com/forum/excel-questions/795123-using-excel-solver-generate-power-rankings.html
https://www.mrexcel.com/forum/excel-questions/978116-elo-rating.html -> with downloadable example

And for the start of the season all players either start where they ended last season or at the same value (maybe league based?).
Good luck and I'm curious to see what you make of it!
Cheers,
Koen