1 / 13

Excel at Feedback

Excel at Feedback. “Using Excel to Generate Individual Grade Sheets for Students & Posting to the Web “ James V. Pinto John D. Eastwood James.Pinto@nau.edu John.Eastwood @nau.edu (928)523-7356 (703)248-8782 College of Business Administration Northern Arizona University

autumn-lancaster
Download Presentation

Excel at Feedback

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel at Feedback “Using Excel to Generate Individual Grade Sheets for Students & Posting to the Web “ James V. Pinto John D. Eastwood James.Pinto@nau.edu John.Eastwood @nau.edu (928)523-7356 (703)248-8782 College of Business Administration Northern Arizona University NAU Box 15066 Flagstaff, AZ 86011

  2. Overview • Problem • Provide information • Protect privacy • Traditional Solutions • Posting • Database • Scissors

  3. Excel as a Database • Many faculty already use a“flat file” • Students in rows (records) • Columns for recorded grades (fields) • Three ways to use this as a database in Excel • Excel’s Data – Filter – AutoFilter • A template with internal links • Excel’s database functions (e.g., DGET)

  4. Data – Filter – AutoFilter • Show titles and one student’s grades • Useful when meeting with an individual student • Advantages and Disadvantages • Simplest – no work required • Printable, but cumbersome • To turn on the filter • Place your cursor on the title row • Follow the menu commands listed above

  5. Data – Filter – AutoFilter • Filter off:Filter on, one student selected:

  6. Template With Internal Links • Adds a separate worksheet for each student • Advantages and Disadvantages • No complicated formulae required • Laborious to build, but reusable • Difficult to modify • Show one student’s grades on one page • You create the format you desire • Print reports for one student or for everyone

  7. ‘Grades’ worksheet: Links on first student’s worksheet: Result:

  8. Formula to Return Grades Nested IF statement =IF($X2>GPA!$B$4,"A",IF($X2>GPA!$B$5,"B",IF($X2>GPA!$B$6,"C",IF($X2>GPA!$B$7,"D","F"))))

  9. Template With Internal Links Steps: • Develop your flat file from your syllabus • Develop the report for the first student • Edit-Paste Special-[Paste Link button] • Copy it n times, editing each • Edit-Replace command helps • Save workbook as a template • Enter or import a class roster into a copy of the template • Save the workbook

  10. Database Functions • Add only one worksheet (not n) and get the same results • Show or print a one page report for any one student • A simple macro will print reports for each student • Advantages and Disadvantages • Easier to create, easier to modify • Still reusable • A bit more complex • User must learn only ONE of Excel’s database functions • DGET(database range, field, criteria)

  11. ‘Grades’ worksheet: Links on report worksheet: Resulting report for first student:

  12. Macro for Printing ' ' GradeReport Macro ' Prints a one-page report for each student. ' Set the upper limit in the FOR statement ' to the number of students in the class. ' Before running the macro, click on cell D2 ' on the Grade Report worksheet, to make it ' the active cell. Sub GradeReport() Dim STUDENT As Integer For STUDENT = 1 To 4 ActiveCell.FormulaR1C1 = STUDENT ActiveWindow.SelectedSheets.PrintOut Copies:=1 Next STUDENT End Sub

  13. Posting to the Web • Written permission to post by • last four digits of student id # • or student-selected digits. • Extract w/ “=RIGHT(B2,4)” • Returns “1111” from Andrea Ables’s SSN (111-11-1111) • Excel’s File – Save as Web Page • Edit results as desired • Hints in proceedings • Example on web

More Related