2 .Quarterback Efficiency

Formula 2: completion % = (completions/attempts) * 100,
Formula 3: touchdown % = (TD/attempts) * 100, and
Formula 4: interception % = (interceptions/attempts) * 100.
Formula 5 : Yards per Attempt = Yards / Attempts

1. Enter the formula (#2) to calculate completion % for Quarterback Topes and copy down the column for the rest of the players
2. Enter the formula (#3) to calculate Touchdown % for Quarterback Topes and copy down the column for the rest of the players
3. Enter formula (#4) to calculate Interception % in the appropriate place and copy to the other cells.
4. Calculate Yards / Attempt for Topes and copy down the column.
5. Now enter the formula (#1) to calculate points in the appropriate column using the four values you have just derived.
6. Sort the rows on the Points column.
7. Create a bar chart of points for schools. Hint:
   1. Highlight ranges for School and Points (include the title cell) and select the chart wizzard
   2. Choose the column chart and type in chart title and labels for X (school) and Y (points) axes
   3. Remove gridlines
   4. Place the chart in the spreadsheet
8. Format the chart:
   1. remove plot area border and background ("area")
   2. scale Y axis with a minimum of 80
   3. set font for axes to 12 point and align 90

3. Charting

Bar chart

1. Select data range and include titles
2. Use chart wizard and select column chart.
3. Remove gridlines and place the legend at the bottom
4. type in Titles for the chart and X and Y axes.
5. Place chart in current sheet

Line chart

6. Repeat selection from step #1
7. Now choose the simple line option for chart type
8. Remove gridlines and place the legend at the bottom as before
9. type in Titles for the chart and X and Y axes.
10. Place chart in current sheet
11. Format Plot Area: remove border and area colour
12. Change the Y axis number to read "thousands", so instead of 1,600,000 you would have 1,600 and the caption "thousands"
13. Change the colour and thickness of the data lines on the chart.
14. On the X axis, align the year number so that it's 90 to the axis (ie vertical rather than slanted).

4. Vlookup

1. Sum the totals for all 4 courses for each student in the Total points column.
2. For each student, calculate the average grade in the Average per student column (ie Total points/4)
3. Use the VLOOKUP function to assign letter grades in the Letter Grade column
   Note:
   Range lookup needs to find closest match, so TRUE should be entered