excel formula help

Copper Contributor

Hi everyone,

First I just want to thank all of you for your extraordinary abilities!  Here is my problem.  I want to track basketball teams, players and the points scored per game.  The first sheet is the team with the coach's name and the players.  Since they play different teams each week, I would like my game 1 tab to fill out the players per the coaches name.  I will then input the points per player.  I'm hoping that there's a formula that which ever point total is larger, the coaches name will appear as the winner.  On the Totals tab, I would like the players points to be put in the game 1 column.  Now on game 2, the coaches will play a different team and again, once I input the coaches name, the players names will populate with the appropriate coach.  Then we do it all again.  I would like to be able to create up to Game 10.  Please let me know if this is "A" possible, or if it's a pipe dream and if I'm going to have to input this manually!  Thanks!

 

Dean

5 Replies

@testing out

 

Hello there!

 

-Automatic updating on the Totals tab

-10 game tabs

-Just select from the dropdown arrow for each coach for the game tabs and the team roster will populate

-winning coach automatically updating on each game

 

Please take a look at the attached spreadsheet. Hopefully it is to your liking. Let me know if you have any questions! Thanks!

 

 

Chris,

That is unbelievable!  I just have an update to it as I added a player to a team, so there are 9 players on one team.  Is there a simple way to add players as I did a copy/paste hoping that it would be able to recognize the formula, but that didn't happen.  Please see game1 tab and the added player.  Also, when the averages are calculated, are they calculated based on the per game, so if I play 2 games, the formula knows to divide by 2, etc?  Or is it based on 10 games.  Thanks again for the help!

 

Dean

Dean,

 

I am happy you like it so far! I am working on the update to the players now. I'll make is so that there is a possible of 10 per team, that way you have more wiggle room just in case you need it. 

 

Also, to answer the second question, the average points per game will only take the average of the games played so far. So if you have only played 4 games it will automatically average just the first 4 games. 

 

I'll send over the updated file as soon as I am done with it! Thanks!

That's great!  Not to make this even more complicated, but if there were 8 teams, how would the formula translate?  Would you need to create another grouping and change all the formulas?  You got me thinking once you mentioned 10 players per team.

 

Dean

@testing out

 

I've created an 8th team for you. All you would need to do is type in the coach's name and the roster names on the first tab (Teams List) and everything else will populate. 

 

Also, I made it so that each team has a 10 player roster potential. There are a lot of "N/A"s on the game tabs right now but those go away once you select a coach from the drop-down menu in the blue and orange sections. 

 

Hope you like it! Thanks!