Translation tables are a special type of calculation that allow a series of results in specified assessment items - the source items - to be translated to a single result in another item - the target item.
Generally, the target item is the assessment item that stores the translated results, uses a list marking scheme, eg A to E, Excellent, Very Good ..., etc.
Some simple to more complex examples are:
| • | The translation of numeric results in one item into an A to E grade, whereby results above 95 receive an A, 85 to 94, a B and so forth. |
| • | It may be a requirement that a student passes three assessment tasks in order to satisfactorily pass a subject, eg an S result in TASK1, TASK2 and TASK3 translates to S in a PASS item. |
| • | In the example shown here, to receive an A, three different sets of criteria may be met so that any combination of two As and one B or better, attains an A. Similar criteria apply to Bs, Cs, etc. |
Other scenarios could involve multiple marking schemes, different criteria needing to be met for different source items, and several alternatives being acceptable for each target item result.
Translation Table calculations, because they are set out graphically, allow very complex formulae to be set up easily, but the same results could be achieved by a long and complex calculation using If( ) or MultiIf( ) functions.
Proceed as follows to add a Translation Table calculation:
Your table will have a Result column relating to the target assessment item and one column for each of the selected source assessment items
The translation process evaluates from the top and keeps moving down until all of the conditions are met.
As shown above, if students do not meet any of the criteria for A to D, they get E.
|
In the Translation table, you add the source items, the items that contains the raw results that are going to feed into the calculation. There is no limit on the number of source items that may be added. Then, in each cell, you stipulate the criterion that must be met. For example, as seen below, to attain an A a student must receive greater than 90 in each of AT1, AT2 and AT3.
When entering the criterion for each cell, you may use one of the following operators:
|
|
= <> > >= < <= |
Equal to Not equal to Greater than Greater than or equal to Less than Less than or equal to |
The operator is followed by a valid value for the marking scheme of the source item. For example, >=A, =S, >70, for an A to E, Satisfactory/Not Satisfactory, or Numeric marking scheme, respectively. Note, however, that the Equals operator may be omitted, ie entering A in a cell is equivalent to =A.
|
|
The Translation Table process begins at the top row of the table and, for each student, checks to see if all the criteria stipulated in the row are met. If they are, the student’s result is translated to the Result value for the row. If they are not met, the process moves to the second row checking whether the criteria for the second Result value are met, and continues down the rows until all conditions in a row are met. Because of this process of working from the top down, it is important that the Result rows in the Translation Table progress from best to worst result and all of the rows of the same value are grouped together. If not entered in the correct order, ensure you move the rows up or down into the order in which they are to be evaluated. Generally, the order of values entered in the Translation Table should match the order of the values in the target item's marking scheme. Note also, that the marking scheme's order of values is used to determine the maximum trade-offs allowed, with each progressional step being one trade-off point. For example, in an A to E marking scheme, if a maximum trade-off deficit of 2 is allowed, C or D would be acceptable for a B value. When the Translation Table is being evaluated, if no row’s criteria can be met, the student is not assigned a result. A blank cell anywhere in the translation table indicates that there is no criterion for the particular source item, in which case any result, including a blank result, fulfils this criterion.
|
You may trade-off a surplus in one source item against a deficit in another, where the student’s achievement has not been even, ie the student does not quite meet the criterion for one source item, but has a surplus in another item. In this example, a result in source item AT1 may be offset by at least an equivalent surplus in item AT2, up to a maximum of 1 point.
It is not necessary to stipulate a trade-off, in which case the student must meet the criteria as set out in the table. If trade-offs are allowed, you may stipulate one trade-off only, nominating the item in which a deficit may be offset by an equivalent surplus in another. The maximum trade-off allowed must then be stipulated, with the value entered in the Max field being relative to the marking schemes of the two items. A maximum trade-off of one in an A to E marking scheme is one value, eg the difference between A and B, B and C, etc.
|
