Analyze Team Data Values (fantasy football teams)

[coxmg]

Hey guys new here, i have a stats problem, im new to stats and was wondering what the best way to approach this problem is..
i have teams of players in randomly generated lineups NFL players for example, i can use either name or ID value to describe them.
What would be the best way to tell how closely the lineups are correlated with each other? for example 2 lineups may contain the same qb but different running backs. Each team has the same # of players for example 9. what is an excel function to allow me to calculate how similarly the lineups are related? lets say that i have 50 lineups with 9 players each. The same qb might be included in 5 of the 50 etc. i know there is the correll function but that only accepts 2 inputs not 50.

 

[Romsek]

Hey guys new here, i have a stats problem, im new to stats and was wondering what the best way to approach this problem is..
i have teams of players in randomly generated lineups NFL players for example, i can use either name or ID value to describe them.
What would be the best way to tell how closely the lineups are correlated with each other? for example 2 lineups may contain the same qb but different running backs. Each team has the same # of players for example 9. what is an excel function to allow me to calculate how similarly the lineups are related? lets say that i have 50 lineups with 9 players each. The same qb might be included in 5 of the 50 etc. i know there is the correll function but that only accepts 2 inputs not 50.

Label all the positions uniquely. Label all the players uniquely.

Then I would create a distance function between teams that measures the number of mismatches between positions.

It could be a simple count of the mismatches or you could weight these mismatches by position. It might be more important for example if the quarterbacks are different than if the linemen are.

So if you're using excel to do this I'd have column A be the position labels, column B would be the weights of each position. Columns C and D would be the player labels for teams 1 and 2.

Then column E would have a function along the lines of =If(C#!=D#,B#,0), i.e. assign the weight if there is a team mismatch in the row (denoted by #), or assign 0 if they match.

Have a sum at the bottom of column E totalling these mismatch scores up for the entire column.

You could of course add additional columns representing additional teams you want to compare with the team in column C. I assume you're savvy enough with Excel to do this.

Sounds like about 2 minutes of work to me.... other than the data input.

 

[coxmg]

NOT QUITE as simple as you make it sound but thanks for the advice. the problem with what you are suggesting is that i would need to get the variance between 1 particular lineup and the other 49 lineups i have created. so you need something that compares one to each of the others. one other problem is that for this particular fantasy competition, 1 lineup can be made up of players from both teams.

Label all the positions uniquely. Label all the players uniquely.

Then I would create a distance function between teams that measures the number of mismatches between positions.

It could be a simple count of the mismatches or you could weight these mismatches by position. It might be more important for example if the quarterbacks are different than if the linemen are.

So if you're using excel to do this I'd have column A be the position labels, column B would be the weights of each position. Columns C and D would be the player labels for teams 1 and 2.

Then column E would have a function along the lines of =If(C#!=D#,B#,0), i.e. assign the weight if there is a team mismatch in the row (denoted by #), or assign 0 if they match.

Have a sum at the bottom of column E totalling these mismatch scores up for the entire column.

You could of course add additional columns representing additional teams you want to compare with the team in column C. I assume you're savvy enough with Excel to do this.

Sounds like about 2 minutes of work to me.... other than the data input.