Translation Table Calculations

Accelerus Icon

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.AI CalcTransStep6

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.


minusSetting up a Translation Table calculation

Proceed as follows to add a Translation Table calculation:

Select Translation Table from the Calculation Type dropdown box.

AI CalcTransStep1

Right click over the table and, from the list of available assessment items, select one of the items that holds the source data.
Repeat this step for all other source items that are contributing to the calculated result.

AI CalcTransStep2

Your table will have a Result column relating to the target assessment item and one column for each of the selected source assessment items

In the first row of the table:
In the Result cell, add the highest or best of the results a student may achieve for the target item, eg if using an A to E marking scheme, enter A.

The translation process evaluates from the top and keeps moving down until all of the conditions are met.

For each of the source assessment items' cells enter the cell criteria, ie what must be achieved in each item for a student to receive the result value stipulated, in this case an A.

AI CalcTransStep3

Add the next row required, ensuring that you move down from best in descending order, adding this row's allowed combination of results in the source items.
If necessary, insert multiple rows for the same result value, where different results in the source items translate to the same target result.

Ai CalcTransStep4A

Continue in this way, adding rows as required.
Leave any cells or rows blank if no particular criteria are required, especially useful for the last row of the table, to ensure all students are assigned one of the Result values.

AI CalcTransStep4

As shown above, if students do not meet any of the criteria for A to D, they get E.

Ensure the rows of the table are in the order in which you wish them to be evaluated, moving rows up or down if they are in the wrong position.
If trade-offs are allowed:
Choose the required assessment items from the two Trade-off dropdown lists.
Insert a number for the maximum trade-off allowed, keeping in mind that the number entered here will be relative to the type of marking scheme of the respective assessment items.

AI CalcTransStep5

To remove columns from the table, right click over the table and deselect the column from the list displayed.
To remove any rows from the table, highlight the rows and click the Delete icon.
Click OK.


minusSource items and cell criteria

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.

AI CalcTransStep3

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.


minusTranslation Table calculation process

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.AI CalcTransStep6

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.



Leave the last row of a Translation Table totally blank to assign the last Result to all students who do not meet any of the other rows' criteria, eg if any of their results are blank or an exceptional value that you may have set up in your marking scheme.



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.

AI CalcTransStep5

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.



Where more than one trade-off is required, or the trade-offs are complex, you simply need to add additional rows to the table which contain the different scenarios allowed.