Formula Language
The formula language is similar to the languages in
traditional spreadsheets
with an important difference.
-
In addition to one line '=' expressions from
traditional spreadsheets
, formulas can be expressed in multiple steps or statements.
-
These steps can include If statements, For loops, statement blocks and other flow of control statements as well as local variables.
-
The If statement is used to replace cryptic if() functions. If() functions can be difficult to construct and read, especially when nested functions are employed.
-
The For statement is used to loop over the contents of a
or the results of a
.
-
Local variables are useful to calculate intermediate results rather using
for those calculations. Local variables help document the logic behind a calculation with meaningful names.
-
These statements are patterned after the C-family of languages that include Java and C#. The use of the ';' to terminated statements is not required.
-
,
,
,
and
are all written using the formula language.
-
A
traditional spreadsheets
style formula is written as follows:
-
-
For example, an If statements can be written as follows:
-
var sales = $A$6
var costs = $B$12
var gross = sales - costs
if (gross / costs > 50%) {
return "All good here"
}
else {
return backgroundColor("Improvements needed here", "red")
}
-
For example, a For statements can be written as follows:
-
var totalDeposits = 0
for (var transaction: A1:A15) {
if (transaction > 0) {
totalDeposits = totalDeposits + transaction
}
}
= totalDeposits
The formula language supports 9 data types. These data types define local variables and are returned as the formula result.
-
float: floating point -- with 15 decimal digits of precision.
-
fixed(<precision>): fixed point -- where the precision of the calculation is fixed to a user defined number of decimals, useful for precise, rounded monetary calculations.
-
text -- string of characters
-
date: date/time -- the serial calendar date and the fraction of the day.
-
time: The fraction of the day.
-
int: integer -- whole numbers, up to nineteen digits
-
boolean -- a true or false value.
-
user defined records -- data records that are generated by a
. See
.
-
var -- used where data type isn't explicitly enforced and takes on the natural type of the calculation.
For example:
/* a value of 0.333333333333333 */
float myFloat = 1 / 3
/* a value of 0.6667 */
fixed(4) myfixed = 2 / 3
text myText = "Hello, World"
/* a value of 1-Dec-2024 */
date myDate = Date(2024, 12, 1)
/* a value of 1:04:11 pm */
time myTime = Time(13, 4, 11)
int myInteger = 123
boolean myBoolean = true
MyRecordType myRecord = MyQuery(1, 2, 3)
/* a value of 0.833333333333334 */
var myAnyType = 5 / 6
= myAnyType
Queries