+ Reply to Thread
Results 1 to 12 of 12

Autograding Excel spreadsheets

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Autograding Excel spreadsheets

    I am an MS Office instructor, and am exploring options for autograding Excel spreadsheets (and Word documents). This must be capable of dealing with variations of formulas (e.g "=B3*B4" is just as correct as "=B4*B3"). There are instances where I need to assess/grade the cell formatting, headers/footers, formulas, column widths, etc). I know that the technology exists, because I have taught courses where the publisher has provided autograding tools.
    Is anyone familiar with where I may find a customizable autograding app, or available resources to create my own. I am assuming that the app would compare against a Master Solution file, and mark the differences.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,383

    Re: Autograding Excel spreadsheets

    If you Google: automatically grading excel spreadsheets, a lot of information comes up. I don't know if it will help directly but it might aid your research.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Autograding Excel spreadsheets

    Thank you for your response. My research always leads me first to Google, and when I cannot find anything in Google's results, I enter the forum with my specific needs. Unfortunately, the Google results do not seem to provide the assistance I need. I'm hoping that someone on the forum will know of, or has used a method of autograding Excel formulas, etc. It is more complex than first thought, because of all the 'right' variations of composing a formula.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,010

    Re: Autograding Excel spreadsheets

    .
    I suggest it would be easier to create if the answers were multiple choice rather than having the student manually write out the formula. That
    will allow you to create a list of "correct" answers to check against.

    The few examples of 'auto correct exams' I located on the internet were created in this same manner.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,383

    Re: Autograding Excel spreadsheets

    Your question is very "open", hence me looking for ideas on Google and suggesting that approach to you. Not everyone uses Google before coming to the forum, even for relatively simple questions, perhaps because they don't know how to phrase their question or what to search for. Sorry if I’m teaching you to suck eggs

    The example you have quoted is for a VERY simple question but even that has at least two correct answers. Let's say you want to sum a range dependent on another range; you could use SUMIF, or even SUMIFS with a single argument ... or maybe even SUBTOTAL. And, in each case, you might refer to specific ranges or whole columns. Or, I quite like using SUMPRODUCT ... and whole column references are acceptable although not advisable. How do you hope to cope with the myriad of solutions that would address a problem?

  6. #6
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Autograding Excel spreadsheets

    Logit,
    Whenever possible, I use a M/C or T/F quiz questions, but there are times when I really want to use a practical approach, and let them compose their own formula. It is these practical activities with which I am struggling.

  7. #7
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Autograding Excel spreadsheets

    TMS,
    You have identified my problem - there are indeed a myriad of solutions for any given question. Within reason, my desired approach would be able to accommodate these. I have used autograding exercises provided by the publisher, and they are able to handle variant solutions. I suppose that is why they would specifically say "using SUMIF, what is the sum of all the ...".
    Maybe I'm hoping for too much, and maybe I'll have to incorporate M/C and T/F questions, but I really don't want to give up the practical questions. I believe that learning Excel is much more than theory, and works best when using practical questions.

  8. #8
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,010

    Re: Autograding Excel spreadsheets

    .
    I don't know how you would approach your third option for answering. I suppose you could auto-grade everything but leave those
    last answers to be manually graded ? Of course, the student would not immediately receive their grade.

    ????

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,383

    Re: Autograding Excel spreadsheets

    If you say "using SUMIF, what is the sum of all the ..." you are actually providing the key to the solution. Whereas, as I have pointed out, there could be four, or more, solutions ... and you have pointed them in the direction of one of them (perhaps the simplest).

  10. #10
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,010

    Re: Autograding Excel spreadsheets

    .
    To add to my last answer ... what if you created a question that provides part of the answer. Enough so the student has not alternative BUT to provide the correct answer.

    This is very simplified but you could ask : What is another way of "saying" =B3*B4 ? And the answer would be =B4*B3. Again ... simplified but it provides an example.

    And I had a number of instructors who, when presenting the material, would say "You will see this again." Another means of insuring the students provide the exact answer.

  11. #11
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,010

    Re: Autograding Excel spreadsheets

    .
    TMS and I are thinking along the same lines.

    I'll stop responding at this point so as not to be repetitive.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,383

    Re: Autograding Excel spreadsheets

    Agreed. I don't think I can add anything constructive.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-08-2012, 03:12 PM
  2. Import Data from multiple spreadsheets into seperate spreadsheets worksheet
    By cablecrt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2008, 08:01 AM
  3. Can Excel Spreadsheets be saved as Microsoft Works spreadsheets?
    By EXCEL WORKS CONFUSED in forum Excel General
    Replies: 4
    Last Post: 08-18-2006, 06:25 PM
  4. [SOLVED] Excel 2003 loses the links to other excel spreadsheets on a server
    By Mark at SMS in forum Excel General
    Replies: 0
    Last Post: 05-01-2006, 10:00 AM
  5. [SOLVED] How do I combine data from various Excel spreadsheets into one Excel spreadsheets
    By SouthAfricanStan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2006, 01:10 PM
  6. [SOLVED] Opening excel spreadsheets via excel.application object w/o prompts
    By Andy S. in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  7. [SOLVED] Opening excel spreadsheets via excel.application object w/o prompts
    By Andy S. in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 09:05 AM
  8. Opening excel spreadsheets via excel.application object w/o prompts
    By Andy S. in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1