Using Excel 2007 for Calculating Grades
& Student/Parent Database Information
This assignment is to construct a spreadsheet that is appropriate for your subject and grade level that will calculate your grades. This example will weight 3 types of grades: Homework (50%), Quiz (30%), Test (20%). You use the types of grades that are appropriate for your subject area & course. Also, it will drop the lowest test score. You must use the "drop grade" feature & have 2 different types of grades to complete this assignment. Use at least 10 names and addresses of your choosing for this section & the Mail-Merge Section. Do not use the same names & grades used in the example(s). Be sure to round all of the averages to 0 decimals prior to posting.
Before you start, read “Cheating” located the SPSE 3220/5220 main page. You are required to use the formulas within Excel to make all the calculations.
This database will allow you to maintain “contact information” on your student’s parent(s). This information will used in the Mail-Merge Section to write a personalized letter to each of the parent(s).
When you have completed the assignment, post it to your website as an Excel document – named grades.xls.
Views of the Example Spreadsheet:
Use this example as a pattern for your completed spreadsheet (xls-file) & to see the formulas click on Spreadsheet Example with “rounding” formulas ( XP, 2003, or 2007 version). This view includes the formulas shown in Row 2. For example, click on Cell J2 to the see the formula "=ROUND(AVERAGE(F2:I2),0)" for that cell. The “Round” feature will round to 0 decimals so you do not have to round manually after you merge the documents in “Mail-Merge” section. Once you get the formulas typed, then use the “fill-down feature” to fill-in the other formulas in each row. To view all of the Formulas, Click [Tools][Options][View][Formulas][OK]. Use the same key sequence to change back to the regular format. See below for Instructions for Excel 2007 Slide 16.
Instructions
for Excel 2007:(Do not use the example or the formulas shown in
this PowerPoint Presentation)
Streaming Video (Impatica) version
When the
presentation comes up it is self-timed, but you can use the navigation bar in
the lower left corner to move forward or backward to view a slide again. This
PowerPoint presentation was converted to “streaming video” by Impactica & reduced
the PowerPoint file size from 5,083 KB to 708 KB which allows it to run faster
over the Internet. There is some lost in quality.
PowerPoint Version
saved as an XP so you can download it.
PowerPoint Version saved as pptx (2007) format
Fundaments
of Excel XP/2003 Online Slide Show:
(Do not use the example or the
formulas shown in this PowerPoint Presentation)
To view the
“Fundamentals of using Excel” PowerPoint presentation click Excel Slide Show
–Streaming Video (Only works
properly on Internet Explorer) . . When the presentation comes
up it is self-timed, but you can use the navigation bar in the lower left
corner to move forward or backward to view a slide again. This PowerPoint
presentation was converted to “streaming video” by Impactica & reduced
the PowerPoint file size from 5,083 KB to 708 KB which allows it to run faster
over the Internet. There is some lost in quality.
To view or download
the same slide show as a regular PowerPoint file click Excel Slide Show – PPT File.
To view or download the same slide show as a regular PowerPoint file click Excel Slide Show – HTM File
Tips on Using Excel:
· Click Fill-Function to see how to avoid retyping the formulas so many times.
· When using the Fill-Function, enter your formula by clicking the Green-Check so that the cursor will stay in the same cell ready to be filled-down.
· When entering the headings, use the right-arrow key to “enter” the data & the cursor will be in place for the next entry.
· Do not skip any Columns or Rows.
· Make you columns fit the information by clicking in the longest cell in the column & click [Home][Format][Auto fit column Width].
· Change your spreadsheet to landscape mode because it will wider than tall, [Page Layout][Orientation] [Landscape]
· Keep your columns as narrow as possible so that you do not have to scroll to the right & left so much.
· To view all of the Formulas, Click [Formulas][Show Formulas]. Use the same key sequence to change back to the regular format.
·
If you spreadsheet
gets too wide, then you can split the screen. Click in the cell where you
want the split to be & Click [Window][
Note:
I clicked on Cell D2, [View] ribbon [
Using Excel as database for mail-merge:
1. Use Row 1 (A1, B1, C1…) for the titles or categories, such as, Student Name, Student ID number, etc.
2. Do not skip any Columns or Rows. When using mail-merge with WORD, if you skip Rows it merges the blank line into your document.
3. Check the Spelling [F7], or [Tools][Speller & Grammar] of names & make sure you have the proper spacing within each cell. Word merges the cell exactly as you have them typed.
Learning Objectives for this Section (Excel grades):
1. Construct an Excel worksheet for calculating grades with weighted averages & drop scores. The worksheet will contain parent(s) contact information for use in Mail-Merge project.
2. Post the spreadsheet/database on the student’s website as an Excel file as grades.xls.
3. Learn to use the Microsoft Excel 2003/XP & 2007 features:
a. formulas to AVERAGE, SUM, min, drop-score, Letter grade calculation, COUNT, ROUND & basic math functions
b. “Fill” feature to insert formulas into multiple cells without having to type each 1 separately
c. color cells, columns, or rows
d. save Excel file for use on webpage