Before you start this project, send me a note in Edmodo explain order of operation. Explain this formula to me: =(b4+c4+d4+e4)/($b$20+$c$20+$d$20+$e$20)*$j$20
Project 2: Course Grades Analysis for Psychology 200
PR-D Course Grades Analysis.xlsx
In Project 2, you will set up a grades worksheet for Psychology 200, calculate the grades earned by each of the students enrolled in the course, and then create a Subtotals list that counts the number of students who earned each grade (for example, A, B, and so forth). You will then display the data from the Subtotals list in a pie chart. The goal of Project 2 is to show you how they can use the Subtotals function to analyze data entered in a worksheet and then display this data in chart form. You will also learn how to use the Lookup function to match values entered in a lookup table with the appropriate values entered in a worksheet. Before you begin Project 2, you might want to review the following topics.
Setting Up the Worksheet
Be sure to carefully check the values enter enter in the worksheet against the values entered in Figure D-10, on page 85 of the text. If a value is entered incorrectly, you will not achieve the required results.
Understanding Weighted Values
Review the concept of weighted values, as calculated in Steps 2 through 7 on page 84 of the text. Make sure that the total points a student earns in any given category (for example, 60 points for Assignments) are divided by the total points possible (for example, 80 points for Assignments) and then multiplied by the weighted score (for example, 50 points for Assignments). The resulting value represents the student’s weighted score for the category.
This method of weighting scores enables instructors to total the weighted scores to calculate the number of points out of 100 that each student earns. This number of points (for example, 87) represents a grade of 87% for the course. A lookup table is then used to assign a letter grade to the student (for example, B+ if the percentage is greater than or equal to 87% and less then 90%).
In Steps 2 through 6, on page 84 of the text, you are instructed to enter a formula to calculate the total points earned by the first student on the list (Michael Banks) and then to copy the formula for the remaining students in the class. Note the use of [F4] to insert dollar signs ($) in the formula so that the references to cells in rows 20 and 21 are absolute. Make sure you understand that these references must be absolute so that the total assignment score earned by each student is divided only by the values in row 21.
Entering the Lookup Formula
In Steps 1 through 3, on page 86 of the text, use the Lookup function to calculate the letter grade earned by each student in the class. Here are the tasks performed by the Lookup function:
- Searches the ranges in a lookup table
- Finds the range that includes the value in a designated cell (for example, cell M5)
- Enters the values for that range (for example, A-)
You can use the Lookup function for other purposes. For example, you can create a list of products in Sheet1 and then create a lookup table in Sheet2 that contains a price list. The Lookup function can then be used to enter a specific price, depending on the product entered in a specific cell in Sheet 1.
Note that array refers to the range of cells that contains the lookup table (cells A2 to B12 in the Lookup sheet).
Creating a Subtotals List
What is the purpose of the Subtotals list?—to display the number of times each letter grade appears in column M. Be sure that you compare your collapsed Subtotals list against Figure D-14, on page 87 of the text, to ensure that the correct breakdown of grades is achieved.
Creating the Pie Chart
You will create a pie chart from the Subtotals list when it is collapsed to level 2. If you click 3 in the Grouping frame, the pie chart will display different results. Be sure to click 3, check how the pie chart appears, then click 2 again so the pie chart appears as shown in Figure D14.
Using Table Formats
In Step 7, on page 88 of the text, you are instructed to apply one of Excel’s preset table formats to the worksheet. Be sure to experiment with the various table formats available. Notice how such formatting can make worksheet data more readable or it can obscure data unnecessarily. Make sure you always format a worksheet so that the data can be easily read.
PRINT AS INSTRUCTED AND SHOW ME!
Independent Challenge 2
PR-D My Grades Analysis.xlsx
Create a course grades analysis for at least 15 students taking a course of your choice. Create this for a course you are currently taking. Use JMC to see the categories and weights assigned. SHOW THIS to the teacher of the course for EXTRA CREDIT. Take time to determine the appropriate names and percentages required for each of three grade categories. Grade categories could include Assignments, Quizzes, Exams, or other categories chosen by the you. Be sure to adapt the course grades analysis you created for Project 2. You might want to change the grade allocations in the lookup table to more closely match the allocations used in the courses you take. For example, an A might be assigned to a total of 86 or higher, and the A- and A+ grades might not be assigned.
Send the finished workbook to me as an attachment in an Edmodo note.
Illustrated below is a sample course grades analysis.