Arithmetic functions
Do the everyday sums of a business statement with a function call: sizes, powers, roots, remainders, factorials and common divisors.
Syntax
Write the function's name and its arguments, anywhere a value is allowed, such as inside RETURN or SHOW.
ABS(number)
SUM(numbers)
POWER(base, exponent)
SQRT(number)
MOD(number, divisor)
QUOTIENT(number, divisor)
EXP(number)
LN(number)
LOG10(number)
FACT(number)
GCD(numbers)
LCM(numbers)
SIGN(number)
PI()
COMBIN(n, k)
ADD(numbers)
SUBTRACT(numbers)
MULTIPLY(numbers)
DIVIDE(numbers)
LOG(number, [base])
SQRTPI(number)
COMBINA(n, k)
FACTDOUBLE(number)
MULTINOMIAL(numbers)
RAND()
RANDBETWEEN(bottom, top)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
ABS
Required: YesnumberSUM
Required: YesnumbersPOWER
Required: Yesbase
Required: YesexponentSQRT
Required: YesnumberMOD
Required: Yesnumber
Required: YesdivisorQUOTIENT
Required: Yesnumber
Required: YesdivisorEXP
Required: YesnumberLN
Required: YesnumberLOG10
Required: YesnumberFACT
Required: YesnumberGCD
Required: YesnumbersLCM
Required: YesnumbersSIGN
Required: YesnumberPInone
COMBIN
Required: Yesn
Required: YeskADD
Required: YesnumbersSUBTRACT
Required: YesnumbersMULTIPLY
Required: YesnumbersDIVIDE
Required: YesnumbersLOG
Required: Yesnumber
Required: NobaseSQRTPI
Required: YesnumberCOMBINA
Required: Yesn
Required: YeskFACTDOUBLE
Required: YesnumberMULTINOMIAL
Required: YesnumbersRANDnone
RANDBETWEEN
Required: Yesbottom
Required: Yestop
Examples
Share 1000 cents between three people: the whole cents each gets, and the cent left over.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
ABS | number | The value without its sign | ABS(-5.5) |
SUM | number | The total of the values | SUM(1.5, 2.5) |
POWER | number | The base raised to the exponent | POWER(2, 10) |
SQRT | number | The square root | SQRT(16) |
MOD | whole number | The remainder after division | MOD(10, 3) |
QUOTIENT | whole number | The whole part of the division | QUOTIENT(7, 2) |
EXP | decimal | e raised to the number | EXP(1.0) |
LN | decimal | The natural logarithm | LN(10.5) |
LOG10 | decimal | The logarithm to base 10 | LOG10(1000.0) |
FACT | whole number | The factorial | FACT(5) |
GCD | whole number | The greatest common divisor | GCD(12, 18) |
LCM | whole number | The least common multiple | LCM(4, 6) |
SIGN | whole number | -1 for a negative value, 0 for zero and 1 for a positive value | SIGN(-3) |
PI | decimal | The value of pi | PI() |
COMBIN | whole number | The number of ways to choose k items from n | COMBIN(5, 2) |
ADD | number | The total of the values. Use it where a variable sits in a sum, because + does not accept a variable | ADD(1, 2, 3) |
SUBTRACT | number | The first value less each of the others | SUBTRACT(10, 3, 2) |
MULTIPLY | number | The product of the values | MULTIPLY(2, 3, 4) |
DIVIDE | number | The first value divided by each of the others. A zero divisor is refused with HTQL-8002 | DIVIDE(100, 2, 5) |
LOG | number | The logarithm of the number to the base. A whole number argument whose answer is whole, such as LOG(1000), comes back as a whole number; any other answer is a decimal. A value or base outside the range is refused with HTQL-8004 | LOG(1000, 10) |
SQRTPI | decimal | The square root of the number times pi. A negative number is refused with HTQL-8004 | SQRTPI(4) |
COMBINA | whole number | The number of ways to choose k items from n kinds when an item can be chosen again. With n of zero and k above zero it is refused with HTQL-8004 | COMBINA(4, 2) |
FACTDOUBLE | whole number | The product of the number and every second smaller number down to 1 or 2 | FACTDOUBLE(7) |
MULTINOMIAL | whole number | The number of ways to split the total into groups of those sizes | MULTINOMIAL(2, 3, 4) |
RAND | decimal | A random decimal from 0 up to, but not including, 1. A new value on each call | RAND() |
RANDBETWEEN | whole number | A random whole number from the bottom to the top, both included. A new value on each call | RANDBETWEEN(1, 6) |
A value that is not a number is refused with HTQL-8003 (#VALUE!), and a value outside the function's range, such as SQRT(-1.0), with HTQL-8004 (#NUM!).
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-8003 | An argument is not a number, such as ABS("x") | Pass a number |
HTQL-8004 | An argument is outside the function's range, such as SQRT(-1.0) or FACT(-1) | Pass a value in range |
HTQL-8002 | A divisor is zero, such as MOD(10, 0) or DIVIDE(1, 0) | Check the divisor first |
Limitations
- Arguments are numbers. Text such as
ABS("x")is refused.SUMskips an argument that is not a number rather than refusing it. - Write a logarithm's argument as a decimal, such as
10.5.LN(10), with a whole number, answers with a whole number (2), not 2.302585. ABSreturns a wrapped number thatROUNDrefuses:ROUND(ABS(-2.345), 1)answersHTQL-2001"Expected a numeric value, ExcelValue(F64(2.345))". Round first and take the sign off last,ABS(ROUND(-2.345, 1)), or use the value as it is.MODandQUOTIENTcut a decimal argument to a whole number before they divide.FACTandCOMBINwork in whole numbers up to about 9.2 quintillion:FACT(20)is exact, butFACT(21)andCOMBIN(30, 15)stop with an overflow message that has no error code.- A variable cannot sit beside
+ - * /in a statement (total / 3is refused withHTQL-1003). Pass it to ADD, SUBTRACT, MULTIPLY or DIVIDE instead.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use SUM with a list to total values you have collected.
- Use ADD, SUBTRACT, MULTIPLY and DIVIDE when a variable is part of the sum.
- Round a result with the rounding functions before you show it.