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.
Note: As of the writing of this assignment, Microsoft Excel for Mac does not
include the option to create a PivotChart. You can finish all the rest of the
assignment and then open the file on a Windows computer which has Excel to
finish the last step. It should only take a few minutes once you have access
to Excel on Windows.
Worksheets
There should be three worksheets in your Excel workbook when you are finished.
- CIS 123: This worksheet contains your grades from the CIS 123 course.
- Tables: This worksheet contains a table used to calculate a letter grade.
- PivotChart: This worksheet contains a PivotChart showing how points are distributed across categories of assignments.
Requirements
- You probably want to have the sample CIS 123 worksheet
viewable as you create your version.
- 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.
- Create a new blank Excel workbook.
- (1 pt) Change the name on the first worksheet from Sheet1 to CIS 123.
- (1 pt) Create a second worksheet if there is not already one created. Change its name
from Sheet2 to Tables.
- (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]
- (1 pt) Modify the document properties for the workbook so the title property is
CIS 123 gradebook assignment. [Image of document properties panel]
- (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.]
- (2 pts) Merge cells A1 through D1 so they become one cell. Leave the text in the cell left aligned instead of centering it.
- (1 pt) In cells A3 through D3 enter the text:
- A3: Category
- B3: Item
- C3: Possible
- D3: Earned
- (1 pt)Make the text in cells A3 through D3 bold.
- (1 pt)Give cells A3 through D3 a bottom border.
- (1 pt)Align the text in cells C3 and D3 (but NOT A3 or B3) to the right.
- (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.
- (1 pt) Add a bottom border to cells A30 through D30.
- (1 pt) Auto resize columns A and B so the category and grade item names fit in their columns properly.
- (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.
- (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.]
- (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.
- 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.
- (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.
- (1 pt) Format the grade percentage in cell C34 as % format with 1 digit after the decimal point.
- (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.
- (1 pt) Format the grade percentage in cell C35 as % format with 1 digit after the decimal point.
- (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):
- (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.
- (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.
- (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.
- (1 pt) Center align the letter grades in cells D34 and D35.
- (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
- (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.
- (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
- Test your workbook before you submit it. Try some different values for a few items to
make sure your formulas and conditional formatting work.
- Submit your finished .xlsx format file to the appropriate dropbox in Desire2Learn.
Examples
- CIS 123 worksheet showing an A
- Tables worksheet
- Document properties panel
- CIS 123 worksheet showing a B
- CIS 123 worksheet showing a C
- CIS 123 worksheet showing a D
- CIS 123 worksheet showing an F
- Pivot chart
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.