Question 1
In the Excel File, navigate to the "Course Grade" tab if you are not there by default.
1) Please label Column J "Total Score" and calculate the total score for each student using a formula in Excel and fill down this column with total scores.
2) Please label Column K "Percent Score" and calculate the percent score for each student using a formula in Excel that generates a number between O and 100. The total points available in the course are 1000.
"Even though it is possible to do it this way, please don't just format your cells for "percent." Write a formula that gets you a number between O and 100 without having to go back and format the cells as percent.
Don't forget to save your spreadsheet before continuing onwards.
Question 2
Please type here the formula you typed into J2 to calculate the total score for the student in Row 2.
Question 3
Please type here the formula you typed into K2 to calculate the percent score in Row 2 of the spreadsheet.
Remember, your formula should allow you to calculate percent as a number between O and 100, not O and 1, and should reference column J, or more specifically here cell J2
Question 4
Based on the data in Column J "Total Score", please sort the data on total score, and organize from highest to lowest. Enter the highest score here.
And please don't forget to turn in your Excel file at the end of the exam so we can see your sorted columns.
1. Columns sorted correctly (5 points)
2. Correct number entered here (5 points)
Question 5
Enter the lowest score here (from previous question).
Question 6
In this question you will make a chart to compare how the student who earned the highest total score and the student who earned the lowest total score performed on each of the different assessments in the course.
1) At the bottom of the Excel screen, please click on the "Grade Comparison" tab. Copy and paste the data of the student who earned the highest total score and student who earned the lowest total score into this new data sheet in the correct format to make the chart in the next part of this question.
2) Please convert the two student's scores to show them as a percentage of the total points available in each assessment category. (You can find the total points per assessment category in the header row.)
3) Please choose an appropriate chart type to compare how the two students performed on the different assessment categories. Show your chart in the tab for Grade Comparison. Your chart should have the following components:
1. Correct data (10 points)
2. Correct chart type (10 points)
3. Attention to aesthetics and appropriate labeling (10 points)
3) Please copy and paste your chart into answer blank here.
Don't forget to save your Excel sheet before moving on.
Question 7
We are interested in comparing the average total score of each section. Using the data in the "Course Grade" tab, please create a PivotTable (place this in a new tab). Set up your PivotTable to show the average of Total Score by section for each section.
Which Section has the highest average total score?
Section A
Section B
Section C
Section D
Question 8
1) At the bottom of the Excel sheet, please click on the "Section Comparison" sheet tab. Make a chart showing the average total score by section. Note, you should copy and paste the appropriate data into the sheet and show it in an appropriate table for making the chart.
2) Please choose an appropriate chart type to display the data. Your chart should have the following components:
1. Correct data (10 points)
2. Correct chart type (10 points)
3. Attention to aesthetics and appropriate labeling (10 points)
3) Please also copy and paste your chart into the answer blank here.
Don't forget to save your Excel spreadsheet before moving on.
Question 9
You suspect there is a correlation between participation and midterm exam score. In the fourth tab of the spreadsheet labeled "participation and midterm", please produce a chart that demonstrates this correlation with the axes set up appropriately for the most appropriate configuration of likely independent and dependent variables.
1) Paste the correct data into the "participation and midterm" tab
2) Choose an appropriate chart type to display the data. Your chart should have the following components:
1. Correct data (10 points)
2. Correct chart type (10 points)
3. Attention to aesthetics, and appropriate labeling and scaling (10 points)
3) Please also copy and paste your chart into the answer blank here.
Don't forget to save your Excel spreadsheet before moving on.