Goal:
To demonstrate basic spreadsheet design with simple formulas, using Excel as the tool.
Introduction:
A spreadsheet is simply a tool that allows one to enter data or formulas into cells, and do calculations. Keeping track of students' grades is a responsibility of every teacher. A spreadsheet is a great way to enter, organize and calculate grades.
In this project you will be asked to write formulas to calculate different aspects of student grades including total points, averages and percentages. You will be asked to use many different tools that Excel has to offer to complete the task. You will need to work together and use help menus and to accomplish the task. Using right click with your mouse will be crucial to working efficiently.
Be sure to 'estimate' your answers. It is very easy to type in a formula that has errors, and the answer is obviously wrong. I treat this like giving change in a store. You can NOT be off. If you cannot figure out a formula, I'd much rather have you ask for assistance than turn in a sheet that has an incorrect answer.
Procedure:
This is a list of skills I want you to be able to demonstrate in this project:
- Enter data
- Write formulas
- Lock cells using $ signs in some formulas
- Highlight and sort data in cells
- Enter comments in certain cells
- Format cells to:
- Change borders
- Change color
- Center data in cells
- Change data to percent form
Step 1 Getting Started
Do the following using the image below:
- Type the data into an Excel spreadsheet.
- Do not enter any numbers into the totals column.
- The entries in the total column will be formulas
Step 2 Writing Formulas
We want to know how many points a student earns. Do the following to achieve this
- We must write a formula
- To write a formula you MUST type an "="
- To add up Janis Joplin scores, in the cell I2, type = c2+d2+e2+f2+g2+h2 and press enter
- Notice that in the cell I2, the total for Janis is displayed.
- Change one of the numbers in her row and the total changes
- Write formulas that will total each student's points. Don't forget the "=" sign
Step 3 Adding a new row/column
In a gradebook what each assignment is worth is important. Our gradebook thus far does not include any way to input the value of each assignment. To do this we need to insert a row and column into the spreadsheet. To do this follow the directions below:
Insert row
- Right click on the "2" next to Janis Joplin.
- Choose "insert" and left click.
Insert column
- Right click on the letter C above the word "syllabus"
- Choose "insert column" and left click
- In the cell C2 type Points Poss in the cell so it looks like the spreadsheet below.
Step 4 Adding a new columns...% , grade
Teachers usually want to calculate a pecent so they can have a standardized grading scale. Follow the directions below to add and calculate percentages for each student.
- In the I1 column next to total points, type in the word "percent"
- In I3 for Janis Joplin write a formula that will calculate the pecentage
- Recall from your math classes that this means to take the points earned by Janis and divide it by the total points.
- So it will look like " =h3/h2"
- The / or "forward slash" is the division symbol for Excel. Don't forget the = sign!
- Write the percent formulas for the rest of the students.
- Once a percent is known, a grade may be assigned. Add the "grade" column in the "J" column
- Excel can automatically assign a grade based on percent but it is an ADVANCED SKILL AND IS EXTRA CREDIT
- For this project you may just type in a grade and not have it assigned by formula
Step 5: First and Last names/Adding more students
One thing that can be very helpful is to separate the first and last names into different cells. Do the following:
- Insert a column so the 1st column is for "last name"
- Change the 2nd column to "first name"
- Now edit the 5 names to separate the first and last names in the spreadsheet
We want our gradebook our grade book to have a total of 10 names
- Add any names and appropriate scores you want
- Keep the scores realistic (consider the total points possible for any given assignment)
- I should be able to show the names to the assistant-principal without any problems
Review your work:
- Check the all your formulas for accuracy
- Do the answers seem reasonable? If not check your formula for accuracy
- Change a number for any score for someone you have added. All your formulas should calculate for you!!
- Excel is SMART!
Step 6: copying formulas and absolute referencing
Adding all the formulas for the additional 10 names is a real pain. There is a way around this. Follow the directions below to speed up this process!
- Right click on Janis Joplin's total points and a menu should come up
- Choose copy
- Hightlight (left click and drag) all the numbers in the total points column
- Right click and paste. It creates all the formulas
- SAVE YOUR SPREADSHEET
I always save after completing an operation that effects a large amount of data, as long as it is accurate of course.
Repeat this for the Percent column. Prepare youself for a problem.
- Notice that the precents are not correct
- The problem is that we want to divide by the cell I2 every time and when we copied and pasted, the I2 changed
- There is a way to do this by copy/paste, but it will require going back to cell J3 for Janis Joplin
- Re-type the formula so it looks like this:
- '=I3/$I$2 "
- The $ "lock" the cells so that when you copy-paste the cell I2 will not change
- Now copy/paste this formula and it will work throughout the spreadsheet
- The use of $$ in formula work is called absolute referencing and is an important Excel concept
Step 7: The Final Gradebook
You now have nearly all the main pieces in place. Now it is time to make it look readable and pleasing to the eye. Making the spreadsheet so a person can easily read it is important. To do this we use the following:
- Correct titles
- Color groupings for like data
- Column headings with unique formatting (color, bold, centering)
- Adding borders to separate cells
- Correct formatting (percents, correct rounding)
Match what you see in the image below. Notice I moved some columns around so it is easier for a student to find his/her grade.
We will practice this skill in class
Notice the little red triangles by the names.
- Right click on the cell and choose "insert comment"
- Insert a comment for the first 5 students
- Make sure the include only appropriate comments since our work will be shared!!
Also notice how your formulas have changed.
For example the percentage for Janis Joplin is now written:
=G5/$G$4
Excel is very smart and adjusts cell references when you cut and paste!
Step 8: Sorting the Data
One powerful feature of Excel is the ability to sort and organize data. Let's say we want to have a list printed that shows the students in ranked order by total points. This will tell us who performed the best in the class. It is very important that we keep all the students' scores with the same student. So, if we perform a sort we must highlight all the names so the data stays with the same person. Highlight all the names and then click on "Data" on the toolbar.
See picture below
Now choose:
- "Sort"
- "Column A" for last name
- "Ascending" and it will sort the data from smallest to largest. See below
Grading Requirements:
REFER TO THE PICTURE IN STEP 7 FOR THE FOLLOWING:
- A minimum of 10 names in your book
- Make the assignments the same as in step 7.
- The total points and percent columns must be formulas
- The percent cells must be formatted as percents
- Percent cells must use absolute referencing (that is they must use $)
- Borders must be enabled to provide the same look in the image above (step 7)
- Color groupings must be match exactly as shown in the image above. The first 5 names must have a comment inserted (note the red triangles in the corners)
- You may type the correct grade into the grade column and extra credit will be given for formulas that determine the Grade. Try looking up "If" statements or V-Lookup tables for help
- Perform a sort on demand. I will grade this project at your computer and I will ask you to sort your gradebook data in some way.
This is quite a complex project for our first venture into spreadsheets. We will work over several days and I want you to work together on it. My goal is for everyone to have a quality product that they can be proud of.
Mr. A
