Introducing ExcelAssess

Note: As of May 2016, we have retired this software as it does not work with the current versions of Windows. See the ExcelAssess home page for details.

David Whigham, Glasgow Caledonian University

The background to this work was the need to assess large numbers of students as painlessly as possible, on a variety of modules. The one thing in common was that they would be assessed either in their Excel skills themselves or in their use of Excel in generic business problems, of which Economics is clearly a part, but also some basic quantitative methods and some statistics.

With a large number of students coming through the system, John Houston and I worked for quite a while, several years ago, preparing sets of examination questions which are self-marking. On average 700 students per year were coming through the system, all to be assessed in a one-and-a-half-hour examination, although as we'll see later on we now have much more flexibility in how big or small the examination happens to be.

My dream was to be able to sit down at nine in the morning, click a couple of buttons, prepare an examination with ten questions, administer it to over 700 students over three or four hours and then have the marks all compiled and ready for publication. We're talking at most five hours turn-around between preparing, administering and submitting the results of the examination.

Given it was our own work, each of these examinations had to be compiled manually. We would have a bank of various questions that we thought would be useful but it would still require that we pull them together to make this particular test, change it for the next exam etc. etc. That was frequently time-consuming, especially given the need for security and various other potential bugs created by the nature of the questions themselves.

What we needed was a compiling program that could be applied to a bank of questions that would allow mixing and matching to create individual assessments quickly and efficiently.

The Questions

The front sheet is the mark sheet, showing the marks earned and the marks available from the assessment that has been created. Basically, the student goes to the first question and, in line with instructions, attempts the question, puts the answer in the prescribed cells, and then checks whether the marks have been awarded. If they have then they proceed to the next question or question part. If they have not then they can attempt to correct their answer and check on the success of their amendment. This process continues until either a successful answer is obtained or they move on to another question.

There are some checks on the input. For example, in a basic question on demand, the student is warned if they try to enter a price which is negative.

There are one or two issues about security - in particular the extent to which the student can inadvertently corrupt the question. Protection of the question sheet is clearly the answer - except when routines such as the Solver or Data Analysis or Sorting are required then the question sheet must not be protected. This means that students could erase critical parts of the question. We have dealt with this difficulty however, by including a restore facility.

In the initial stages of developing the files the possibility was considered that a student might look at the screen of the student next to them and simply type the number into their own exam, and so get marks without knowing the right formula to use. Unfortunately, Excel cannot distinguish between the results of a formula and the formula itself. So our most recent development was to build in randomness. There are issues with this, but it seems to work. In the blurb for these exams, we always stress that students must type in a formula, not work out the answer separately (with a calculator, say) and type it in.

Creating an Assessment

In conjunction with Bristol, we were helped to employ a programmer to put a front end on to our supply of questions. This is freely available: see the ExcelAssess home page for download instructions.

It is then simply a question of sitting down, selecting questions from the front end, (after previewing if necessary) noting the total marks awarded from the selected question set, setting the parameters you want to set, then clicking a button to compile the assessment.

It is very easy to use. All the questions are classified by category and you simply tick a box to select them. In the "marks available" column you can select the total number of marks that you wish to award for that question and also see a running total of the marks available from the questions selected to date.

In the "tolerance" field you can allow the student's answer to vary, by a small proportion, from the given answer. Often in Economics questions, particularly if you're using the Solver or different Excel configurations, the computation is not always 100% the same as the given answer, so for portability we are allowing a certain degree of tolerance. You can set that at, say, .01, so as long as the answer is correct within one percent it gets the marks. Sometimes you will want to set it to zero. In sorting questions, for example, there is no possibility of deviance from the correct answer, so the tolerance would 0% for that. A default tolerance is set in the each question itself as well as a default available mark but these can be changed as you please from the front end selection program. Where a question consists of more than one part then the default marks distribution among the parts is automatically applied pro rata to whatever total mark you have selected.

There is also a choice of whether to make the marks visible. If you open the question file directly, you will see that the default situation is that marks are visible to the student, allowing them to see how they're getting on. However this visibility can be switched off.

Broadly speaking, it is suggested that visibility should be on when the files are being used in formative assessments. In summative examinations then it is a matter of personal choice. In our own institution we have kept visibility on and there is a lot of evidence that students like knowing whether they are right or wrong and having the ability to correct what may only have been a careless typo.

Having selected the questions you want, you simply click "Compile" and you get a compiled assessment with each question on a different sheet. There is a separate marks sheet, which is protected and which is hidden if you've chosen that option in the compiler.

The Future

At the moment, we have about fifty questions spanning a variety of topics: general Excel, stats, basic mathematics, elementary and intermediate micro, elementary and some intermediate macro and a few odds and sods. The list of questions will be augmented on an ongoing basis, by us and hopefully by others as well, creating questions to a fairly straightforward standard format.

Any comments about the files will be greatly appreciated as well as any other general comments that users feel could improve the existing format.

These should be addressed to David Whigham, dwh@gcal.ac.uk . Any difficulties with downloading or installation should be addressed to the Economics Network, econ-network@bristol.ac.uk .