Available Functions

RA Functions A 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.

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}).

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

Numbers in ascending order, then text in alphabetic order

SORT_90AZ

Numbers in descending order, then text in alphabetic order

SORT_09ZA

Numbers in ascending order, then text in reverse alphabetic order

SORT_90ZA

Numbers in descending order, then text in reverse alphabetic order

SORT_AZ09

Text in alphabetic order, then numbers in ascending order

SORT_AZ90

Text in alphabetic order, then numbers in descending order

SORT_ZA09

Text in reverse alphabetic order, then numbers in ascending order

SORT_ZA90

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.

NB	When 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.

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.

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.

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.

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.

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:

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.

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.