1 / 42

MEAGER

MEAGER. Microsoft Excel Automated Grader. Applications of MEAGER. Microsoft Excel courses Microsoft Office Apps courses. Purposes. To reduce the time and effort required in grading assignments To grade assignments more accurately and in greater detail compared to hand grading via hard copy.

amable
Download Presentation

MEAGER

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. MEAGER Microsoft ExcelAutomated Grader

  2. Applications of MEAGER • Microsoft Excel courses • Microsoft Office Apps courses

  3. Purposes • To reduce the time and effort required in grading assignments • To grade assignments more accurately and in greater detail compared to hand grading via hard copy

  4. Text labels Numbers Formulas Worksheet names Fonts Face Size Style (B/I/U) Color What does MEAGER grade?

  5. Cell Alignment (L/R/C) Fill Color Indent level Orientation (tilt) Number format Currency, Percent Accounting, Scientific Etc. Borders Color Style solid broken Thickness Row Height Column Width Merged Ranges What does MEAGER grade? (continued)

  6. Charts Embedded objects Pictures Clip Art Org charts Word Art Drawing objects Shapes Lines Drop shadows Controls Buttons List Box Text Box, etc. What does MEAGER not grade?

  7. How does MEAGER work? • MEAGER is a Microsoft Access application • Attributes are extracted • from Excel workbooks • into Microsoft Access tables: _Cell _Format _Border _Formula _Row _Col _Worksheet

  8. Example Workbook

  9. Cell Table

  10. Format Table

  11. Formula Table

  12. How does MEAGER work? (continued) • After attributes are extracted from both • instructor’s correct workbook • a student’s workbook • and placed into tables • The resulting tables are compared • missing attributes are detected • unequal attributes are found

  13. Marking Errors • Errors are recorded in an Access table AND • Student worksheets are marked • Strike-through format is added to incorrect values and formulas • Comments are added to cells to describe errors

  14. Example Correct Workbook

  15. Student’s Marked Up Version

  16. Screenshots

  17. Grade Report

  18. Class Errors Report

  19. Grading Challenges

  20. Corner Sums: An Error? • Assignment instructs bottom right (F8) value to be sum of column F: =Sum(F3:F7) • What if student makes F8 the sum of row 8? =Sum(B8:E8) • Is the answer incorrect?

  21. Corner Sums: An Error? (cont’d) Note both column F and row 8 are totals The sum of column F and row 8 are both equivalent to =Sum(B3:E7) =Sum(F3:F7) and =Sum(B8:E8) are not identical, but ARE algebraically equivalent

  22. =B9+(10%*B9) =SUM(G3:G8) G3 is =3.2% * E3 G8 is =3.2% * E9 =B9*(1+10%) =3.2%*E9 E9 is =SUM(E3:E8) Similar Equivalent Formulas Correct Student Algebraic Simplification Needed Order of Operations: f(g(x)) vs g(f(x)) Sum of Products vs Product of Sums

  23. Symbolic CAS(Computer Algebra System) • Interpreter for symbolic manipulation of mathematical expressions (like Maple but free) http://www.mb.hs-wismar.de/Mitarbeiter/Pawletta/00Uwe/casE.html • Accepts identifiers consisting of at least one letter followed by several letters or digits. • All Cell refs from A1 to IV65536 are valid identifiers • Evaluates functions with same syntax as Excel:SQRT(x),EXP(x),LN(x),SIN(x),COS(x),TAN(x),ASIN(x), ACOS(x),ATAN(x),SINH(x),COSH(x),ABS(x) Includes function: Simplify(instr, outstr)

  24. Symbolic CAS(Computer Algebra System) Shortcomings for Excel formulas • Does not handle recognize ranges • Does not recognize many Excel functions, including common ones: Sum & Average • Does not recognize Excel function symbolslike $ and %

  25. Algorithm to Detect Equivalent Formulas Given student’s formula SF and correct formula CF For both formulas: Do Until no replacement SF or CF Replace Sum/Average functions in SF or CF with +, / ops For Each cell reference in SF and CF If the cell reference contains another formula Replace cell reference with nested formula Loop Remove $, percents (shift two decimals), remaining ranges in CF,SF Create string to be evaluated: CF || “-(“ || SF || “)” Use Simplify function to determine if result is zero

  26. Challenge #2Renaming Worksheets • Problem: • Assignment has multiple worksheets • Assignment requires three sheets to be renamed: Payroll, AP, AR • Student does not rename sheets, leaving nonempty sheets: Sheet1, Sheet2, Sheet3 • How does one match worksheets?

  27. Renaming Worksheets • Possible solutions to matching worksheets • Match by highest percent of matching cell values • Expensive: every pair of worksheets must be compared • Match based on worksheet statistics • Match closest count of nonempty: constants, formulas, etc. • Match based on ordinal position

  28. Renaming Worksheets • MEAGER solution • Match based on worksheet statistics • Match closest count of nonempty cells (constants and formulas) • When multiple worksheets have same number of nonempty cells, no rename is performed

  29. How to use MEAGER

  30. Before Using MEAGER • (The hard part) Instructor must gather all student Excel files • Files need to have unique names so they can be placed in the same folder • Copy the student folder for possible grading again • The files are marked up (comments are added, scores are added at the bottom of the worksheet) • Correct file should be in a different folder than student file directory

  31. Selecting Files • User must select the correct worksheet • click on browse file button (or type filename) • navigate to file • select correct excel file • Next select ANY student file • Use browse button or type filename

  32. Points to Deduct • 100 point grading scale • Enter number of points to be deducted for errors • Mistyped/missing labels or numeric constants • Incorrect formulas • Formatting errors

  33. Workbook Grading Rules • Extraneous values and formats ARE NOT deducted by default • The same amount is deducted for all types of formatting errors. • Formats applied to ranges (fill color, borders) are deducted for each cell in the range. • An incorrect number format applied to a large range would be deducted at each cell

  34. Options: Visible Marking • Displays the Excel window as documents are being graded • When cells are extracted, nothing changes • When grading comments are added and error cells are stricken • User can jump between MEAGER and Excel windows

  35. Options: Skip Format Grading • Only grades formulas and cell labels/number • Useful with first class assignments • Avoids grading more advanced features (for example, row height/col width) before being covered in class • Avoids “error overload”; hides details that might be more easily handled manually

  36. Options: Deduct Extraneous • To count any extra formatting or cells values against the student • If student enters name, class, date information, these will marked as incorrect (correct this manually in student grade)

  37. Error Analysis • Helps discover possible plagiarism • Considers a pair of students at a time • How many errors do they have in common? • More errors, more evidence of plagiarism • What percent of a student A’s errors were also made by student B?

  38. Student Pairs Common Errors Total Errs (for first file)

More Related