Available Functions

Accelerus Icon
 

RA FunctionsA major part of the building of a formula type calculation in an assessment item, as well as when setting up analyses, involves using functions.

Functions are built-in procedures used to evaluate, make calculations on, or transform data. When you specify a function, a set of operations built into the function are executed, without you having to identify each operation separately. This makes creating formulae easier.

In order to define expressions, users need to be familiar with the general principles of building formulae and the types of functions available across other applications, eg Microsoft Excel. Many of the functions in Accelerus are similar to those available in Microsoft Excel.

The functions available in the Calculation Editor, brought up when a Formula calculation is being added to an assessment item, whether in the Accelerus program or via the web, are grouped under 9 different branches. All of the functions available are covered below, with details of the arguments required for each, their syntax, the type of data that is returned, and straightforward examples of each.

 

minusArray Functions

The Array functions work with multi-valued data or lists in arrays and, in this way, are not dissimilar to the Group functions.

They are used primarily with the multi-valued Array.Tags field and the various All Roles fields that apply to subject, class and cohort records. For example, you could extract the name of the first teacher from the list of teachers assigned to a class.

The Array() function converts a set of single values into an array, whereas most of the other functions extract single values from an array.

The Index() and Ordinal() functions, and the various permutations of these, refer to an array or list of values, and are very useful in many calculations, eg converting grades to numbers, averaging, and then converting them back again.

RA FunctionsArray

 

Arguments

expr is any set of expressions

Returns

A multi-valued expression

Action/Description

Converts the set of single values into a multi-valued expression so it may be used by the Group functions.

Example syntax

DConcat(Array({WR1}, {WR2}, {WR3}, {WR4}), " – ")

Example usage/s

The group functions accept multi-valued parameters. The array function can be used to create a multi-valued parameter.

The Array function can also be written as a list of values in square brackets, eg:

DConcat([{WR1}, {WR2}, {WR3}, {WR4}], " – ")

 

Arguments

list is multi-valued argument containing a set of values

Returns

A string

Action/Description

Extracts the first value in the set of values

Example syntax

First({Cl.Roles.TPF})

First({AI.Tags}) = Last({AI.Tags})

Example usage/s

The function is used where only the first of a set of values is required, eg the first teacher where there are multiple teachers of a class. It may also be used where there is only one value, eg one teacher role only exists in the database, to convert the field from an array to a string.

In the first example above, if teachers John Smith and Mary Brown taught a class, Mr John Smith would be returned.

The First function can be used in conjunction with Last() to determine if there is only one value in an array, as shown in the second example above, which could be inserted into a Filter operator to ensure that only assessment items with one specific tag are extracted.

However, with some multi-valued fields, where blanks are allowed, it would be better to use FirstX() and LastX().

 

Arguments

list is multi-valued argument containing a set of values

Returns

A string

Action/Description

Extracts the first non-blank value in the set of values

Example syntax

FirstX({Sb.Roles.Code})

If(FirstX({Cl.Roles.TPF}) = LastX({Cl.Roles.TPF}), "teacher", "teachers")

Example usage/s

The function is used where only the first of a set of values is required, eg the first teacher where there are multiple teachers of a class but you cannot be sure if the first role contains a blank or not.

In the first example above, if the school had three subject level roles and the array Sb.Roles.Code was [ ,SMIJ,BROM], SMIJ would be returned.

FirstX() in conjunction with LastX() can be used to determine if there is only one non-blank value in an array. In the second example above, if there is only one teacher, the singular teacher is inserted, otherwise the plural is inserted.

 

Arguments

num is a number representing which item in the list to return

list is a multi-valued argument containing the possible set of values that can be returned

Returns

Numeric or text string

Action/Description

Returns the item in a given array at the location specified.

Example syntax

Index({TASK1}, ["Good", "Better", "Best"])

Index(DAvg(Ordinal([{AT1}, {AT2}, {AT3}], {AT1.List})), {AT1.List})

Index(DAvg(Ordinal([{AT1}, {AT2}, {AT3}],  ['E','D','C','B','A'])),  ['E','D','C','B','A'])

Example usage/s

The first example allows TASK1 to contain a result between 1 and 3. If the result is 1, the function returns Good; a result of 2 returns Better and 3 returns Best.

The second example shows the way to average a set of letter grades, eg A, B, C … into a letter grade. The Ordinal function converts each of the three letter grades into a numeric ordinal, which is then averaged by the DAvg function, and finally converted back into a letter grade by the Index function. However, in this case, it would be assumed that the list of values for the assessment item AT1 were in the appropriate order, ie from lowest to highest, ie E, D, C, B, A. Otherwise, when the values are averaged, they will be rounded down rather than up. Therefore, you could replace {AT1.List} with an array of the actual values in the correct order, as shown in the third example above.

The RevIndex function may be used as an alternative to Index, where you want the index to begin from the end of the list, rather than the beginning.

 

Arguments

num is a number representing which item in the list to return

list is a multi-valued argument containing the possible set of values that can be returned

Returns

Numeric or text string

Action/Description

Returns the item in a given array at the location specified. However, any missing items in the list are skipped.

Example syntax

IndexX(2,[{WR1}, {WR2}, {WR3}, {WR4}, {WR5}])

IndexX(1,{Cl.Roles.Code})

Example usage/s

In the first example, where results are stored in the WR1 to WR5 items, the second non-blank result will be returned. For example, if WR1 and WR2 are not missing, WR2 will be returned. If WR2 is missing, WR3 will be returned, if not blank, and so forth.

The second example will return the first non-blank class teacher role and is, therefore, the equivalent of FirstX({Cl.Roles.Code}).

 

Arguments

list is multi-valued argument containing a set of values

Returns

A string

Action/Description

Extracts the last value in the set of values

Example syntax

First({Cl.Roles.TPF})

First({AI.Tags}) = Last({AI.Tags})

Example usage/s

The function is used where only the last of a set of values is required, eg the last teacher where there are multiple teachers of a class. It may also be used where there is only one value, eg one teacher role only exists in the database, to convert the field from an array to a string.

In the first example above, if teachers John Smith and Mary Brown taught a class, Ms Mary Brown would be returned.

The First function can be used in conjunction with Last() to determine if there is only one value in an array, as shown in the second example, which could be inserted into a Filter operator to ensure that only assessment items with one specific tag are extracted.

However, with some multi-valued fields, where blanks are allowed, it would be better to use FirstX() and LastX().

 

Arguments

list is multi-valued argument containing a set of values

Returns

A string

Action/Description

Extracts the last non-blank value in the set of values

Example syntax

FirstX({Sb.Roles.Code})

If(FirstX({Cl.Roles.TPF}) = LastX({Cl.Roles.TPF}), "teacher", "teachers")

Example usage/s

The function is used where only the last of a set of values is required, eg the first teacher where there are multiple teachers of a class but you cannot be sure if the last role contains a blank or not.

In the first example above, if the school had three subject level roles and the array Sb.Roles.Code was [SMIJ,BROM, ], BROM would be returned.

FirstX() in conjunction with LastX() can be used to determine if there is only one non-blank value in an array. In the second example, if there is only one teacher, the singular teacher is inserted, otherwise the plural is inserted.

 

Arguments

expr is any expression

list is an array of values against which the expression will be matched, typically being sorted from best to worst.

Returns

Number

Action/Description

Returns the ordinal position of a value within the given array of sorted values.

Example syntax

Ordinal({AT1}, {AT1.List}) * 2

Ordinal({EnteredValue}, ["A", "U", "S", "R", "N"]) * 2

Index(DAvg(Ordinal([{AT1}, {AT2}, {AT3}], ['E','D','C','B','A'])),  ['E','D','C','B','A'])

Example usage/s

The first example is used in a calculated assessment item to return a number corresponding to the position of the result in the marking scheme definition for AT1 and multiply it by 2.

This is one way of converting a letter grade into a number out of 10 without having to use a sequence of If statements.

The second example is from the Analyser and operates in a similar fashion to the first example in that it translates a grade in the EnteredValue field into a number. The set of values is explicitly listed because there is no access to marking schemes in formulae in an analysis. Note that it is generally preferable to use the ResultOrdinal field in an analysis to do this.

The third example is the same as that described in the Index function for averaging a series of letter grades, ensuring that they are rounded up when averaged and not down, by listing the values in lowest to highest order.

 

Arguments

list is a multi-valued argument containing the possible set of values that can be returned

Returns

An array

Action/Description

Allows an array of values to be evaluated for errors, removing any error values from the list of values

Example syntax

DMax(RemoveError({CalculatedResults}))

Example usage/s

This function is most likely to be used as part of other calculations, to ensure that errors are excluded. In the example above, where a field may have been created to hold an array of calculated results for a student or a subject from the Aggregate operator, any results that contain errors such as #NUM would be removed prior to the maximum value being extracted by the DMax function.

 

Arguments

list is a multi-valued argument containing the possible set of values that can be returned

Returns

An array

Action/Description

Removes any values in a list that are not numbers, to produce a an array of numbers

Example syntax

RemoveNotNumber([45, NA, 73, 89])

DMax(RemoveNotNumber({EnteredValue}))

Example usage/s

The first example would return an array of [45, 73, 89], ie the NA value would be removed.

This function could be used to filter out any non-numerics prior to performing operations on them. In the second example, being used in an Aggregate operator to, for example, find the maximum result across each subject, any non-numerics would be removed prior to the maximum being identified.

 

Arguments

list is a multi-valued argument containing the possible set of values that can be returned

Returns

An array

Action/Description

Removes any blank values from an array

Example syntax

DCount(RemoveBlank({Cl.Roles.Code}))

Example usage/s

This example is using one of the multi-valued All Roles fields, such as Cl.Roles.Code. A school may have defined several class roles but some roles may be blank, eg where there is only one teacher to a class. The blank roles are being removed, to leave an array of class teacher codes. In this case, a count of the number of teachers per class is being produced by then using DCount.

 

Arguments

num is a number representing which item in the list to return

list is a multi-valued argument containing the possible set of values that can be returned

Returns

Numeric or text string

Action/Description

Beginning at the end of the list, returns the item in a given array at the location specified

Example syntax

RevIndex({TASK1}, ["Good", "Better", "Best"])

RevIndex([{AT1}, {AT2}, {AT3}],  ['A','B','C','D','E'])

Example usage/s

The first example allows TASK1 to contain a result between 1 and 3. If the result is 1, the function returns Best; a result of 2 returns Better and 3 returns Good.

The second example could be used to convert a series of  numeric scores between 5 and 1 to an A to E score, with A being equal to 5, B to 4, and so on.

This function simply starts at the other end of the list to the Index function and, therefore, is more appropriate to use with an A to E marking scheme than Index().

 

Arguments

num is a number representing which item in the list to return

list is a multi-valued argument containing the possible set of values that can be returned

Returns

Numeric or text string

Action/Description

Beginning at the end of the list, returns the item in a given array at the location specified. However, any missing items in the list are skipped.

Example syntax

RevIndexX(2,[{WR1}, {WR2}, {WR3}, {WR4}, {WR5}])

RevIndexX(1,{Cl.Roles.Code})

Example usage/s

In the first example, where results are stored in the WR1 to WR5 items, the second non-blank result will be returned. For example, if WR5 and WR4 are not missing, WR4 will be returned. If WR4 is missing, WR3 will be returned, if not blank, and so forth.

The second example will return the last non-blank class teacher role and is, therefore, the equivalent of LastX({Cl.Roles.Code}).

 

minusBoolean Functions

Boolean functions primarily deal with true or false, eg whether all or any of a series of conditions have been met or not, usually returning true or false.

Two of the available Boolean Functions, although evaluating whether conditions are true or false, actually return a count of the number of conditions met. These are the CountIf and CountTrue Functions.

Lastly, the Compare function returns a value of -1, 0 or +1 depending on whether the value in one item is smaller, identical or greater than that in another with which it is being compared.

 

RA FunctionsBoolean

 

Arguments

cond is a Boolean expression, and multiple Boolean expressions are permitted

Returns

Boolean True or False

Action/Description

Returns true if all conditions in the list are true.

Example syntax

AllTrue({NumEnglish} > 2, {NumMaths} > 2, {NumOther} >= 5)

AllTrue({AT1} like "A*", {AT2} like "A*", {AT3} like "A*")

Example usage/s

To ensure that every one of a number of conditions have been met, such as:

Having completed at least two English units, two Maths units and five or more other units, when running an analysis.

The student has received either an A-, A+ or A in all three of the stipulated assessment items.

This function is equivalent to using a logical AND operator between each condition.

 

Arguments

cond is a Boolean expression, and multiple Boolean expressions are permitted

Returns

Boolean True or False

Action/Description

Returns true if any of the conditions in the list are true.

Example syntax

AnyTrue({calcWR1} = "N", {calcWR2} = "N", {calcWR3} = "N")

Example usage/s

In the above example, if any of the three {calcWRx} fields has an N value, the function will return true. If none of them contain an N, the function will return false.

This function is equivalent to using a logical OR operator between each condition.

 

Arguments

expr1 is the first value being compared

expr2 is the second value against which expr1 is being compared

order is one of a set of sort modes comparing text and numbers, in ascending or descending order. Either the sort mode name or its value may be used in the function:

 

Name

#

Ordering of Values

SORT_09AZ

0

Numbers in ascending order, then text in alphabetic order

SORT_90AZ

1

Numbers in descending order, then text in alphabetic order

SORT_09ZA

2

Numbers in ascending order, then text in reverse alphabetic order

SORT_90ZA

3

Numbers in descending order, then text in reverse alphabetic order

SORT_AZ09

4

Text in alphabetic order, then numbers in ascending order

SORT_AZ90

5

Text in alphabetic order, then numbers in descending order

SORT_ZA09

6

Text in reverse alphabetic order, then numbers in ascending order

SORT_ZA90

7

Text in reverse alphabetic order, then numbers in descending order

Returns

-1, 0 or +1

Action/Description

Compares the two values based on the selected ordering of values, returning:

-1 if val1 is smaller than val2
0 if the two values are identical
+1 if val1 is greater than val2

Being able to adjust the ordering of values allows the comparison to take into consideration different value ranges. For example, in an exam with numeric results, a result of 1 is usually considered to be of less value than a result of 9. However, in a ranking using numeric ranking values, a rank value of 1, or first, is considered to be of higher value than a ranking of 9th. Likewise, alphabetically A comes before or has a lower value than E; yet as a grade in schools an A is typically considered have a higher value than an E.

The standard comparison operators (<, >, <=, >=, <> and =) always use a comparison ordering of ascending numbers followed by text in alphabetic order.

 

NBWhen using the Ordinal function, a lower ordinal number usually implies a better value, because List marking schemes should have the best values first.

Example syntax

If(Compare({TASK1}, {TASK2}, SORT_09AZ ) > 0, "Improving", "No progress")

If(Compare({TASK1}, {TASK2}, SORT_90AZ ) > 0, "Improving", "No progress")

Example usage/s

The two examples above return either the text Improving or No progress.

The difference is that the first example works on the basis that if TASK1 has a higher numeric value than TASK2 it returns Improving, whereas the second example will return this if TASK1 has a lower numeric value. The first example would be used for exam marks; the second example would be used for comparing ranking.

 

Arguments

str is a partial expression which is combined with each of the expr expressions to create a complete Boolean expression

Returns

Number

Action/Description

Combines the first string parameter with each subsequent expression and returns the count of how many are true.

Example syntax

CountIf("='S'", {WR1}, {WR2}, {WR3}, {WR4})

Example usage/s

To see how many S results were obtained in the four specified work requirement assessment items.

The CountIf function is used where the conditional expressions to be counted are very similar. It is important that the first parameter is enclosed in double quotation marks as it is a string and, within this, any string values should be enclosed in single quotes.

CountTrue is the preferred function to CountIf, as intent of the conditions is clearer.

 

Arguments

cond is a Boolean expression

Multiple Boolean expressions are permitted

Returns

Number

Action/Description

Returns a count of the number of the Boolean conditions which are true.

Example syntax

CountTrue({calcAT1} > 7, {calcAT2} > 7, {calcAT3} > 14)

Example usage/s

The three {calcATx} fields are new fields created to hold students' achievements in a series of assessment tasks. The function then calculates how many high achievements were obtained for a set of assessment tasks.

In the example shown above, the first two calculated fields use different criteria for high achievement than the third field. A high achievement in the first two is any result over 7, whereas the third requires a result over 14.

 

 

minusDate Functions

The Date functions apply to data in Accelerus that uses a date marking scheme or which can be interpreted as a date. They allow, for example, the extraction of the day, month or year from a date, and to calculate the number of days difference between two dates.

 

RA FunctionsDate

 

Arguments

Date is a date value or data that can be interpreted as a date

num is a numeric expression

Returns

A date

Action/Description

Returns the date that is the specified number of days after the date specified in the argument.

Example syntax

AddDays("15/06/2014", 20)

AddDays("15 June 2014", SumX({Task1Days}, {Task2Days}))

AddDays(Today(), 365)

AddDays({Student.DOB}, 40)

Example usage/s

In the first example above, the date that falls 20 days after 15 June 2014 is returned, ie 5 July 2014.

In the second example, the number of days allowed for two tasks are summed, and the total of days is added to the specified date.

The third example uses the Today() function to determine the current date, and then 365 days are added to this.

The last example adds 40 days to the student's date of birth, stored in a student custom property coded DOB.

 

Arguments

yyyy is a four digit number that can represent a year, eg 2014

mm is a two digit number that can represent a month, eg 02, 15

dd is a two digit number than can signify a day, eg 03, 30

Returns

A date

Action/Description

Returns a date made up of the three arguments

Example syntax

Date(2014,02,28)

Date(Year(Today()) + 1,Month(Today()), Day(Today()))

Example usage/s

The first example above is to demonstrate the syntax of this function, returning 28 February 2014.

The second example allows the date one year from today's date to be calculated, extracting the year from today's date and adding 1 to it, with today's month and day completing the Date() function. The same result would be achieved by using AddDays(Today(), 365).

 

Arguments

Two dates or data that can be interpreted as dates

Returns

A number

Action/Description

Returns the number of days difference between two dates, subtracting the second date from the first.

Example syntax

DateDiff("15 Feb 2014", "31 March 2015")

DateDiff(Today(), {TestDate})

 

Example usage/s

In the above first example, the number of days between the two stipulated dates is calculated, in this case -44 days. If the dates had been reversed, the result would be 44 days.

The second example calculates the number of days that have passed since the date of a particular test, stored in the assessment item TestDate.

 

Arguments

Two dates or data that can be interpreted as dates

Returns

A string made up of two numbers separated by a full stop, eg 10.1

Action/Description

Returns the number of years and months difference between two dates, expressing this in the format Years.Months, eg  9.1 is 9 years and 1 month; 14.10 is 14 years and 10 months.

Note that, when used in an assessment item, the item should use a comment marking scheme and not a numeric. Otherwise, 14.10 would be converted to 14 or 14.1, depending on the number of decimal places in the marking scheme.

Example syntax

DateDiffYearMonth("15 Feb 2014", {Student.DOB})

DateDiffYearMonth({Test1Date}, {Test2Date})

 

Example usage/s

In the first example, the student's date of birth, as stored in a custom property coded DOB, is subtracted from the first date, with the number of years and months difference being returned. This could be used to work out the student's age at a particular point in time, eg the date of a test.

The second example calculates the years and months between two test dates.

 

Arguments

Date is a date value or data that can be interpreted as a date

Returns

A two digit number

Action/Description

Extracts the day component of a date

Example syntax

Day("15/06/2014")

Day(Today())

Example usage/s

The first example extracts the 15 from the stipulated date and the second whatever the day is today.

 

Arguments

Date is a date value or data that can be interpreted as a date

Returns

A number between 1 (Sunday) and 7 (Saturday)

Action/Description

Determines upon which day of the week a particular date falls, expressed as an integer between 1 and 7

Example syntax

DayOfWeek("15/07/2014")

DayOfWeek(Today())

Example usage/s

The first example returns the number 3, ie the third day of the week, Tuesday. The second example returns the day of the week for today's date, whatever this may be.

 

Arguments

expr1 is the expression to be evaluated

expr2 is the value to be returned if expr1 is not a date

Returns

Date or text string

Action/Description

Allows an expression to be evaluated to ensure it returns a date. If it is not a date, the second expression is returned. Otherwise, the date is returned. This function is the equivalent to the following which uses an If and an IsDate function:

If(Not IsDate(expr1), expr2, expr1)

Example syntax

IfNotDate({TestDate}, "15/7/204")

Example usage/s

If the data stored in the assessment item TestDate is not a date, eg it is NA, this function will return 15/7/2014 instead.

 

Arguments

expr is the expression to be evaluated

Returns

True if the expression is a date

Action/Description

Allows an expression to be evaluated to ensure it is a date.

Example syntax

If(IsDate({TestDate}), "Test undertaken", "NA")

Example usage/s

If the data in the assessment item TestDate can be evaluated as a date, the text Test undertaken is output, otherwise NA is printed.

 

Arguments

Date is a date value or data that can be interpreted as a date

Returns

A two digit number

Action/Description

Extracts the month component of a date

Example syntax

Month("15/6/2014")

Month(Today())

Example usage/s

The first example extracts the 6 from the stipulated date and returns 06. The second returns the number of whatever the month is today.

 

Arguments

No arguments

Returns

A date

Action/Description

Returns today's date in the format yyyy-mm-dd

Example syntax

Year(Today())

DateDiff(Today(), '31 March 2015')

Example usage/s

The first example extracts the year from the stipulated date, eg if today was 15 June 2014, 2014 would be returned.

In the second example, the number of days difference is calculated between today's date and the second date.

 

Arguments

Date is a date value or data that can be interpreted as a date

Returns

A four digit number

Action/Description

Extracts the year component of a date

Example syntax

Year("15/06/2014")

Year({Student.DOB})

Example usage/s

The first example extracts the 2014 from the stipulated date.

The second example extracts the year of birth of the student from a student custom property coded DOB.

 

minusGroup Functions

The Group functions deal with multiple data values.

When setting up analyses, they are primarily used in the Aggregate Operator where the set of values for a single aggregate grouping can be processed and a single result returned.

In assessment items, across-subject or across-class calculations can be performed using the Group functions.

Most of the functions have equivalent non-group functions and operate on similar principles to these.

The Group functions all begin with D, signifying database, to distinguish them from the equivalent non-group functions.

Note that, where group functions allow more than one multi-valued parameter, there must be the same number of values in each parameter. This is the same as mathematical matrix operations. It is acceptable to use a single-valued parameter in place of multi-valued parameters. In such a case, it is assumed that the single value is a multi-value parameter with identical values.

 

RA FunctionsGroup

 

Arguments

dnum is a multi-valued numeric expression

Returns

Number

Action/Description

Returns the average of the multi-valued parameter

Example syntax

DAvg({EnteredValue})

DAvg({Class.EXAM})

Example usage/s

In the first example, used in the Analyser, assuming the result values are limited to a strict set of numeric assessment items and grouped by {Student.Code} in the Aggregate Operator, the above formula would return a single average value for each unique {Student.Code}.

In the second example, used in an assessment item, the average of the class results for the assessment item EXAM is calculated.

Note that any non-numeric value will make the DAvg function return an error, unlike the DAvgI and DAvgX functions covered below.

 

Arguments

dnum is a multi-valued numeric expression

Returns

Number

Action/Description

Returns the inclusive average of the multi-valued parameter, treating any non-numerics as zero.

Example syntax

DAvgI({EnteredValue})

DAvgI({Class.EXAM})

Example usage/s

The difference between the examples here and those used in the DAvg function, above, is that if any non-numeric value is found, DAvgI replaces it with 0. This has the effect of reducing the average, or moving it closer to 0.

 

Arguments

dnum is a multi-valued numeric expression

Returns

Number

Action/Description

Returns the exclusive average of the multi-valued parameter, ie any non-numerics are ignored

Example syntax

DAvgX({EnteredValue})

DAvgX({Class.EXAM})

Example usage/s

When calculating the average, any non-numeric values are not considered in the calculation. For example, in the case of example two above, if there were ten students in the class and one had a blank EXAM result, the average for the class would be based on 9 students and not 10.

 

Arguments

dstr1 is a multi-valued string expression

dstr2 is an optional multi-valued string expression to be used as the delimiter

Returns

Text string

Action/Description

Concatenates, or appends, all values in the group expression with an optional delimiter. If the delimiter is multi-valued, the final delimiter value will be ignored as there is always one less delimiter than expressions.

Example syntax

DConcat({Class.Code}, ", ")

Example usage/s

In an analysis, assuming a data source of enrolments being fed into an Aggregate Operator grouped by Student.Code, the above formula will create a single field which lists all class codes in which the student is enrolled, separated by a comma.

If a Filter Operator is applied to limit the set of enrolments to only those which have achieved some standard, this can be used to get a list of passed or failed classes.

 

Arguments

dstr1 is a multi-valued string expression

dcond is a multi-valued Boolean condition

dstr2 is an optional multi-valued string expression to be used as the delimiter

Returns

Text string

Action/Description

Concatenates all values in the group expression, where the condition is met, with an optional delimiter.

Example syntax

DConcatIf({Class.Code}, {EnteredValue} = "N", ", ")

Example usage/s

In the Analyser, the above formula will create a list of class codes where the EnteredValue is N. Assuming the Source Operator has been set up to only extract the final results for students and that N was the value for a failed result, it would create a field which listed all the class codes in which failed final results were attained.

 

Arguments

dexpr is a multi-valued expression of any type

Returns

Number

Action/Description

Returns the count of items in the group expression

Example syntax

DCount({Student.Code})

DCount({Subject.EXAM})

Example usage/s

Can be used to count the number of expressions in the multi-valued expression. The most common use is where a count of some aggregated entity is required in an analysis. For example, if an Aggregate Operator is processing Enrolment data and is set to group by Subject.Code, the formula above would return the number of students in each subject.

The second example above shows the use of DCount in an assessment item to count the number of results for the item EXAM for the whole subject. Any missing results would not be included in the count, although any error results would be.

Note that the special fields {Subject.Count} and {Class.Count} are also available when adding calculations in assessment items, as alternatives to DCount.

 

Arguments

dexpr is a multi-valued expression of any type

dcond is a multi-valued Boolean condition

Returns

Number

Action/Description

Returns the count of items in the group expression where the condition is met.

Example syntax

DCountIf({Student.Code}, {Student.Gender} = "F")

DCountIf(1, {Subject.Final} like "A*")

Example usage/s

Assuming a source of enrolments being fed into an Aggregate Operator in an analysis, which is grouped by Class.Code, the first example would return the number of female students enrolled in each class.

In the second example, where the assessment item FINAL contains a result beginning with A, ie A+, A or A-, is is counted, returning the number of A results attained in the subject.

 

Arguments

dnum is a multi-valued numeric or string expression

Returns

Number or text string

Action/Description

Returns the maximum value of all items in the group expression.

If all values are numeric, a numeric maximum will be returned; otherwise a string maximum will be returned.

Example syntax

DMax({Subject.EXAM})

DMax({EnteredValue})

Example usage/s

Returns the maximum result value from the multi-valued set of result values.

In the first example, used in an assessment item, the maximum EXAM result is returned for all students in the subject.

The second example also returns the maximum result value, this time using the Analyser, whereby the multi-valued set will be dependent on the grouping used in the Aggregate Operator.

Beware of using DMax with List marking schemes as the maximum value may not necessarily be the best value, eg E is the maximum value in an A to E marking scheme.

 

Arguments

dnum is a multi-valued numeric or string expression

Returns

Number or text string

Action/Description

Returns the minimum value of all items in the group expression.

If all values are numeric, a numeric minimum will be returned; otherwise a string minimum will be returned.

 

Example syntax

DMin({Subject.EXAM})

DMin({EnteredValue})

Example usage/s

Identical to the DMax function except that it returns the minimum result value from the multi-valued set of result values.

 

Arguments

dpop is the population, or set of values, for which the mode is required

dvals is an optional array of possible values for the mode

Returns

Number or text string

Action/Description

Returns the most common value in a given population array, optionally using a sorted array of possible return values.

The array of possible return values is used in cases where there is more than one value with the same maximum number of values. If it is missing, the first value in the population that matches the maximum count of the most common values is returned.

Example syntax

DMode({Subject.TASK1})

DMode({EnteredValue}, [‘A','B','C','D','E'])

DMode( ["C", "B", "A", "B", "C"])

DMode( ["C", "B", "A", "B", "C"], ["A", "B", "C", "D"])

Example usage/s

In the first example, the most common result for TASK1 across the subject is extracted.

In the second case, in the Analyser, the mode of the EnteredValue in an Aggregate Operator expression is determined from the sorted array of A to E values.

The third and fourth examples show how the optional sorted array of possible values works. In both cases, there are two Bs and two Cs in the population. The third example, without the set of possible values, returns the first value in the population out of the B and C values, ie C. The fourth example would return a B because it comes before C in the array of sorted possible values.

 

Arguments

dpop is an array of numbers representing the population for which the standard deviation is to be calculated

Returns

Number

Action/Description

Returns the standard deviation of the population in the specified array.

If any non-numeric value is found, eg NA, an error is returned.

Example syntax

DStdDev({Subject.EXAM})

DStdDev({Class.EXAM})

Example usage/s

The standard deviation of results for the assessment item EXAM across the subject is calculated in the first example, or across a class in the second.

 

Arguments

dpop is an array of numbers representing the population for which the standard deviation is to be calculated

Returns

Number

Action/Description

Returns the standard deviation of the population in the specified array, ignoring any errors or non-numeric values.

Example syntax

DStdDevX({Subject.EXAM})

Example usage/s

As with the DStdDev function, the standard deviation of results for the assessment item EXAM across the subject is calculated, but any errors or non-numerics are excluded from the calculation.

 

Arguments

dnum is a multi-valued numeric expression

Returns

Number

Action/Description

Returns the sum of all items in the group expression.

If any non-numeric value is found, an error is returned.

Example syntax

DSum({UnitValue})

DSum(Subject.ABSENT)

Example usage/s

The first example, used in an analysis, is calculating the sum of all items in the array UnitValue.

The other example, from a calculated assessment item, returns the sum of all results contained in the ABSENT assessment item for the subject.

 

Arguments

dnum is a multi-valued numeric expression

dcond is a multi-valued Boolean condition

Returns

Number

Action/Description

Returns the sum of all items in the group expression where the condition is met.

If any non-numeric value is found, an error is returned.

Example syntax

DSumIf({UnitValue}, {EnteredValue} <> "N")

Example usage/s

In an analysis, assuming a source which is a single final result for each subject and a formula which determines the value of a unit based on the subject code, and an Aggregate Operator which groups by student code, the above formula would give the total unit points awarded for all subjects, where the final result was other than N.

Note that this could also be obtained by adding a restriction in the Source Operator.

However, if the total points possible was desired, as well as the total points obtained, a single Aggregate operator could be used with two formulae: one a DSum and the other a DSumIf.

 

Arguments

dnum is a multi-valued numeric expression

Returns

Number

Action/Description

Returns the sum of all items in the group expression, ignoring any errors or non-numeric values.

Example syntax

DSumX({UnitValue})

Example usage/s

Similar to the DSum function except that any items in the multi-valued expression that are invalid or non-numeric are ignored.

 

Arguments

dvals is a multi-valued expression

Returns

An array of the unique values

Action/Description

Returns a list of the values found in the group expression, ignoring any duplicates.

Example syntax

DUnique({EnteredResult})

Example usage/s

The above example, used in an Aggregate operator which is grouping results by student, will extract all of the unique results assigned to each student. Therefore, if a student received A for three tasks, A would appear once in the returned array.

 

 

minusMathematical Functions

Mathematical functions deal with numeric values, combining and converting them. Many of the functions have been designed especially for Accelerus, in order to deal with missing results which can skew mathematical results.

For example, the AvgX function takes the average of a listed set of results, but excludes any which are blank or use special values such as NA or UG.

 

AI CalcFunctionsMaths

 

Arguments

num is a numeric expression

Multiple numeric expressions may be entered

Returns

Number

Action/Description

Returns the average of all expressions in the list.

Example syntax

Avg({Calc1}/40, {Calc2}/25, {Calc3}/70) * 100

Example usage/s

Returns the average of the values of the three {Calc} fields listed. If any of the values is non-numeric, the result is an error.

In the example shown, {Calc1} is marked out of 40, {Calc2} out of 25 and {Calc3} out of 70. The result of the AVG function will therefore be a value between 0 and 1. Multiplying it by 100 gives a percentage.

 

Arguments

num is a numeric expression

Multiple numeric expressions may be entered

Returns

Number

Action/Description

Returns the average of all expressions in the list, including blanks and non-numerics which are replaced with zero.

Example syntax

AvgI({Calc1}, {Calc2}, {Calc3})

Example usage/s

Calculates the average of the given numeric expressions, but if any non-numeric value is found, it is replaced with 0.

 

Arguments

num is a numeric expression

Multiple numeric expressions may be entered

Returns

Number

Action/Description

Returns the average of all expressions in the list, excluding any blanks or non-numerics.

Example syntax

AvgX({Calc1}, {Calc2}, {Calc3})

Example usage/s

Calculates the average of the given numeric expressions, but if any non-numeric value is found, it is not considered in the calculation. For example, if {Calc1} was 10, {Calc2} was 14 and {Calc3} had a value of "NA", the exclusive average is (10 + 14) / 2 = 12. By comparison, the plain average would be an error and the inclusive average would be (10 + 14 + 0) / 3 = 8

 

Arguments

num1 is a numeric expression to be rounded

num2 is a numeric expression that defines the rounding factor

Returns

Number

Action/Description

Returns the value of num1 rounded up to the next multiple of num2.

Example syntax

Ceiling({TestAvg}, 0.5)

Example usage/s

Rounds the result of TestAvg up to the next multiple of 0.5.

Note that num1 and num2 must have the same sign, ie they must both be positive numbers or both negative numbers.

 

Arguments

num1 is a numeric expression to be rounded

num2 is a numeric expression that defines the rounding factor

Returns

Number

Action/Description

Returns the value of num1 rounded down to the next multiple of num2.

Example syntax

Floor({TestAvg}, 0.5)

Example usage/s

Rounds the result of TestAvg down to the next multiple of 0.5.

Note that num1 and num2 must have the same sign, ie they must both be positive numbers or both negative numbers.

 

Arguments

num is a numeric expression

Returns

Number

Action/Description

Returns just the integer, or whole number, portion of the given number.

Example syntax

Integer({TestAvg})

Example usage/s

If only the whole number part of a value is of interest, the Integer function can be used to calculate that. Frequently average functions return decimal values. The Integer function can be used to ignore the fractional component.

Note that the Integer function always rounds down in value. For example Integer(3.9) = 3. This includes negative numbers such that Integer(-3.9) = -4

 

Arguments

Expr is any numeric or string expression

Multiple expressions may be entered

Returns

Number or text string

Action/Description

Returns the maximum value of the list of expressions.

The list is scanned first to see if all values are numeric. If so, a numeric maximum is returned. Otherwise the text maximum is returned.

Example syntax

Max({WRAvg}, {ATAvg}, {TestAvg})

Example usage/s

Assuming WRAvg, ATAvg and TestAvg are fields that hold each student's average for work requirements, assessment tasks and tests, the above formula would return the best numeric value of the three fields.

The distinction between numeric and string comparisons is important. If the values above were 1, 11 and 10, the maximum would be 11. However if the values were 1, 11 and NA, the maximum would be NA as N is greater than 1 and 2.

Also, the maximum value in an A to E marking scheme would be E and not A.

 

Arguments

Expr is any numeric or string expression

Multiple expressions may be entered

Returns

Number or text string

Action/Description

Returns the minimum value of the list of expressions.

The list is scanned first to see if all values are numeric. If so, a numeric minimum is returned. Otherwise the text minimum is returned.

Example syntax

Min({HG1Avg}, {HG2Avg}, {HG3Avg}, {HG4Avg})

Example usage/s

Assuming that a school has four Home Groups and an average value had been aggregated for each Home Group, the above function would return the minimum value that had been obtained by students in a Home Group.

 

Arguments

val is a number which is to be translated to another number using the sets of source and target mapping pairs

src is a source mapping value of a mapping pair

tgt is a target mapping value of a mapping pair

Returns

Number

Action/Description

This function produces the same results as the predefined Multi-Linear mapping calculation whereby students' results can be recalculated or mapped to other values, according to their relative positions within the minimum and maximum and other specified mappings. It works on pairs of source and target values, with the first pair being the mapping for the minimum value and the last being for the maximum.

Example syntax

MultiLinear({EXAM}, DMin({Subject.EXAM}), 40, 50, 65, DMax({Subject.EXAM}), 100)

Example usage/s

The results in the EXAM assessment item are being mapped, using the following pairs of target and source values:

The minimum result scored by a student is to be mapped to the value 40.
50 is to be mapped to 65.
The maximum result in the subject is to be mapped to 100.

The MultiLinear function may be used in a Formula Operator in an analysis, but to use the maximum and minimum values in the example above would require these to be calculated first within an Aggregate Operator.

 

Arguments

num is a number indicating the maximum number to be returned

Returns

Number

Action/Description

Returns a random number between 1 and the given number inclusive.

Example syntax

Random(10)

Example usage/s

Random may be used to extract a random sample of results. For example, in an analysis, to get a 10% random sample, use a Filter Operator with the formula set to Random(10) = 10.

Note that in small sets, random values may not be evenly distributed. In an analysis, if you need an exact number of samples, it is better to create a formula field with a formula of Random(20000), feed that through a Rank Operator and then use a Filter Operator to filter off the desired number of samples.

 

Arguments

num1 is a fractional value to be rounded off

num2 is the number of decimal places num1 is to be rounded off to

Returns

Number

Action/Description

Returns the rounded value of a fractional number using a given number of decimal places. The result always contains the specified number of decimal places, even if zeros, eg Round(12, 2) returns 12.00.

Example syntax

Round(({CountBoys}/{CountTotal} * 100), 2)

Example usage/s

To clean up the number of decimal places returned by an Average or other calculation.

 

Arguments

num is a numeric expression

Multiple numeric expressions may be entered

Returns

Number

Action/Description

Returns the sum of all expressions in the list.

Example syntax

Sum({Yr8Units}, {Yr9Units} * 2, {Yr10Units} * 3)

Example usage/s

Sum may be used to add together a list of numeric expressions. It is the same as using the addition operator (+) between each expression.

Any non-numeric values will give an error.

 

Arguments

num is a numeric expression

Multiple numeric expressions may be entered

Returns

Number

Action/Description

Returns the sum of all expressions in the list, excluding any non-numerics.

Example syntax

SumX({Yr8Units}, {Yr9Units} * 2, {Yr10Units} * 3)

Example usage/s

SumX may be used to add together a list of numeric expressions in the same manner as the plain Sum function. However, any non-numeric values are ignored.

 

Arguments

val is a numeric value that is to be evaluated

fact is a number describing the factor of the value, typically the maximum value that val could be

wght is a number that gives the relative weighting of val within the set of values

newfact is a number describing the new overall factor of the expression.

Returns

Number

Action/Description

Returns the weighted average of several values specified by triplets detailing the value, the factor (or maximum value), and the relative weighting, finally followed by the new factor, ie the new maximum value.

If any of the values being evaluated are blank or an error value, the weighted average returns the blank or error value.

A weighting of 0 in any of the triplets excludes that triplet from the calculation regardless of its value. This can be used to produce a weighted average from the remaining triplets using their relative weightings, but still adjusted to consider the final maximum value.

Example syntax

WAvg({TASK1}, 50, 0.5, {TASK2}, 100, 0.3, {TASK3}, 25, 0.2, 100)

OR

WAvg({TASK1}, {TASK1.Max}, 50, {TASK2}, {TASK2.Max}, 30, {TASK3}, {TASK3.Max}, 20, 100)

OR

WAvg(

{TASK1}, {TASK1.Max}, if({TASK1} ="" or IsMissing({TASK1}) or not IsNumber({TASK1}), 0, 50),

{TASK2}, {TASK2.Max}, if({TASK2} ="" or IsMissing({TASK2}) or not IsNumber({TASK2}), 0, 30),

{TASK3}, {TASK3.Max}, if({TASK3} ="" or IsMissing({TASK3}) or not IsNumber({TASK3}), 0, 20),

100)

Example usage/s

Allows multiple numeric results to be averaged, with each being able to be assigned their relative weighting. The valid range of numbers for each of the results can vary.

In the first example above, three tasks are evaluated with a weighted average out of 100 to be calculated. TASK1 is out of 50 but is to make up 0.5 or 50% of the weighted average; TASK2 only makes up 0.3, despite it being out of 100, and so forth …

The second example above is identical to the first except it uses the assessment items' maximum value field – eg {TASK1.Max} – to determine the factor for each item, thereby not requiring you to know the maximum value for each field beforehand.

The weightings are relative, so weightings of 0.5, 0.3 and 0.2 are equivalent to 50, 30 and 20 and also equivalent to 15, 9 and 6.

The third example is for the same purpose as the first two, but shows how to exclude any missing or error values from the weighted calculation. Note how the formula is spread over multiple lines to make it easier to see its structure.

 

 

minusOther Functions

The Other functions do not cleanly fall into a single category but include some of the most widely used functions such as If() and MultiIf().

Also, several of these functions are used to perform checks on the data, eg IfMissing(), IsNumber(), etc, and are often used within other functions or expressions, to deal with exceptions or to ensure that inappropriate data is not being included in a calculation.

 

RA FunctionsOther

 

Arguments

cond is a Boolean condition

expr1 is the value to return if the condition is true

expr2 is the value to return if the condition is false

Returns

Number or text string

Action/Description

If the condition is met, it returns the first expression, otherwise it returns the second expression.

Example syntax

If({EnteredValue} = "S", "Satisfactory", "-")

Example usage/s

The If function is used wherever a choice of values is required based on some condition. In the example above, an S result should print Satisfactory, but anything else will just print a dash.

Nested Ifs may be used, but the MultiIf function may be used instead, with greater ease.

 

Arguments

expr1 is the expression to be evaluated

expr2 is the value to be returned if expr1 is an error

Returns

Number or text string

Action/Description

Allows an expression to be evaluated to ensure it does not contain an error. If it does, the second expression is returned. Otherwise, the first expression is returned. This function is the equivalent to the following which uses an If and an IsError function:

If(IsError(expr1), expr2, expr1)

IfError is easier to understand and much quicker to evaluate than the equivalent calculation above, especially in cases where expr1 is a lengthy expression.

Example syntax

IfError( {TOTAL} / {ATTEMPTS}, "NA")

Example usage/s

If an error is found evaluating TOTAL / ATTEMPTS, the text NA is returned; otherwise the result of TOTAL / ATTEMPTS is returned. An error value would normally be returned if ATTEMPTS has a value of 0. The IfError function catches this and returns the text NA instead.

 

Arguments

expr1 is the expression to be evaluated

expr2 is the value to be returned if expr1 is an error

Returns

Number or text string

Action/Description

Allows an expression to be evaluated to ensure it exists, ie is not a blank result. If it is missing, the second expression is returned. Otherwise, the first expression is returned.

This function is the equivalent to the following which uses an If and an IsMissing function:

If(IsMissing(expr1), expr2, expr1)

IfMissing is easier to understand and much quicker to evaluate than the equivalent calculation above, especially in cases where expr1 is a lengthy expression.

Example syntax

IfMissing({EXAM}, DAvgX({Class.EXAM}))

Example usage/s

In the example above, if the student's exam result is missing, the Class average for the exam is returned instead.

 

Arguments

expr1 is the expression to be evaluated

expr2 is the value to be returned if expr1 is not a number

Returns

Number or text string

Action/Description

Allows an expression to be evaluated to ensure it returns a number. If it is not a number, the second expression is returned. Otherwise, the first expression is returned. This function is the equivalent to the following which uses an If and an IsNumber function:

If(Not IsNumber(expr1), expr2, expr1)

IfNotNumber is easier to understand and much quicker to evaluate than the equivalent calculation above, especially in cases where expr1 is a lengthy expression.

If expr1 is missing or is an error, the result will be the missing or error value. To catch missing or error values as well as non-numeric values, use a combination of functions:

IsError(IsMissing(IsNotNumber(expr1, expr2), expr2), expr2)

Example syntax

IfNotNumber({TASK1}, 0)

Example usage/s

If the student received a result for TASK1 which is not a number, this function will return 0 instead.

 

Arguments

expr is the expression to be evaluated

Returns

True if the expression is an error

Action/Description

Allows an expression to be evaluated to ensure it does not contain an error.

Example syntax

If(IsError({EXAM}), "NA", {EXAM})

Example usage/s

If an error is found in the exam result, NA is used. Otherwise, the exam result is used. The above, using both If and IsError functions, is the equivalent of:

IfError({EXAM}, "NA")

 

Arguments

expr is the expression to be evaluated

Returns

True if the expression is missing

Action/Description

Allows an expression to be evaluated to ensure it exists, ie is not a blank result.

Example syntax

If(IsMissing({TASK1}) OR IsMissing({TASK2}), "NA", {TASK1} + {TASK2})

Example usage/s

If the student's result for either TASK1 or TASK2 is missing, returns NA otherwise it returns the two results added together.

 

Arguments

expr is the expression to be evaluated

Returns

True if the expression is a number

Action/Description

Allows an expression to be evaluated to ensure it is a number.

Example syntax

If(IsNumber({AVGTASK1}) AND IsNumber({TASK2}), {TASK1} + {TASK2}, "NA")

Example usage/s

If both TASK1 and TASK2 are numbers, it returns the sum of the two tasks, otherwise it returns NA.

IsNumber is commonly used in conjunction with IsMissing and IsError for full validation of the expression.

 

Arguments

Pairs of arguments:

cond is a Boolean condition
expr is the value to return if the condition is true

The final expr is the value to return if no conditions are met

Returns

Numeric or text string

Action/Description

Works on pairs of conditions and expressions, working through the pairs from left to right until a condition is met. If no true conditions are found, the final expression is returned.

The MultiIf can be used instead of nesting If functions, making for much easier reading.

Example syntax

MultiIf({EnteredValue} = "S", "Satisfactory", {EnteredValue} = "U", "Unsatisfactory", "Unknown")

Example usage/s

The MultiIf function may be used wherever there are multiple outcomes dependent upon a different condition, allowing these to be worked through until the default value is assigned if no conditions are met.

In the example above, if a student receives an S result, Satisfactory is assigned. If this condition is not met, the result is then evaluated against the next paired condition and expression, ie if the student receives U, Unsatisfactory is printed. Otherwise, if neither of these is met, Unknown is printed.

The same calculation could have been achieved using a nested If function:

If({EnteredValue} = "S", "Satisfactory", (If({EnteredValue} = "U", "Unsatisfactory", "Unknown)))

However, if there are many conditions, when using the If function, it can be difficult to ensure all brackets are dealt with correctly.

 

minusSpecial Functions

The Special folder only contains one function: Custom. These are usually used in assessment items.

Custom functions are graphic functions which convert data into drawings or pictures. The custom graphic functions are defined in an XML file which contains the instructions for converting the input data into the desired graphic output. These are then imported into the Accelerus database via File > Import > Graphic Function, in order that they may be used in assessment items.

 

AI CalcFunctionsSpecial

 

Custom(name, val, val ...)

Arguments

name is the name of the function, enclosed within quotation marks

val is an argument, however defined in the particular custom function, passed to the custom function, eg the width or height of the graphic.

Any number of parameters may be defined in the function and, therefore, the correct number must be included when the function is used.

Returns

A graphic consisting of one or more graphic elements, eg circles, rectangles, lines and text

Action/Description

Produces a graphic based on the values passed to it.

Example syntax

Custom('15PtDistrib'), 90, 8, {FINAL}, {Subject.FINAL})

Example usage/s

Graphs the distribution of A to E grades, including + and – variations of these, eg A-+, A, A–, etc, across a class or subject, for a particular assessment item. It draws 5 boxes, from E to A, the width of which is proportional to the number of students receiving that grade. Within this graphic, in the appropriate position, a dot is drawn to indicate the current student’s grade.

The distribution graph will graph the students’ results for the assessment item FINAL, across the whole subject, together with the individual student’s result for the FINAL item. The graph will be 90mm wide and 8mm high.

It will appear as seen here:

SRP Graphic Function Eg2

minusStatistical Functions

The Statistical functions allow the calculation of various statistics such as standard deviations, Z-scores and percentile rankings, particularly useful if a school wishes to moderate students' scores within the context of the scores of all students in a class or subject.

Many of these statistical functions form the basis of the predefined across-subject and across-class calculations available for insertion in assessment items. It is more common, and easier, for the predefined calculations to be used, rather than building equivalent formulae within the Calculation Editor.

 

AI CalcFunctionsStatistical

 

Arguments

expr is any expression

Returns

Numeric or text string

Action/Description

Returns the most common value in a list of values. If multiple values are encountered an equal number of times, the first of the multiple values in the expression list is returned.

Example syntax

Mode({TASK1}, {TASK2}, {TASK3},{TASK4})

Example usage/s

To find the most common result a student received in a series of tasks or assessment items.

Note that it a preferable to use the DMode function as it allows the specification of an ordered list from which to match the result in the case of there being more than one mode.

The equivalent function using DMode is:

DMode( [{TASK1}, {TASK2}, {TASK3}, {TASK4}], {TASK1.List}).

The square brackets around the list of TASK items define a multi-valued argument and the {TASK1.List} field is used to define the ordered list of possible values.

 

Arguments

expr is the percentile value to be found within the list

list is the set of all results in the statistical population from which the percentile value is sought

Returns

Number

Action/Description

Returns an estimate of the xth percentile within a population, where x, the expr, is a number between 0 and 1.

Example syntax

Percentile(0.5, [9,4,3,1,8,5,6,3])

Percentile(0.75,{Subject.EXAM})

Example usage/s

The first example is to illustrate how the Percentile function operates. The 50th percentile is obtained by sorting the list and picking the middle value. In this case there is no middle value, so an estimate is used, being half-way between the 4th and 5th values which is 4.5. The 0th percentile in this case is 1 and the 100th percentile is 9.

The second example is used to determine the 75th percentile from the set of exam marks.

 

Arguments

expr is the value for which the percentile ranking is sought

list is the set of all results in the statistical population within which the percentile ranking is to be calculated

Returns

Number

Action/Description

Returns the percentile rank of the expression value within the population, as a number between 0 and 1. The PercentRank function is calculated by dividing the number of items in the list below the expr value by the total number of items above and below the expr value.

The expr value must be contained within the list or an error is returned.

Example syntax

PercentRank(2,[13,12,11,8,4,3,2,1,1,2])

PercentRank({EXAM},{Subject.EXAM})

Example usage/s

Example 1 is for illustration. It returns the value 0.25 because there are 2 items below the value 2 and 6 above, equalling 2 / (2+6).

Example 2 returns a number between 0 and 1 for each student, representing their percentile ranking in the EXAM assessment item.

 

Arguments

expr is the value for which the ranking is sought

list is the set of all results in the statistical population within which the ranking is to be calculated

order is an optional value specifying the ordering of values

Returns

Number

Action/Description

Returns the rank value within a given population, optionally allowing a custom sort order to be specified.

Where there are tied rankings, rank positions are skipped, eg 1,2,3,3,5.

The custom sort orders that may be used are as follows:

Name

#

Ordering of Values

SORT_09AZ

0

Numbers in ascending order, then text in alphabetic order

SORT_90AZ

1

Numbers in descending order, then text in alphabetic order

SORT_09ZA

2

Numbers in ascending order, then text in reverse alphabetic order

SORT_90ZA

3

Numbers in descending order, then text in reverse alphabetic order

SORT_AZ09

4

Text in alphabetic order, then numbers in ascending order

SORT_AZ90

5

Text in alphabetic order, then numbers in descending order

SORT_ZA09

6

Text in reverse alphabetic order, then numbers in ascending order

SORT_ZA90

7

Text in reverse alphabetic order, then numbers in descending order

Example syntax

Ranking({EXAM}, {Subject.EXAM})

Ranking({EXAM}, {Subject.EXAM}, SORT_09AZ)

Ranking(Ordinal({TASK1}), Ordinal({Subject.TASK1}), SORT_90AZ)

Example usage/s

Student rankings on a particular task or item, within the subject or class can be calculated, as shown in the first example which ranks a student's EXAM result within the subject.

The second example contains a sort order to ensure that the ranking sort order is ascending numeric values.

The third example is using a descending numeric sort order as the best results for TASK1 have the lowest ordinal values.

 

Arguments

value is the number which is to be normalised

population is the set of all results in the statistical population

newmean is the mean that is to be used for the normalisation process

newstddev is the standard deviation to be used

Returns

Number

Action/Description

Standardises a value using its Z-score within the given population and normalising that against a specified new mean and standard deviation.

Example syntax

Standardised({EXAM}, {SUBJECT.EXAM}, 60, 12.5)

Example usage/s

Subject or class results may be moderated, standardised to a particular mean and standard deviation. In the above example, a student's EXAM result is standardised within the subject, using a mean of 60 and a standard deviation of 12.5.

The predefined Standardise calculation uses a similar function within its standardisation process.

 

Arguments

num is a numeric value

Returns

Number

Action/Description

Returns the standard deviation of the set of given numbers, ie the measure of how widely values are dispersed from the average of the numbers.

If any non-numeric value is found, eg NA, an error is returned.

Example syntax

StdDev({TASK1}, {TASK2}, {TASK3},{TASK4})

Example usage/s

The standard deviation of any number of results for a student could be calculated, as shown in the example above where four assessment item tasks are evaluated.

 

Arguments

num is a numeric value

Returns

Number

Action/Description

Returns the standard deviation of the population in the specified array, excluding any errors or non-numeric values.

Example syntax

StdDevX({TASK1}, {TASK2}, {TASK3},{TASK4})

Example usage/s

As with the StdDev function, the standard deviation of results for the four assessment items is calculated, but if any of the items contains an error or non-numerics, they are excluded from the calculation.

 

Arguments

expr is the value for which the Z-score is to be calculated

list is a multi-valued argument being the set of all results in the statistical population

Returns

Number

Action/Description

Returns the Z-score of the given number within the given population. Z-scores indicate how many standard deviations a given value is above or below the mean.

Example syntax

ZScore({EXAM},{Subject.EXAM})

Example usage/s

In the above example, a student's Z-score is calculated for the EXAM assessment item within the context of the scores for all students in the subject.

 

 

minusString Functions

String functions deal with the extraction of parts of a string and the conversion of single characters to and from their equivalent numeric ASCII value.

 

AI CalcFunctionsString

 

Arguments

str is a string value. If the string is longer than one character, only the first character is considered

Returns

Number

Action/Description

Returns the numeric ASCII value of the given character.

Example syntax

Asc({CalcGrade}) – Asc("A")

Example usage/s

Allows the conversion of a single character string into a number which then makes it available for a much greater range of operations.

The ASCII value of a character is a number representing the character: capital A has a value of 65; capital B has a value of 66; lower case a has a value of 97, and so forth.

In the example above, if CalcGrade is A, the result is 65 – 65 which is 0. If CalcGrade had a value of D, the result would be 3.

 

Arguments

num is a number which is to be translated to a string.

Returns

Text string

Action/Description

Returns the ASCII character corresponding to the given number.

Example syntax

Chr({WHAvg} + Asc("A"))

Example usage/s

Translates the given number into its equivalent ASCII character. This is the reverse of the Asc( ) function above.

In the example here, the Work Habit average numeric value is being converted back into a character string. The ASCII value of a capital A is being added to the average so that the resultant character is a capital letter. Assuming the range of average values is 0 to 4, the possible resultant character string is A to E.

 

Arguments

str is a text string value

num is a number indicating the number of characters to be extracted from str.

Returns

Text string

Action/Description

Returns a given number of characters starting from the left of the given string.

Example syntax

Left({EnteredValue},1)

Example usage/s

By only considering the first character of the result, it is possible to consider A+, A and A- results as a single A result category.

 

Arguments

str is a text string value

Returns

Number

Action/Description

Returns the length of a given string, ie number of characters the string contains.

Example syntax

If(Len({Subject.Name}) > 20, Left({Subject.Name}, 20) & "…", {Subject.Name})

Left({Subject.Code}, Len({Subject.Code}) - 1)

Example usage/s

In the first example, the length of the name of the subject is checked. If it is more than 20 characters, only the first 20 are taken and the string "…" is appended. Otherwise, the full subject name is retained.

The second example, which combines Left and Len functions, may be used to drop the final character from a subject code, even when the subject codes are of varying lengths.

 

Arguments

str is a text string value

num1 is a number indicating the starting position of the extraction.

num2 is an optional number indicating the maximum number of characters to be extracted from str.

Returns

Text string

Action/Description

Returns characters from the middle of a given string starting at a given position and having an optional maximum length. If num2 is not included, the rest of the string from the starting point is returned.

Example syntax

Mid({Subject.Code},4,1)

Example usage/s

If subject codes have been set up following a strict pattern where the fourth character of the code indicates the number of points the unit is worth, the above function allows the extraction of that value.

The Mid function can also be used to extract a number of characters from the right side of a string by specifying the start position, but not specifying a maximum length. This may be useful where the subject code has a year level as its first character, but the rest of the code indicates the subject area. Eg 7ENG, 8ENG, 7MATH, 8MATH. By getting the Mid({Subject.Code}, 2), the resulting values would be ENG, ENG, MATH, MATH. This allows the sorting or grouping of the subject codes by subject area without consideration of the year level.

 

Arguments

str is a text string value

num is a number indicating the number of characters to be extracted from str.

Returns

Text string

Action/Description

Returns a given number of characters extracted from the rightmost side of a given string.

Example syntax

Right({Student.HG.Code},2)

Example usage/s

If Home Group codes have been set up so that the last two characters are the year level, the above function extracts the year level, regardless of the length of the codes.

For example, if the codes were BROWN07 and RED10, the function would return 07 and 10.