This section is still very much in draft form.
Important Notes about Formulas
- Formulas are case sensitive.
- Formulas only work across (to other columns), not down (to data in the same column).
- Formulas can only be used for data on a data wall.
- Formulas do not currently work when using another formula column as an input.
- Formulas will not calculate if a score/input is blank for an input. The formula will output a blank.
- Additional resources are available in the Advanced Ion section of Fusion - https://fusion.ion-k12.com/c/advanced-ion/
| Formula | Description/ Comments | Example(s) |
|---|---|---|
isNumeric(expression) |
Returns TRUE if the entire expression is recognized as a number, otherwise it returns FALSE.Returns FALSE if the expression is a date. |
Example Inputs: 53, 459.95isNumeric(c1)Output: TRUEExample Input: 53L isNumeric(c1)Output: FALSE
|
isDate(expression) |
Returns TRUE if the expression is a date or is recognizable as a valid date;otherwise, returns FALSE |
Possible Inputs: 04/28/2022, April 28, 2022, 04.28.22, 04.28.2022 Output: TRUEInput: Friday, Spring, Period 1 Output: FALSE |
isNothing(variable) |
Returns TRUE if the expression represents an object variable that currently has no object assigned to it. Otherwise it returns FALSE.This expression is intended to work on reference types. A value type cannot hold a value of nothing. |
Input: Output: |
isBetween(variable1, variable2) |
The first numeric value is greater than or equal to the second numeric value (min), and less than or equal to the third numeric value (max). |
Input: isBetween(0,3)Output: 0, 1, 2, 3 |
notBetween(variable1, variable2) |
The first numeric value is less than or equal to the second numeric value (min), and greater than or equal to the third numeric value (max) |
Input: notBetween(0,3)Output: 0, 3, 4, 5, etc. |
mod(variable1, variable2) |
Returns the remainder of two numbers after division. The result of MOD carries the same sign as the divisor. |
Input: mod(10,-3)Output: -1 |
isLessThan(variable1, variable2) |
Tests for field contents that are less than the value entered. |
Input: Output: |
isGreaterThan(variable1, variable2) |
Takes into account nulls, which means that the output will be blank if a null exists. |
Input: Output: |
isLessThanEqual(variable1, variable2) |
Input: Output: |
|
isGreaterThanEqual(variable1, variable2) |
Input: Output: |
|
isEqual(variable1, variable2) |
Tests for an exact match between the field contents and the value entered. |
Input: Output: |
mid(variable, start, length) |
Returns a Variant (String) containing a specified number of characters from a string. Variable: Required. String expression from which characters are returned. If string contains Null, Null is returned. Start: Required; Long. Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string (""). Length: Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned. |
Example: My String = Mid Function Demo Input: mid(MyString, 1, 3)Output: Returns "Mid" |
left(variable, length) |
Variable: the string containing the characters you want to extract, or a reference to a column that contains text. Length: The number of characters you want LEFT to extract; if omitted, 1. If the length argument is a number that is larger than the number of characters available, the function returns the maximum characters available and does not raise an error. |
Data: abcdefg Input: left(x,3)Output: abc |
right(variable, length) |
Variable: the string containing the characters you want to extract, or a reference to a column that contains text. Length: The number of characters you want RIGHT to extract; if omitted, 1. If the length argument is a number that is larger than the number of characters available, the function returns the maximum characters available and does not raise an error. |
Data: abcdefg Input: right(x,3)Output: efg |
indexOf(variable, lookup) |
Given one argument: a sub string to search for, searches the entire calling string, and returns the index of the first occurrence of the specified sub string. Given a second argument: a number, the method returns the first occurrence of the specified sub string at an index greater than or equal to the specified number. |
Data: The quick brown fox jumps over the lazy dog. If the dog barked, was it really lazy? Input: indexof(dog, Output: |
rtrim(variable) |
Removes trailing spaces. |
Input: Output: |
ltrim(variable) |
Removes leading spaces. |
Input: Output: |
formatNumber(variable, numDigitsAfterDecimal) |
Used to round a number to a specific place value. |
Input: 83.855formatNumber(x,1)Output: 83.9 |
coalesce([variable, variable, variable]) |
Input: Output: |
|
len(variable) |
Determines the character length of a variable. |
Input: Level 4len(x)Output: 8 |
replace(variable, find, replace) |
Used to replace part of a value. |
Input: 1020Lreplace(x,"L","")Output: 1020 |
parseDate(variable, format) |
Converts a string of values to a valid date. |
Input: 2023-04-15parseDate(x,"yyyy-mm-dd")Output: 04/15/2023 |
| Operator/ Syntax |
Description/ Comments | Example |
|---|---|---|
| + | Add |
c1+c2 |
| - | Subtract |
c1-c2 |
| * | Multiply |
c1*c2 |
| / | Divide |
c1/c2 |
| () | Parentheses |
(c1+c2)/2 |
On a data wall, ion considers most values as strings, or just plain text. In order to use formulas, users will likely need to wrap each of the referenced column with the type of value that should be used.
| Formula | Description | Example |
|---|---|---|
toDouble() |
Converts the input to a decimal. |
Input: Output: |
toInteger() |
Converts the input to a integer. If the conversion if successful, the result will be an integer. If the conversion isn't sucessful, the result will be null. |
Input: 3.24516toInteger(x)Output: 3 |
toString() |
Converts the input to a string. |
Input: Output: |
toDate() |
Converts the input to a date. |
Input: Output: |
When mapping a file in ion, use the following formulas. Formulas only reference the column in which they reside and cannot reference another column within the same file. Please note: users should receive file mapping training from ion before attempting this function.
l
| Formula | Description | Example |
|---|---|---|
lookupYear(x, orig) |
Uses a date to determine which year the data should be associated with. |
Input: 9/15/2022 Output: 2023 |
lookupTerm(x, orig) |
Uses a date to determine which term the data should be associated with. |
Input: 9/15/2022 Output: Fall |
left(x, length) |
length: the number of characters you want to extract from the left side moving right. |
Input: 99-05 Formula: left(x, 2) Output: 99 |
right(x, length) |
length: the number of characters you want to extract from the right side moving left. |
Input: 99-05 Formula: right(x, 2) Output: 05 |
parseDate(x, format) |
Converts a string a values to a valid date. format: enter the format of the source data. |
Input: 2023-04-15 Formula: parseDate(x, "yyyy-mm-dd") Output: 04/15/2023 |
Goal: Determine the average score of columns 1-6, ignoring blank cells.
Full Formula:
(toInteger(c1)+toInteger(c2)+toInteger(c3)+toInteger(c4)+toInteger(c5)+toInteger(c6))/(iif(isNumeric(c1),1,0)+iif(isNumeric(c2),1,0)+iif(isNumeric(c3),1,0)+iif(isNumeric(c4),1,0)+iif(isNumeric(c5),1,0)+iif(isNumeric(c6),1,0))
Formula Explanation:
The numerator/dividend is determined with the first part of the formula:
(toInteger(c1)+toInteger(c2)+toInteger(c3)+toInteger(c4)+toInteger(c5)+toInteger(c6))
Columns 1-6 reference the "percentile" field. The toInteger() formula allows the values in those columns to be used as an integer. After converting each column to an integer, we add up their values.
The denominator/divisor is determined with the second part of the formula:
(iif(isNumeric(c1),1,0)+iif(isNumeric(c2),1,0)+iif(isNumeric(c3),1,0)+iif(isNumeric(c4),1,0)+iif(isNumeric(c5),1,0)+iif(isNumeric(c6),1,0))
Since we want to ignore blank values, we need to determine how many of the cells contain a number. iif(isNumeric(c1),1,0) means: if c1 is numeric, give it a value of 1, otherwise, give it a value of 0. Each column is tested the same way and then the "1"s are added up to give us the number of columns that have a valid value. We will then divide the numerator by that value to determine the average.