Homework 5 - Excel/Database

video instructions  You may either use these video instruction or the written instructions below.

Create the Excel workbook as shown in the instructions. It should have the following sheets: Top 10, Team Lookup, Teams, and Players.  Your results may be different since the data is updated every year.

Step 1
Download the NBA.xlsx spreadsheet.  It will have the Players and Notes sheets.  The Notes sheet shows the Players column abbreviations.

Step 2
Create a new sheet named Teams.  Enter all 30 NBA Teams along with the Division, Head Coach, and number of Championships.  This step may take some time.  You may want to use Wikipedia to get the information.

Step 3
Create a new sheet named Top 10.  It should have the four top 10 lists as shown.

To get the Top 10 players for each category, add a filter to the Players sheet and sort it by that category. 

Copy & paste the Top 10 players to the Top 10 sheet.  Select the top 10 players Name, Team, and PPG - hold the Ctrl key to select non-adjacent cells.

Step 4
Create a new sheet named Team Lookup.  It will have a cell where the user can type the partial name of a team.  It will lookup the full Team name, Division, Coach, and Championships.  It will show player statistics for the team including Average Points/Game, Average Free Throw %, Average 2 Point %, Average 3 Point %, and Average Age.

Create a named ranged called Teams:  Select the Teams worksheet and select all (Ctrl-A).  Click in the name box and enter Teams in the name box.

Create the PPG named ranged:  Select the Players worksheet.  Select the PPG column and enter PPG in the name box.

Select the Team Lookup worksheet.  Here are some of the formulas to help you get started:

Cell C10:   =VLOOKUP("*"&C9&"*",Teams,1,FALSE)
Cell C11:   =VLOOKUP(C10,Teams,2,FALSE)

For the Coach and Championships, use the same formula as Division, but use 3 and 4 instead of 2 to pull from the 3rd and 4th columns.

Cell C15:   =AVERAGEIF(Team,C10,PPG)