Spreadsheet Assignment #5: Shooting Percentage (Feedback/Formative)

PROGRAMS: MS Excel

SKILLS: Input Formula

Shooting Percentage

Starting Line-up

You have been put in charge of tracking the points and shooting percentage for the starting line-up of your school’s basketball team. You have been provided with the stats from the first game and you want to create an Excel spreadsheet that will track the points produced and shooting average for each player as well as the points produced and shooting average of the team.

Player Stats

  • Begin by creating the title for your spreadsheet. Merge the appropriate cells and centre the text.
  • Next create the separate charts for each player. Merge the appropriate cells for the Name. Fill in the text for the rest of the chart (not numbers) and format the borders. Note that the cells for shot type, attempted, made, points produced and shot % are not merged. They simply have been edited to not show a border.
  • Note: It is easiest to completely format the first chart and then copy and paste it 4 times to create the 5 charts needed for the starting line-up. You can easily change the names and colours for each chart after you have pasted them. (This is more time efficient than re-creating the chart 4 times.) To individualize the charts;
    • Highlight the entire chart area and change the fill colour
    • Highlight the cell with the player’s name and change the name.
  • Next, enter the shots attempted and made for each player.

  • After the stats for shots attempted and made have been entered for each player you need to use formulas to calculate the points produced and shot % for each player.
    • For points produced you will need to create a formula that takes the shots made and multiplies it be the appropriate points awarded for that shot.

Remember

  • Free Throw = 1 point
  • Field Goal = 2 points
  • 3 Pointer= 3 Points (Duh)

Example: If Cell C4 is the amount of free throws sunk then the formula in cell D4 would be: =C4*1

  • For shot % you will also need to use a formula to calculate a percentage

Example: If B4 is attempted and C4 is made then the formula in cell E4 would be: =C4/B4 (remember to format the cell to show the number as a percentage with no decimals)

  • Auto sum the attempted, made, points produced, and shot % columns in each chart.
  • Once all of the player stat charts are finished label this worksheet so that it reads Player Stats.

  • Enter at least one piece of clip art on the Player Stats page.

Team Stats.

  • Create a chart based on the example below. Once again you will need to merge the cells for the Team Totals title and team shooting average title. Format the chart borders and colours as appropriate.
  • Note: All of the values in the Team stats are calculated by using formulas (This allow the team stats to automatically update if any player stats are changed). I have left the total shot % and total points visible in the example chart as a reference point for your spreadsheet. If your formulas do not give you these values then there is an error in your formulas.

  • Some of the formulas for the cells are very simple. For example, when entering a formula for the total shots attempted for Big Mac you would enter =B7 in cell you want to show that value.
  • The remaining formulas require adding the values of individual cells. =B7+C7+D7… or whatever individual cells you want to add.
  • Auto sum the columns to produce your totals.

Charts

  • Create a 3D pie chart that shows the % of total points that each player scored. Format the chart so that each pie piece shows the player and their point %.
  • Change the chart colours to reflect the colour of each player’s stat chart.
  • Place the Team stats and Chart together on a separate worksheet entitled Team Totals.

When you feel you are complete, check your letter against the checklist of requirements found here!

When you are satisfied with your assignment, hand in the completed document to the appropriate In Basket folder. (Remember to save it lastname,firstname…)