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

    number

    Required: Yes
  • SUM

    numbers

    Required: Yes
  • POWER

    base

    Required: Yes
  • exponent

    Required: Yes
  • SQRT

    number

    Required: Yes
  • MOD

    number

    Required: Yes
  • divisor

    Required: Yes
  • QUOTIENT

    number

    Required: Yes
  • divisor

    Required: Yes
  • EXP

    number

    Required: Yes
  • LN

    number

    Required: Yes
  • LOG10

    number

    Required: Yes
  • FACT

    number

    Required: Yes
  • GCD

    numbers

    Required: Yes
  • LCM

    numbers

    Required: Yes
  • SIGN

    number

    Required: Yes
  • PI

    none

  • COMBIN

    n

    Required: Yes
  • k

    Required: Yes
  • ADD

    numbers

    Required: Yes
  • SUBTRACT

    numbers

    Required: Yes
  • MULTIPLY

    numbers

    Required: Yes
  • DIVIDE

    numbers

    Required: Yes
  • LOG

    number

    Required: Yes
  • base

    Required: No
  • SQRTPI

    number

    Required: Yes
  • COMBINA

    n

    Required: Yes
  • k

    Required: Yes
  • FACTDOUBLE

    number

    Required: Yes
  • MULTINOMIAL

    numbers

    Required: Yes
  • RAND

    none

  • RANDBETWEEN

    bottom

    Required: Yes
  • top

    Required: Yes

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.

FunctionReturnsBehaviourForm
ABSnumberThe value without its signABS(-5.5)
SUMnumberThe total of the valuesSUM(1.5, 2.5)
POWERnumberThe base raised to the exponentPOWER(2, 10)
SQRTnumberThe square rootSQRT(16)
MODwhole numberThe remainder after divisionMOD(10, 3)
QUOTIENTwhole numberThe whole part of the divisionQUOTIENT(7, 2)
EXPdecimale raised to the numberEXP(1.0)
LNdecimalThe natural logarithmLN(10.5)
LOG10decimalThe logarithm to base 10LOG10(1000.0)
FACTwhole numberThe factorialFACT(5)
GCDwhole numberThe greatest common divisorGCD(12, 18)
LCMwhole numberThe least common multipleLCM(4, 6)
SIGNwhole number-1 for a negative value, 0 for zero and 1 for a positive valueSIGN(-3)
PIdecimalThe value of piPI()
COMBINwhole numberThe number of ways to choose k items from nCOMBIN(5, 2)
ADDnumberThe total of the values. Use it where a variable sits in a sum, because + does not accept a variableADD(1, 2, 3)
SUBTRACTnumberThe first value less each of the othersSUBTRACT(10, 3, 2)
MULTIPLYnumberThe product of the valuesMULTIPLY(2, 3, 4)
DIVIDEnumberThe first value divided by each of the others. A zero divisor is refused with HTQL-8002DIVIDE(100, 2, 5)
LOGnumberThe 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-8004LOG(1000, 10)
SQRTPIdecimalThe square root of the number times pi. A negative number is refused with HTQL-8004SQRTPI(4)
COMBINAwhole numberThe 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-8004COMBINA(4, 2)
FACTDOUBLEwhole numberThe product of the number and every second smaller number down to 1 or 2FACTDOUBLE(7)
MULTINOMIALwhole numberThe number of ways to split the total into groups of those sizesMULTINOMIAL(2, 3, 4)
RANDdecimalA random decimal from 0 up to, but not including, 1. A new value on each callRAND()
RANDBETWEENwhole numberA random whole number from the bottom to the top, both included. A new value on each callRANDBETWEEN(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
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-8003An argument is not a number, such as ABS("x")Pass a number
HTQL-8004An argument is outside the function's range, such as SQRT(-1.0) or FACT(-1)Pass a value in range
HTQL-8002A 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. SUM skips 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.
  • ABS returns a wrapped number that ROUND refuses: ROUND(ABS(-2.345), 1) answers HTQL-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.
  • MOD and QUOTIENT cut a decimal argument to a whole number before they divide.
  • FACT and COMBIN work in whole numbers up to about 9.2 quintillion: FACT(20) is exact, but FACT(21) and COMBIN(30, 15) stop with an overflow message that has no error code.
  • A variable cannot sit beside + - * / in a statement (total / 3 is refused with HTQL-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.