meager n.
Skip this Video
Loading SlideShow in 5 Seconds..
MEAGER PowerPoint Presentation
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 42

MEAGER - PowerPoint PPT Presentation

  • Uploaded on

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


Microsoft ExcelAutomated Grader

applications of meager
Applications of MEAGER
  • Microsoft Excel courses
  • Microsoft Office Apps courses
  • 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
what does meager grade
Text labels



Worksheet names




Style (B/I/U)


What does MEAGER grade?
what does meager grade continued

Alignment (L/R/C)

Fill Color

Indent level

Orientation (tilt)

Number format

Currency, Percent

Accounting, Scientific





solid broken


Row Height

Column Width

Merged Ranges

What does MEAGER grade? (continued)
what does meager not grade

Embedded objects


Clip Art

Org charts

Word Art

Drawing objects



Drop shadows



List Box

Text Box, etc.

What does MEAGER not grade?
how does meager work
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


how does meager work continued
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
marking errors
Marking Errors
  • Errors are recorded in an Access table


  • Student worksheets are marked
    • Strike-through format is added to incorrect values and formulas
    • Comments are added to cells to describe errors
corner sums an error
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?
corner sums an error cont d
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(F3:F7) and =Sum(B8:E8) are not identical, but ARE algebraically equivalent

similar equivalent formulas


G3 is =3.2% * E3

G8 is =3.2% * E9



E9 is =SUM(E3:E8)

Similar Equivalent Formulas



Algebraic Simplification Needed

Order of Operations:

f(g(x)) vs g(f(x))

Sum of Products vs Product of Sums

symbolic cas computer algebra system
Symbolic CAS(Computer Algebra System)
  • Interpreter for symbolic manipulation of mathematical expressions (like Maple but free)

  • 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)

symbolic cas computer algebra system1
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 %
algorithm to detect equivalent formulas
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


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

challenge 2 renaming worksheets
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?
renaming worksheets
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
renaming worksheets1
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
before using meager
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
selecting files
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
points to deduct
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
workbook grading rules
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
options visible marking
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
options skip format grading
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
options deduct extraneous
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)
error analysis
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?
student pairs common errors
Student Pairs Common Errors

Total Errs (for first file)