Spreadsheet assignment: gradebook

This assignment is designed to use certain features of Excel and also help you keep track of your grade in this course.

This problem uses a number of advanced features of Excel and it is expected that there will be questions. Keep in mind that the instructor's CIS 123 website has a FAQ sheet for using Excel, there will be a demonstration of Excel in one of the optional class sessions, and there are many tutorials on how to use specific features of Microsoft Excel on the Web. There is also a discussion forum on Desire2Learn for asking questions about assignments.

There are sample pictures linked within this document that show what most of the project should look like. Keep in mind that many cells must be formulas and that typing in numbers to match a picture instead of using a formula does not fulfill the requirements.

Note: One common mistake is to misuse functions such as SUM. If you want to add a contiguous group of cells, such as D4 through D10, then SUM is perfect since you can specify the range and it will automatically adjust if a row is inserted or deleted within that range. The formula would look like: =SUM(D4:D10). That is much clearer and better than the error-prone: =D4+D5+D6+D7+D8+D9+D10. The misuse comes when people try to use SUM for everything. If you just want to add cells E7 and E12 use: =E7+E12. If you want to multiply them use: =E7*E12. Do NOT use =SUM(E7+E12) or =PRODUCT(E7*E12). In those cases SUM and PRODUCT are not doing anything other than wasting computer processing power. If you do not understand this, please ask the instructor for clarification. Points will be deducted for misusing the SUM and PRODUCT functions.

• Create basic formulas to calculate values
• Modify worksheet properties (such as worksheet name)
• Create lookup tables
• Use lookup tables
• Modify document properties
• Use selection in formulas (IF)
• Create a pivot chart
• Use conditional formatting

1. You probably want to have the sample CIS 123 worksheet viewable as you create your version.
2. Do NOT work closely together or copy or share work with other students. The penalties can be quite severe. You are welcome to ask all the questions you want and to seek help from the instructor or LSC tutors.
3. Create a new blank Excel workbook.
4. (1 pt) Change the name on the first worksheet from Sheet1 to CIS 123.
5. (1 pt) Create a second worksheet if there is not already one created. Change its name from Sheet2 to Tables.
6. (1 pt) Modify the document properties for the workbook so the author property is your full name (first and last name minimum). [Image of document properties panel]
7. (1 pt) Modify the document properties for the workbook so the title property is CIS 123 gradebook assignment. [Image of document properties panel]
8. (1 pt) In cell A1, enter the text: CIS 123 Grades for Floyd Smoot [Change the name to your actual name. Floyd Smoot was the conductor on the Cannonball Express.]
9. (2 pts) Merge cells A1 through D1 so they become one cell. Leave the text in the cell left aligned instead of centering it.
10. (1 pt) In cells A3 through D3 enter the text:
    • A3: Category
    • B3: Item
    • C3: Possible
    • D3: Earned
11. (1 pt) Make the text in cells A3 through D3 bold.
12. (1 pt) Give cells A3 through D3 a bottom border.
13. (1 pt) Align the text in cells C3 and D3 (but NOT A3 or B3) to the right.
14. (3 pts) Type the text for all of the grade items and totals into column A, from cell A4 through A36. Also type in the point values for the grade items in cells B4 through B31. To save you typing, you may copy and paste the following text by placing your cursor in the box below. selecting all the text (Ctrl-a), copying it (Ctrl-c), and then pasting it (text only, Ctrl-v) in cell A4 of the CIS 123 worksheet.
    Quiz Quiz 1 20 Quiz Quiz 2 29 Quiz Quiz 3 28 Quiz Quiz 4 20 Quiz Quiz 5 6 Quiz Quiz 6 12 Quiz Quiz 7 30 Written Written 1 10 Written Written 2 10 Written Written 3 10 Written Written 4 10 Written Written 5 10 Written Written 6 10 Written Written 7 15 Discussion Discussion 1 20 Discussion Discussion 2 20 Discussion Discussion 3 20 Discussion Discussion 4 20 Project Project 1 50 Project Project 2 50 Project Project 3 50 Project Project 4 50 Project Project 5 50 Project Project 6 50 Project Final project 100 Exam Midterm exam 100 Exam Final exam 100 Total Total so far Grade so far Grade overall
15. (1 pt) Add a bottom border to cells A30 through D30.
16. (1 pt) Auto resize columns A and B so the category and grade item names fit in their columns properly.
17. (2 pts) Enter a formula in cell C31 that will sum up all the values in cells C4 through C30. You should use the SUM function with a cell range in your formula. That would make sure that the formula would automatically update itself if items were inserted or deleted from the grade item list. This formula shows the total possible points for the course.
18. (2 pts) Enter a formula in cell D31 that will sum up all the values in cells D4 through D30. You should use the SUM function with a cell range in your formula. That would make sure that the formula would automatically update itself if items were inserted or deleted from the grade item list. This formula shows how many points you have earned so far in the course. [Note: If you got the formula correct for cell C31, you should be able to copy and paste it into cell D31 to get that formula.]
19. (3 pts) The next formula (cell C33) (and the alternative formula in the following list item) includes a built-in function that is a little tricky. We want to add up all the possible points for assignments that have received a grade so far. The formula we want is named SUMIF. The SUMIF function takes three arguments: SUMIF(range, criteria, sum_range). Here's what they are:
    • range: These are the cells we are checking to see if a grade has been recorded. Those cells are in column D, so this value should be set to be the range of cells from D4 through D30.
    • criteria: This is the tricky part of this formula. We want to add cells in column C where the cell in column D is not empty. So for criteria, we need to specify "not empty". In the case of Excel, we will be specifying "less than or greater than empty." Technically that looks like: <>"". We can leave out the "" at the end, but we have to put quotes around the <> in the formula. So, the criteria ends up being: "<>"
    • sum_range: These are the cells we want to sum if the corresponding cell in column D is not empty. Those cells are in column C, so this value should be set to be the range of cells from C4 through C30.
20. Note: An alternative to the formula in the previous step is to use an array formula.
    • We first want to use the IF function. It looks like this: IF(condition, value1, value2)
      where the cell becomes value1 if the condition is true, or value2 if the condition is false.
    • In this case, if a cell in column D contains nothing, we want the value 0, otherwise we want the corresponding value from column C, but we have to specify the range of cells we are working with rather than an individual cell.
    • condition: range1="" (where range1 is the cell range we are checking in column D)
    • value1: 0
    • value2: range2 (where range2 is the cell range we are adding in column C)
    • The above can give us a series of value, but to add them up they must be be sent to the SUM function, which looks something like this now: =SUM(IF(condition, value1, value2))
    • This formula must be entered as an "array formula" since we are working with ranges as if they are single cells. The way that is done is by pressing Ctrl-Shift-Enter to complete the formula rather than just Enter.

21. (2 pts) Enter a formula into cell C34 showing the grade so far. This is simply the total points the student has earned divided by the total possible points so far. This grade shows how well you are doing up to this point in the course.
22. (1 pt) Format the grade percentage in cell C34 as % format with 1 digit after the decimal point.
23. (2 pts) Enter a formula into cell C35 showing the overall grade. This is simply the total points the student has earned divided by the total possible points. This grade will always look low until near the end of the course since items not yet graded are counted as 0. It basically shows the grade you would get if you didn't do any further work in the course.
24. (1 pt) Format the grade percentage in cell C35 as % format with 1 digit after the decimal point.
25. (1 pt) [Note: This step and the next involve entering data and giving a range of cells a name. It does not involve using Insert, Table from the Excel menu). We need letter grades for the percentages. For this we will use a table (NOT an Excel table) and a lookup function. The first step is to create a table (meaning columns of related values - NOT an Excel table). Go to the Tables worksheet and enter the following data starting in cell A1 (Note: you can copy and paste the text below into cell A1):
    GradeTable 0 F 0.6 D 0.7 C 0.8 B 0.9 A
26. (3 pts) Define a name for the data in the table you just created. Call the table GradeTable and make sure it has Workbook scope (which means it can be seen throughout the workbook - not just on the worksheet it is located on). By making this name workbook scope, it keeps clutter down on the worksheet with the CIS 123 grades and also makes it easy to use if you add gradesheets for other courses. If you have selected the data from the table correctly when you go to define the name, Excel should have guessed the correct name since it is in the first cell in the row above the data. Warning: The defined name should not include the first row since that is a heading - not data. Points will be deducted for including the first row as part of the name definition.
27. (4 pts) The next step in getting letter grades is to use the VLOOKUP function to look up the percentage value and translate it into a letter grade. We will create the letter grade for the "Grade so far." Select cell D34 on the CIS 123 worksheet. Enter a formula that contains the VLOOKUP function (remember that formulas start with the = sign). The VLOOKUP function takes four arguments, although the fourth argument is optional and we don't actually need to include it in this case. Here's what the formula with the VLOOKUP looks like conceptually:
    =VLOOKUP(lookupValue, tableName, columnNumber, rangeLookup)
    • lookupValue: This is the value we want to translate. In this case it is the grade percentage from column C, so enter that cell reference.
    • tableName: This is the table we want to look up the value in. Enter the table name, which you just defined in the previous step.
    • columnNumber: This is the column number in the table of the value you want returned. In the grade table, the value you are looking up is in column 1. The value you want returned is in column 2.
    • rangeLookup: This value should be FALSE if the number we are looking up should be an exact match. Since we are looking up a value in a range, you want to use the value TRUE. If you omit this value, Excel will assume TRUE. If we used false, then we would need rows in the grade table for every possible percent value. Since CIS 123 is a 900 point course, that means you would need 900 rows in the grade table. By using the TRUE value, the grade only has to list the boundaries where the letter grade changes.
28. (1 pt) Now that you have a letter grade in cell D34, you should be able to put a similar formula in cell D35 that is based on the value in cell C35. In fact, you should be able to copy the formula from D34 into D35.
29. (1 pt) Center align the letter grades in cells D34 and D35.
30. (4 pts) The next step is to apply conditional formatting to cell D34 to alert us to our current grade status. We want the cell to have a different background color depending on the letter grade. Add the following three conditional formats to cell D34:
    • If the cell contents are less than D, set the format to a custom format with a fill color of green
    • If the cell contains D, set the format to a custom format with a fill color of orangey/yellow (see the example)
    • If the cell contains F, set the format to a custom format with a fill color of red
31. (2 pts) The next step is to add conditional formatting to cell D35 that does the same as the conditional formatting did for cell D34. You can go through the steps and add each conditional formatting rule to cell D35, or you can copy cell D34 and then use "Paste Special" on cell D35 to just paste the Formats.
32. (5 pts) Now it is time to create a PivotChart on a new worksheet. [Please note that as of this time, Excel on the Mac does not have this feature. If you are on a Mac, you can save your file, take it to a Windows machine which has Excel and add the PivotChart there. It should only take a minute.] Select all the headings and data from cell A3 through cell C30. Then click on the Insert tab and click on PivotChart. Choose to create the PivotChart on a new worksheet. We want to see how important each category is to the overall grade, so choose Category and Possible as the fields to add to the chart. The default Column chart is not a good representation, so click on the Design tab and change the Chart Type to a Pie chart. To make the chart more readable, right click on the chart and add Data Labels. Then right click on the chart again to format the data labels. Have the data labels show the percentage, category name, and leader lines. Get rid of the title since it is just in the way. Check the example gradebook chart to see how it should look. Rename the new worksheet: PivotChart
33. Test your workbook before you submit it. Try some different values for a few items to make sure your formulas and conditional formatting work.
34. Submit your finished .xlsx format file to the appropriate dropbox in Desire2Learn.

There should be three worksheets in your Excel workbook when you are finished.

1. CIS 123: This worksheet contains your grades from the CIS 123 course.
2. Tables: This worksheet contains a table used to calculate a letter grade.
3. PivotChart: This worksheet contains a PivotChart showing how points are distributed across categories of assignments.

There are 50 points total available for this assignment. There ia a discussion forum available on Desire2Learn for asking questions about assignments. There is also an Excel FAQ page available on the CIS 123 web site. Class is also a great time to demonstrate Excel usage questions, so feel free to bring in questions to a class session.

1. CIS 123 worksheet showing an A
2. Tables worksheet
3. Document properties panel
4. CIS 123 worksheet showing a B
5. CIS 123 worksheet showing a C
6. CIS 123 worksheet showing a D
7. CIS 123 worksheet showing an F
8. Pivot chart