Functions

Calculations and expressions in prose can call these functions by name, with values in parentheses separated by ,, like SUM(rent, food, travel). Names match in any case. Each function works like the Excel function it names, exactly unless its entry says otherwise, and explains a mistake in plain language where Excel would show an error value.

IF, AND, OR and NOT take conditions, like total > 50, which Conditions explains.

SUM
AVERAGE
MIN
MAX
ROUND
ROUNDUP
ROUNDDOWN
INT
ABS
SQRT
POWER
MOD
PMT
PV
FV
IF
AND
OR
NOT
DATE
TODAY
YEAR
MONTH
DAY
WEEKDAY
DAYS
EDATE
EOMONTH
DATEDIF
NETWORKDAYS
WORKDAY
TIME
HOUR
MINUTE

SUM

SUM(number, …)

Adds up its values.

Works exactly like Excel’s SUM.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit.

Example

\begin{matrix} example & = & SUM (120, 45.5, 30) \\ = & 195.5 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

AVERAGE

AVERAGE(number, …)

Adds up its values and divides by how many there are.

Works exactly like Excel’s AVERAGE.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit.

Example

\begin{matrix} example & = & AVERAGE (120, 45.5, 30) \\ = & 65.1666666666667 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

MIN

MIN(number, …)

The smallest of its values.

Works exactly like Excel’s MIN.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit.

Example

\begin{matrix} example & = & MIN (120, 45.5, 30) \\ = & 30 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

MAX

MAX(number, …)

The largest of its values.

Works exactly like Excel’s MAX.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit.

Example

\begin{matrix} example & = & MAX (120, 45.5, 30) \\ = & 120 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

ROUND

ROUND(number, digits)

Rounds to a number of decimal places, with halves away from zero. Negative digits round to tens, hundreds and so on.

Works exactly like Excel’s ROUND.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit. digits can’t have a unit.

Example

\begin{matrix} example & = & ROUND (2.675, 2) \\ = & 2.68 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

ROUNDUP

ROUNDUP(number, digits)

Rounds away from zero to a number of decimal places. Negative digits round to tens, hundreds and so on.

Works exactly like Excel’s ROUNDUP.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit. digits can’t have a unit.

Example

\begin{matrix} example & = & ROUNDUP (3.14159, 3) \\ = & 3.142 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

ROUNDDOWN

ROUNDDOWN(number, digits)

Rounds towards zero to a number of decimal places. Negative digits round to tens, hundreds and so on.

Works exactly like Excel’s ROUNDDOWN.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit. digits can’t have a unit.

Example

\begin{matrix} example & = & ROUNDDOWN (3.14159, 3) \\ = & 3.141 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

INT

INT(number)

Rounds down to the nearest whole number, so negative numbers round away from zero.

Works exactly like Excel’s INT.

Doesn’t take values with units.

Example

\begin{matrix} example & = & INT (- 8.9) \\ = & - 9 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

ABS

ABS(number)

The number without its sign.

Works exactly like Excel’s ABS.

Takes values with units, which must all measure the same thing, as with +. The result is in the first value’s unit.

Example

\begin{matrix} example & = & | - 4 | \\ = & 4 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

SQRT

SQRT(number)

The positive square root.

Works exactly like Excel’s SQRT.

Takes values with units, and works out the result’s unit from theirs.

Example

\begin{matrix} example & = & \sqrt{16} \\ = & 4 \end{matrix}

Errors

Message	Excel error
Can't take the square root of a negative number	`#NUM!`
The result is too large to show	`#NUM!`

POWER

POWER(number, power)

Raises a number to a power. A fractional power is a root, so a power of 0.5 is a square root.

Works exactly like Excel’s POWER.

Takes values with units, and works out the result’s unit from theirs.

Example

\begin{matrix} example & = & {1.05}^{10} \\ = & 1.62889462677744 \end{matrix}

Errors

Message	Excel error
Can't divide by zero	`#DIV/0!`
Can't raise a negative number to a fractional power	`#NUM!`
Can't raise `0` to the power `0`	`#NUM!`
The result is too large to show	`#NUM!`

MOD

MOD(number, divisor)

The remainder after dividing a number by a divisor, with the same sign as the divisor.

Works exactly like Excel’s MOD.

Doesn’t take values with units.

Example

\begin{matrix} example & = & MOD (- 3, 2) \\ = & 1 \end{matrix}

Errors

Message	Excel error
Can't divide by zero	`#DIV/0!`
The result is too large to show	`#NUM!`

PMT

PMT(rate, nper, pv, [fv = 0], [type = 0])

The payment each period that pays off a loan, or saves up to a future value, at a fixed rate per period.

Works exactly like Excel’s PMT.

Doesn’t take values with units.

Example

\begin{matrix} example & = & PMT (\frac{0.05}{12}, 360, 200,000) \\ = & - 1,073.64324602428 \end{matrix}

Note

Money paid out is negative and money received is positive, as in Excel. A loan you receive is a positive present value, so the payment that repays it comes out negative. Put a minus sign in front to show the repayment as a positive amount.

\begin{matrix} example & = & - PMT (\frac{0.05}{12}, 360, 200,000) \\ = & 1,073.64324602428 \end{matrix}

Errors

Message	Excel error
Can't work out a payment over 0 periods	`#NUM!`, `#DIV/0!`
Can't work out a payment at this rate over this many periods	`#NUM!`
Can't use a rate below `-1` over part of a period	`#NUM!`
The result is too large to show	`#NUM!`

PV

PV(rate, nper, pmt, [fv = 0], [type = 0])

What a payment each period, and a future value, are worth now, at a fixed rate per period.

Works exactly like Excel’s PV.

Doesn’t take values with units.

Example

\begin{matrix} example & = & PV (\frac{0.05}{12}, 360, - 1,000) \\ = & 186,281.617046076 \end{matrix}

Note

\begin{matrix} example & = & - PMT (\frac{0.05}{12}, 360, 200,000) \\ = & 1,073.64324602428 \end{matrix}

Errors

Message	Excel error
Can't work out a present value at a rate of `-1`	`#DIV/0!`
Can't use a rate below `-1` over part of a period	`#NUM!`
The result is too large to show	`#NUM!`

FV

FV(rate, nper, pmt, [pv = 0], [type = 0])

What a payment each period, and a present value, are worth after the last period, at a fixed rate per period.

Works exactly like Excel’s FV.

Doesn’t take values with units.

Example

\begin{matrix} example & = & FV (\frac{0.05}{12}, 120, - 100) \\ = & 15,528.2279445668 \end{matrix}

Note

\begin{matrix} example & = & - PMT (\frac{0.05}{12}, 360, 200,000) \\ = & 1,073.64324602428 \end{matrix}

Errors

Message	Excel error
Can't use a rate below `-1` over part of a period	`#NUM!`
The result is too large to show	`#NUM!`

IF

IF(logical_test, value_if_true, [value_if_false = FALSE])

Gives one value if a condition is TRUE and another if it’s FALSE. Leave out the third value to give FALSE.

Works exactly like Excel’s IF.

A condition can be a plain number, which is TRUE unless it’s 0, but a value with a unit must be compared with something first, like length > 0 cm. The values it gives can have units, even different ones, like IF(metric, 5 cm, 2 in).

Example

\begin{matrix} example & = & IF (45 \geq 50, 0, 4.99) \\ = & 4.99 \end{matrix}

Note

IF only works out the value it gives, as in Excel, so an error in the other value, or a reference in it to a calculation without a value, has no effect. That lets it guard a division by zero.

\begin{matrix} example & = & IF (0 = 0, 0, \frac{120}{0}) \\ = & 0 \end{matrix}

Errors

Message	Excel error
Can't use `length` as a condition: it has a unit, so compare it with something, like `length > 0 cm`	`#VALUE!`
The result is too large to show	`#NUM!`

AND

AND(logical, …)

TRUE if every one of its conditions is TRUE, otherwise FALSE. It works out every one, as Excel does, so an error in any of them is an error.

Works exactly like Excel’s AND.

A condition can be a plain number, which is TRUE unless it’s 0, but a value with a unit must be compared with something first, like length > 0 cm.

Example

\begin{matrix} example & = & AND (TRUE, FALSE) \\ = & FALSE \end{matrix}

Errors

Message	Excel error
Can't use `length` as a condition: it has a unit, so compare it with something, like `length > 0 cm`	`#VALUE!`
The result is too large to show	`#NUM!`

OR

OR(logical, …)

TRUE if any one of its conditions is TRUE, otherwise FALSE. It works out every one, as Excel does, so an error in any of them is an error.

Works exactly like Excel’s OR.

A condition can be a plain number, which is TRUE unless it’s 0, but a value with a unit must be compared with something first, like length > 0 cm.

Example

\begin{matrix} example & = & OR (TRUE, FALSE) \\ = & TRUE \end{matrix}

Errors

Message	Excel error
Can't use `length` as a condition: it has a unit, so compare it with something, like `length > 0 cm`	`#VALUE!`
The result is too large to show	`#NUM!`

NOT

NOT(logical)

TRUE if its condition is FALSE, and FALSE if it’s TRUE.

Works exactly like Excel’s NOT.

A condition can be a plain number, which is TRUE unless it’s 0, but a value with a unit must be compared with something first, like length > 0 cm.

Example

\begin{matrix} example & = & NOT (FALSE) \\ = & TRUE \end{matrix}

Errors

Message	Excel error
Can't use `length` as a condition: it has a unit, so compare it with something, like `length > 0 cm`	`#VALUE!`
The result is too large to show	`#NUM!`

DATE

DATE(year, month, day)

The date of a year, month and day. A month or day out of range rolls over, so month 13 is January of the next year and day 0 the last day of the month before.

Works exactly like Excel’s DATE.

Doesn’t take values with units.

Example

\begin{matrix} example & = & DATE (2,026, 10, 22) \\ = & Thu, Oct 22, 2026 \end{matrix}

Note

As in Excel, a year from 0 to 1899 has 1900 added, so DATE(26, 10, 22) is in 1926. Write a date before 1900 as it is, like 1815-06-18.

\begin{matrix} example & = & DATE (26, 10, 22) \\ = & Fri, Oct 22, 1926 \end{matrix}

Errors

Message	Excel error
A date's year must be from `0001` to `9999`	`#NUM!`
The result is too large to show	`#NUM!`

TODAY

TODAY()

Today's date where the sheet is read.

Works exactly like Excel’s TODAY.

Takes no values.

Example

\begin{matrix} example & = & TODAY () \\ = & Thu, Oct 22, 2026 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

YEAR

YEAR(date)

The year of a date, as a number.

Works like Excel’s YEAR, except where this entry says otherwise.

date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & YEAR (Thu, Oct 22, 2026) \\ = & 2,026 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

MONTH

MONTH(date)

The month of a date, as a number from 1 for January to 12 for December.

Works like Excel’s MONTH, except where this entry says otherwise.

date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & MONTH (Thu, Oct 22, 2026) \\ = & 10 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

DAY

DAY(date)

The day of the month of a date, as a number from 1 to 31.

Works like Excel’s DAY, except where this entry says otherwise.

date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & DAY (Thu, Oct 22, 2026) \\ = & 22 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

WEEKDAY

WEEKDAY(date, [type = 1])

The day of the week of a date, as a number. Type 1 counts from 1 for Sunday to 7 for Saturday, type 2 from 1 for Monday to 7 for Sunday, and type 3 from 0 for Monday to 6 for Sunday. Types 11 to 17 count from 1 for Monday, Tuesday and so on to Sunday.

Works like Excel’s WEEKDAY, except where this entry says otherwise.

date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & WEEKDAY (Thu, Oct 22, 2026) \\ = & 5 \end{matrix}

Errors

Message	Excel error
The type of `WEEKDAY` must be 1, 2, 3 or 11 to 17	`#NUM!`
The result is too large to show	`#NUM!`

DAYS

DAYS(end_date, start_date)

How many days the end date is after the start date, as a number, negative if it’s before. Taking one date from another gives the same count in days, like 42 d.

Works like Excel’s DAYS, except where this entry says otherwise.

end_date and start_date take dates, like 2026-10-22, never numbers, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & DAYS (Mon, Mar 15, 2021, Mon, Feb 1, 2021) \\ = & 42 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

EDATE

EDATE(start_date, months)

The date a number of months after the start date, or before it if that’s negative, on the same day of the month, or the last day of a shorter month. Any fraction of the months is ignored. It moves a date as adding months does, like 2026-01-31 + 1 month.

Works like Excel’s EDATE, except where this entry says otherwise.

start_date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & EDATE (Sat, Jan 31, 2026, 1) \\ = & Sat, Feb 28, 2026 \end{matrix}

Errors

Message	Excel error
A date's year must be from `0001` to `9999`	`#NUM!`
The result is too large to show	`#NUM!`

EOMONTH

EOMONTH(start_date, months)

The last day of the month a number of months after the start date’s, or before it if that’s negative, so 0 gives the last day of its own month. Any fraction of the months is ignored.

Works like Excel’s EOMONTH, except where this entry says otherwise.

start_date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & EOMONTH (Thu, Oct 22, 2026, 0) \\ = & Sat, Oct 31, 2026 \end{matrix}

Errors

Message	Excel error
A date's year must be from `0001` to `9999`	`#NUM!`
The result is too large to show	`#NUM!`

DATEDIF

DATEDIF(start_date, end_date, unit)

How far the end date is after the start date, as a number, in the unit: "y" counts whole years, "m" whole months and "d" days. "md" counts the days left over after the whole months, "ym" the months left over after the whole years, and "yd" the days left over after the whole years. The unit can be in any case.

Works like Excel’s DATEDIF, except where this entry says otherwise.

start_date and end_date take dates, like 2026-10-22, never numbers, unlike in Excel. unit takes text, like "m". Doesn’t take values with units.

Example

\begin{matrix} example & = & DATEDIF (Fri, Jun 1, 2001, Thu, Aug 15, 2002, "m") \\ = & 14 \end{matrix}

Note

A whole month is counted as adding months to a date counts it, so 2026-01-31 + 1 month is 2026-02-28 and that’s a whole month after it. Excel counts no whole month there, and its "md" can give a negative number of days, which this never does.

\begin{matrix} example & = & DATEDIF (Sat, Jan 31, 2026, Sat, Feb 28, 2026, "m") \\ = & 1 \end{matrix}

Errors

Message	Excel error
The end date in `DATEDIF` can't be before the start date	`#NUM!`
The unit of `DATEDIF` must be `"y"`, `"m"`, `"d"`, `"md"`, `"ym"` or `"yd"`	`#NUM!`
The result is too large to show	`#NUM!`

NETWORKDAYS

NETWORKDAYS(start_date, end_date)

How many working days, Monday to Friday, there are from the start date to the end date, counting both, as a number, negative if the end date is earlier.

Works like Excel’s NETWORKDAYS, except where this entry says otherwise.

start_date and end_date take dates, like 2026-10-22, never numbers, unlike in Excel. Doesn’t take values with units.

Doesn’t take Excel’s holidays yet.

Example

\begin{matrix} example & = & NETWORKDAYS (Thu, Oct 1, 2026, Sat, Oct 31, 2026) \\ = & 22 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

WORKDAY

WORKDAY(start_date, days)

The date a number of working days, Monday to Friday, after the start date, or before it if that’s negative, stepping over weekends. Any fraction of the days is ignored.

Works like Excel’s WORKDAY, except where this entry says otherwise.

start_date takes a date, like 2026-10-22, never a number, unlike in Excel. Doesn’t take values with units.

Doesn’t take Excel’s holidays yet.

Example

\begin{matrix} example & = & WORKDAY (Thu, Oct 22, 2026, 10) \\ = & Thu, Nov 5, 2026 \end{matrix}

Errors

Message	Excel error
A date's year must be from `0001` to `9999`	`#NUM!`
The result is too large to show	`#NUM!`

TIME

TIME(hour, minute, second)

The time of day of an hour, minute and second. Minutes and seconds of 60 or more roll over into hours, and 24 hours or more wrap round the clock, so hour 25 is 01:00. Any fraction of each is ignored.

Works exactly like Excel’s TIME.

Doesn’t take values with units.

Example

\begin{matrix} example & = & TIME (9, 30, 0) \\ = & 9:30 AM \end{matrix}

Errors

Message	Excel error
The hours, minutes and seconds in `TIME` can't add up to less than `0`	`#NUM!`
The hours, minutes and seconds in `TIME` must each be at most `32767`	`#NUM!`
The result is too large to show	`#NUM!`

HOUR

HOUR(time)

The hour of a time of day, as a number from 0 to 23.

Works like Excel’s HOUR, except where this entry says otherwise.

time takes a time of day, like 09:00, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & HOUR (5:30 PM) \\ = & 17 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

MINUTE

MINUTE(time)

The minute of a time of day, as a number from 0 to 59.

Works like Excel’s MINUTE, except where this entry says otherwise.

time takes a time of day, like 09:00, never a number, unlike in Excel. Doesn’t take values with units.

Example

\begin{matrix} example & = & MINUTE (5:30 PM) \\ = & 30 \end{matrix}

Errors

Message	Excel error
The result is too large to show	`#NUM!`

Conditions

A condition is TRUE or FALSE, like total > 50. It compares two values with one of these operators, which the working shows as maths:

Write	Means	Shown as
`=` or `==`	equal to	=
`<>` or `!=`	not equal to	≠
`<`	less than	<
`>`	greater than	>
`<=`	less than or equal to	≤
`>=`	greater than or equal to	≥

The first = on a line names the calculation, and any later one compares, so match = a = b is TRUE when a equals b.

Values with units are converted to the same unit before they’re compared, so they must measure the same thing, as with +. A value with a unit can only be compared with another that has one, so write length > 0 cm, not length > 0.

\begin{matrix} example & = & 50 cm < 1.5 m \\ = & TRUE \end{matrix}

Comparisons can’t be chained: instead of 1 < x < 10, write AND(1 < x, x < 10).

TRUE and FALSE are logical values, written in any case, so neither can be a calculation’s name. They can be compared with = and <>, but not with numbers, or with < or >.

Text, like "Basic", can be compared with = and <> (or == and !=), ignoring case, so "basic" = "Basic" is TRUE, but not with numbers or logical values, or with < or >.