Function Reference#
Grist formulas support most Excel functions, as well as the Python programming language.
The table below lists Grist-specific functions, and the suite of the included Excel-like functions. In addition, the entire Python standard library is available. For more about using formulas in Grist, see Intro to Formulas.
Grist uses Python (version 3.11) for formulas. You can use nearly all features of Python (see Python documentation). Here are some helpful notes:
- Python is case-sensitive, including for Grist table and column names. Excel-like functions are always in uppercase. E.g. if is a Python keyword, while IF is an Excel-like function.
- Compare for equality using
==
, in place of Excel’s single=
(which in Python means assignment). “Not equal” uses!=
in place of Excel’s<>
. - You may write multi-line Python in formulas (use
Shift + Enter
to add lines), including statements, variables, imports, etc. - Grist code runs in a secure sandbox, with no access to anything outside your document.
Grist#
#### Record
class __Record__
#
A Record represents a record of data. It is the primary means of accessing values in formulas. A
Record for a particular table has a property for each data and formula column in the table.
In a formula, `$field` is translated to `rec.field`, where `rec` is the Record for which the
formula is being evaluated.
For example:
def Full_Name(rec, table):
return rec.First_Name + ' ' + rec.LastName
def Name_Length(rec, table):
return len(rec.Full_Name)
#### $Field
__$__*Field* or __rec__*.Field*
#
Access the field named "Field" of the current record. E.g. `$First_Name` or `rec.First_Name`.
#### $group
__$group__
#
In a [summary table](summary-tables.md), `$group` is a special field
containing the list of Records that are summarized by the current summary line. E.g. the
formula `len($group)` counts the number of those records being summarized in each row.
See [RecordSet](#recordset) for useful properties offered by the returned object.
Examples:
sum($group.Amount) # Sum of the Amount field in the matching records
sum(r.Amount for r in $group) # Same as sum($group.Amount)
sum(r.Amount for r in $group if r > 0) # Sum of only the positive amounts
sum(r.Shares * r.Price for r in $group) # Sum of shares * price products
#### RecordSet
class __RecordSet__
#
A RecordSet represents a collection of records, as returned by `Table.lookupRecords()` or
`$group` property in summary views.
A RecordSet allows iterating through the records:
sum(r.Amount for r in Students.lookupRecords(First_Name="John", Last_Name="Doe"))
min(r.DueDate for r in Tasks.lookupRecords(Owner="Bob"))
RecordSets also provide a convenient way to access the list of values for a particular field for
all the records, as `record_set.Field`. For example, the examples above are equivalent to:
sum(Students.lookupRecords(First_Name="John", Last_Name="Doe").Amount)
min(Tasks.lookupRecords(Owner="Bob").DueDate)
You can get the number of records in a RecordSet using `len`, e.g. `len($group)`.
#### find.*
RecordSet.**find.\***(value)
#
A set of methods for finding values in sorted sets of records, as returned by
[`lookupRecords`](#lookuprecords). For example:
Transactions.lookupRecords(..., order_by="Date").find.lt($Date)
Table.lookupRecords(..., order_by=("Foo", "Bar")).find.le(foo, bar)
If the `find` attribute is shadowed by a same-named user column, you may use `_find` instead.
The methods available are:
- __`lt`__: (less than) find nearest record with sort values < the given values
- __`le`__: (less than or equal to) find nearest record with sort values <= the given values
- __`gt`__: (greater than) find nearest record with sort values > the given values
- __`ge`__: (greater than or equal to) find nearest record with sort values >= the given values
- __`eq`__: (equal to) find nearest record with sort values == the given values
Example from [our Payroll template](https://templates.getgrist.com/5pHLanQNThxk/Payroll).
Each person has a history of pay rates, in the Rates table. To find a rate applicable on a
certain date, here is how you can do it old-style:
# Get all the rates for the Person and Role in this row.
rates = Rates.lookupRecords(Person=$Person, Role=$Role)
# Pick out only those rates whose Rate_Start is on or before this row's Date.
past_rates = [r for r in rates if r.Rate_Start <= $Date]
# Select the latest of past_rates, i.e. maximum by Rate_Start.
rate = max(past_rates, key=lambda r: r.Rate_Start)
# Return the Hourly_Rate from the relevant Rates record.
return rate.Hourly_Rate
With the new methods, it is much simpler:
rates = Rates.lookupRecords(Person=$Person, Role=$Role, order_by="Rate_Start")
rate = rates.find.le($Date)
return rate.Hourly_Rate
Note that this is also much faster when there are many rates for the same Person and Role.
#### UserTable
class __UserTable__
#
Each data table in the document is represented in the code by an instance of `UserTable` class.
These names are always capitalized. A UserTable provides access to all the records in the table,
as well as methods to look up particular records.
Every table in the document is available to all formulas.
#### all
UserTable.__all__
#
The list of all the records in this table.
For example, this evaluates to the number of records in the table `Students`.
len(Students.all)
This evaluates to the sum of the `Population` field for every record in the table `Countries`.
sum(r.Population for r in Countries.all)
#### lookupOne
UserTable.__lookupOne__(Field_In_Lookup_Table=value, ...)
#
Returns a [Record](#record) matching the given field=value arguments. The value may be any
expression,
most commonly a field in the current row (e.g. `$SomeField`) or a constant (e.g. a quoted string
like `"Some Value"`).
For example:
People.lookupOne(First_Name="Lewis", Last_Name="Carroll")
People.lookupOne(Email=$Work_Email)
Learn more about [lookupOne](references-lookups.md#lookupone).
If multiple records are found, the first match is returned. You may set the optional `order_by`
parameter to the column ID by which to sort the matches, to determine which of them is
returned as the first one. By default, the record with the lowest row ID is returned.
See [`lookupRecords`](#lookuprecords) for details of all available options and behavior of
`order_by` (and of its legacy alternative, `sort_by`).
For example:
Tasks.lookupOne(Project=$id, order_by="Priority") # Task with the smallest Priority.
Rates.lookupOne(Person=$id, order_by="-Date") # Rate with the latest Date.
#### lookupRecords
UserTable.__lookupRecords__(Field_In_Lookup_Table=value, ...)
#
Returns a [RecordSet](#recordset) matching the given field=value arguments. The value may be
any expression,
most commonly a field in the current row (e.g. `$SomeField`) or a constant (e.g. a quoted string
like `"Some Value"`) (examples below).
For example:
People.lookupRecords(Email=$Work_Email)
People.lookupRecords(First_Name="George", Last_Name="Washington")
You may set the optional `order_by` parameter to the column ID by which to sort the results.
You can prefix the column ID with "-" to reverse the order. You can also specify multiple
column IDs as a tuple (e.g. `order_by=("Account", "-Date")`).
For example:
Transactions.lookupRecords(Account=$Account, order_by="Date")
Transactions.lookupRecords(Account=$Account, order_by="-Date")
Transactions.lookupRecords(Active=True, order_by=("Account", "-Date"))
For records with equal `order_by` fields, the results are sorted according to how they appear
in views (which is determined by the special `manualSort` column). You may set `order_by=None`
to match the order of records in unsorted views.
By default, with no `order_by`, records are sorted by row ID, as if with `order_by="id"`.
For backward compatibility, `sort_by` may be used instead of `order_by`, but only allows a
single field, and falls back to row ID (rather than `manualSort`).
See [RecordSet](#recordset) for useful properties offered by the returned object. In
particular, methods like [`.find.le`](#find_) allow searching for nearest values.
See [CONTAINS](#contains) for an example utilizing `UserTable.lookupRecords` to find records
where a field of a list type (such as `Choice List` or `Reference List`) contains the given
value.
Learn more about [lookupRecords](references-lookups.md#lookuprecords).
Cumulative#
#### NEXT
__NEXT__(rec, *, group_by=(), order_by)
#
Finds the next record in the table according to the order specified by `order_by`, and
grouping specified by `group_by`. See [`PREVIOUS`](#previous) for details.
#### PREVIOUS
__PREVIOUS__(rec, *, group_by=(), order_by)
#
Finds the previous record in the table according to the order specified by `order_by`, and
grouping specified by `group_by`. Each of these arguments may be a column ID or a tuple of
column IDs, and `order_by` allows column IDs to be prefixed with "-" to reverse sort order.
For example,
PREVIOUS(rec, order_by="Date") # The previous record when sorted by increasing Date.
PREVIOUS(rec, order_by="-Date") # The previous record when sorted by decreasing Date.
You may use `group_by` to search for the previous record within a filtered group. For example,
this finds the previous record with the same Account as `rec`, when records are filtered by the
Account of `rec` and sorted by increasing Date:
PREVIOUS(rec, group_by="Account", order_by="Date")
When multiple records have the same `order_by` values (e.g. the same Date in the examples above),
the order is determined by the relative position of rows in views. This is done internally by
falling back to the special column `manualSort` and the row ID column `id`.
Use `order_by=None` to find the previous record in an unsorted table (when rows may be
rearranged by dragging them manually). For example:
PREVIOUS(rec, order_by=None) # The previous record in the unsorted list of records.
You may specify multiple column IDs as a tuple, for both `group_by` and `order_by`. This can be
used to match views sorted by multiple columns. For example:
PREVIOUS(rec, group_by=("Account", "Year"), order_by=("Date", "-Amount"))
#### RANK
__RANK__(rec, *, group_by=(), order_by, order='asc')
#
Returns the rank (or position) of this record in the table according to the order specified by
`order_by`, and grouping specified by `group_by`. See [`PREVIOUS`](#previous) for details of
these parameters.
The `order` parameter may be `"asc"` (which is the default) or `"desc"`.
When `order` is `"asc"` or omitted, the first record in the group in the sorted order would have
the rank of 1. When `order` is `"desc"`, the last record in the sorted order would have the rank
of 1.
If there are multiple groups, there will be multiple records with the same rank. In particular,
each group will have a record with rank 1.
For example, `RANK(rec, group_by="Year", order_by="Score", order="desc")` will return the rank of
the current record (`rec`) among all the records in its table for the same year, ordered by
decreasing score.
Date#
#### DATE
__DATE__(year, month, day)
#
Returns the `datetime.datetime` object that represents a particular date.
The DATE function is most useful in formulas where year, month, and day are formulas, not
constants.
If year is between 0 and 1899 (inclusive), adds 1900 to calculate the year.
>>> DATE(108, 1, 2)
datetime.date(2008, 1, 2)
>>> DATE(2008, 1, 2)
datetime.date(2008, 1, 2)
If month is greater than 12, rolls into the following year.
>>> DATE(2008, 14, 2)
datetime.date(2009, 2, 2)
If month is less than 1, subtracts that many months plus 1, from the first month in the year.
>>> DATE(2008, -3, 2)
datetime.date(2007, 9, 2)
If day is greater than the number of days in the given month, rolls into the following months.
>>> DATE(2008, 1, 35)
datetime.date(2008, 2, 4)
If day is less than 1, subtracts that many days plus 1, from the first day of the given month.
>>> DATE(2008, 1, -15)
datetime.date(2007, 12, 16)
#### DATEADD
__DATEADD__(start_date, days=0, months=0, years=0, weeks=0)
#
Returns the date a given number of days, months, years, or weeks away from `start_date`. You may
specify arguments in any order if you specify argument names. Use negative values to subtract.
For example, `DATEADD(date, 1)` is the same as `DATEADD(date, days=1)`, ands adds one day to
`date`. `DATEADD(date, years=1, days=-1)` adds one year minus one day.
>>> DATEADD(DATE(2011, 1, 15), 1)
datetime.date(2011, 1, 16)
>>> DATEADD(DATE(2011, 1, 15), months=1, days=-1)
datetime.date(2011, 2, 14)
>>> DATEADD(DATE(2011, 1, 15), years=-2, months=1, days=3, weeks=2)
datetime.date(2009, 3, 4)
>>> DATEADD(DATE(1975, 4, 30), years=50, weeks=-5)
datetime.date(2025, 3, 26)
#### DATEDIF
__DATEDIF__(start_date, end_date, unit)
#
Calculates the number of days, months, or years between two dates.
Unit indicates the type of information that you want returned:
- "Y": The number of complete years in the period.
- "M": The number of complete months in the period.
- "D": The number of days in the period.
- "MD": The difference between the days in start_date and end_date. The months and years of the
dates are ignored.
- "YM": The difference between the months in start_date and end_date. The days and years of the
dates are ignored.
- "YD": The difference between the days of start_date and end_date. The years of the dates are
ignored.
Two complete years in the period (2)
>>> DATEDIF(DATE(2001, 1, 1), DATE(2003, 1, 1), "Y")
2
440 days between June 1, 2001, and August 15, 2002 (440)
>>> DATEDIF(DATE(2001, 6, 1), DATE(2002, 8, 15), "D")
440
75 days between June 1 and August 15, ignoring the years of the dates (75)
>>> DATEDIF(DATE(2001, 6, 1), DATE(2012, 8, 15), "YD")
75
The difference between 1 and 15, ignoring the months and the years of the dates (14)
>>> DATEDIF(DATE(2001, 6, 1), DATE(2002, 8, 15), "MD")
14
#### DATEVALUE
__DATEVALUE__(date_string, tz=None)
#
Converts a date that is stored as text to a `datetime` object.
>>> DATEVALUE("1/1/2008")
datetime.datetime(2008, 1, 1, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> DATEVALUE("30-Jan-2008")
datetime.datetime(2008, 1, 30, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> DATEVALUE("2008-12-11")
datetime.datetime(2008, 12, 11, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> DATEVALUE("5-JUL").replace(year=2000)
datetime.datetime(2000, 7, 5, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
In case of ambiguity, prefer M/D/Y format.
>>> DATEVALUE("1/2/3")
datetime.datetime(2003, 1, 2, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
#### DATE_TO_XL
__DATE_TO_XL__(date_value)
#
Converts a Python `date` or `datetime` object to the serial number as used by
Excel, with December 30, 1899 as serial number 1.
See XL_TO_DATE for more explanation.
>>> DATE_TO_XL(datetime.date(2008, 1, 1))
39448.0
>>> DATE_TO_XL(datetime.date(2012, 3, 14))
40982.0
>>> DATE_TO_XL(datetime.datetime(2012, 3, 14, 1, 30))
40982.0625
#### DAY
__DAY__(date)
#
Returns the day of a date, as an integer ranging from 1 to 31. Same as `date.day`.
>>> DAY(DATE(2011, 4, 15))
15
>>> DAY("5/31/2012")
31
>>> DAY(datetime.datetime(1900, 1, 1))
1
#### DAYS
__DAYS__(end_date, start_date)
#
Returns the number of days between two dates. Same as `(end_date - start_date).days`.
>>> DAYS("3/15/11","2/1/11")
42
>>> DAYS(DATE(2011, 12, 31), DATE(2011, 1, 1))
364
>>> DAYS("2/1/11", "3/15/11")
-42
#### DTIME
__DTIME__(value, tz=None)
#
Returns the value converted to a python `datetime` object. The value may be a
`string`, `date` (interpreted as midnight on that day), `time` (interpreted as a
time-of-day today), or an existing `datetime`.
The returned `datetime` will have its timezone set to the `tz` argument, or the
document's default timezone when `tz` is omitted or None. If the input is itself a
`datetime` with the timezone set, it is returned unchanged (no changes to its timezone).
>>> DTIME(datetime.date(2017, 1, 1))
datetime.datetime(2017, 1, 1, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> DTIME(datetime.date(2017, 1, 1), 'Europe/Paris')
datetime.datetime(2017, 1, 1, 0, 0, tzinfo=moment.tzinfo('Europe/Paris'))
>>> DTIME(datetime.datetime(2017, 1, 1))
datetime.datetime(2017, 1, 1, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> DTIME(datetime.datetime(2017, 1, 1, tzinfo=moment.tzinfo('UTC')))
datetime.datetime(2017, 1, 1, 0, 0, tzinfo=moment.tzinfo('UTC'))
>>> DTIME(datetime.datetime(2017, 1, 1, tzinfo=moment.tzinfo('UTC')), 'Europe/Paris')
datetime.datetime(2017, 1, 1, 0, 0, tzinfo=moment.tzinfo('UTC'))
>>> DTIME("1/1/2008")
datetime.datetime(2008, 1, 1, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
#### EDATE
__EDATE__(start_date, months)
#
Returns the date that is the given number of months before or after `start_date`. Use
EDATE to calculate maturity dates or due dates that fall on the same day of the month as the
date of issue.
>>> EDATE(DATE(2011, 1, 15), 1)
datetime.date(2011, 2, 15)
>>> EDATE(DATE(2011, 1, 15), -1)
datetime.date(2010, 12, 15)
>>> EDATE(DATE(2011, 1, 15), 2)
datetime.date(2011, 3, 15)
>>> EDATE(DATE(2012, 3, 1), 10)
datetime.date(2013, 1, 1)
>>> EDATE(DATE(2012, 5, 1), -2)
datetime.date(2012, 3, 1)
#### EOMONTH
__EOMONTH__(start_date, months)
#
Returns the date for the last day of the month that is the indicated number of months before or
after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day
of the month.
>>> EOMONTH(DATE(2011, 1, 1), 1)
datetime.date(2011, 2, 28)
>>> EOMONTH(DATE(2011, 1, 15), -3)
datetime.date(2010, 10, 31)
>>> EOMONTH(DATE(2012, 3, 1), 10)
datetime.date(2013, 1, 31)
>>> EOMONTH(DATE(2012, 5, 1), -2)
datetime.date(2012, 3, 31)
#### HOUR
__HOUR__(time)
#
Same as `time.hour`.
>>> HOUR(XL_TO_DATE(0.75))
18
>>> HOUR("7/18/2011 7:45")
7
>>> HOUR("4/21/2012")
0
#### ISOWEEKNUM
__ISOWEEKNUM__(date)
#
Returns the ISO week number of the year for a given date.
>>> ISOWEEKNUM("3/9/2012")
10
>>> [ISOWEEKNUM(DATE(2000 + y, 1, 1)) for y in [0,1,2,3,4,5,6,7,8]]
[52, 1, 1, 1, 1, 53, 52, 1, 1]
#### MINUTE
__MINUTE__(time)
#
Returns the minutes of `datetime`, as an integer from 0 to 59.
Same as `time.minute`.
>>> MINUTE(XL_TO_DATE(0.75))
0
>>> MINUTE("7/18/2011 7:45")
45
>>> MINUTE("12:59:00 PM")
59
>>> MINUTE(datetime.time(12, 58, 59))
58
#### MONTH
__MONTH__(date)
#
Returns the month of a date represented, as an integer from from 1 (January) to 12 (December).
Same as `date.month`.
>>> MONTH(DATE(2011, 4, 15))
4
>>> MONTH("5/31/2012")
5
>>> MONTH(datetime.datetime(1900, 1, 1))
1
#### MOONPHASE
__MOONPHASE__(date, output='emoji')
#
Returns the phase of the moon on the given date. The output defaults to a moon-phase emoji.
- With `output="days"`, the output is the age of the moon in days (new moon being 0).
- With `output="fraction"`, the output is the fraction of the lunar month since new moon.
The calculation isn't astronomically precise, but good enough for wolves and sailors.
Do NOT! use `output="lunacy"`.
>>> MOONPHASE(datetime.date(1900, 1, 1), "days")
0.0
>>> MOONPHASE(datetime.date(1900, 1, 1), "fraction")
0.0
>>> MOONPHASE(datetime.datetime(1900, 1, 1)) == '🌑'
True
>>> MOONPHASE(datetime.date(1900, 1, 15)) == '🌕'
True
>>> MOONPHASE(datetime.date(1900, 1, 30)) == '🌑'
True
>>> [MOONPHASE(DATEADD(datetime.date(2023, 4, 1), days=4*n)) for n in range(8)] == ['🌔', '🌕', '🌖', '🌗', '🌘', '🌑', '🌒', '🌓']
True
>>> [round(MOONPHASE(DATEADD(datetime.date(2023, 4, 1), days=4*n), "days"), 1) for n in range(8)]
[10.4, 14.4, 18.4, 22.4, 26.4, 0.9, 4.9, 8.9]
#### NOW
__NOW__(tz=None)
#
Returns the `datetime` object for the current time.
#### SECOND
__SECOND__(time)
#
Returns the seconds of `datetime`, as an integer from 0 to 59.
Same as `time.second`.
>>> SECOND(XL_TO_DATE(0.75))
0
>>> SECOND("7/18/2011 7:45:13")
13
>>> SECOND(datetime.time(12, 58, 59))
59
#### TODAY
__TODAY__(tz=None)
#
Returns the `date` object for the current date.
#### WEEKDAY
__WEEKDAY__(date, return_type=1)
#
Returns the day of the week corresponding to a date. The day is given as an integer, ranging
from 1 (Sunday) to 7 (Saturday), by default.
Return_type determines the type of the returned value.
- 1 (default) - Returns 1 (Sunday) through 7 (Saturday).
- 2 - Returns 1 (Monday) through 7 (Sunday).
- 3 - Returns 0 (Monday) through 6 (Sunday).
- 11 - Returns 1 (Monday) through 7 (Sunday).
- 12 - Returns 1 (Tuesday) through 7 (Monday).
- 13 - Returns 1 (Wednesday) through 7 (Tuesday).
- 14 - Returns 1 (Thursday) through 7 (Wednesday).
- 15 - Returns 1 (Friday) through 7 (Thursday).
- 16 - Returns 1 (Saturday) through 7 (Friday).
- 17 - Returns 1 (Sunday) through 7 (Saturday).
>>> WEEKDAY(DATE(2008, 2, 14))
5
>>> WEEKDAY(DATE(2012, 3, 1))
5
>>> WEEKDAY(DATE(2012, 3, 1), 1)
5
>>> WEEKDAY(DATE(2012, 3, 1), 2)
4
>>> WEEKDAY("3/1/2012", 3)
3
#### WEEKNUM
__WEEKNUM__(date, return_type=1)
#
Returns the week number of a specific date. For example, the week containing January 1 is the
first week of the year, and is numbered week 1.
Return_type determines which week is considered the first week of the year.
- 1 (default) - Week 1 is the first week starting Sunday that contains January 1.
- 2 - Week 1 is the first week starting Monday that contains January 1.
- 11 - Week 1 is the first week starting Monday that contains January 1.
- 12 - Week 1 is the first week starting Tuesday that contains January 1.
- 13 - Week 1 is the first week starting Wednesday that contains January 1.
- 14 - Week 1 is the first week starting Thursday that contains January 1.
- 15 - Week 1 is the first week starting Friday that contains January 1.
- 16 - Week 1 is the first week starting Saturday that contains January 1.
- 17 - Week 1 is the first week starting Sunday that contains January 1.
- 21 - ISO 8601 Approach: Week 1 is the first week starting Monday that contains January 4.
Equivalently, it is the week that contains the first Thursday of the year.
>>> WEEKNUM(DATE(2012, 3, 9))
10
>>> WEEKNUM(DATE(2012, 3, 9), 2)
11
>>> WEEKNUM('1/1/1900')
1
>>> WEEKNUM('2/1/1900')
5
#### XL_TO_DATE
__XL_TO_DATE__(value, tz=None)
#
Converts a provided Excel serial number representing a date into a `datetime` object.
Value is interpreted as the number of days since December 30, 1899.
(This corresponds to Google Sheets interpretation. Excel starts with Dec. 31, 1899 but wrongly
considers 1900 to be a leap year. Excel for Mac should be configured to use 1900 date system,
i.e. uncheck "Use the 1904 date system" option.)
The returned `datetime` will have its timezone set to the `tz` argument, or the
document's default timezone when `tz` is omitted or None.
>>> XL_TO_DATE(41100.1875)
datetime.datetime(2012, 7, 10, 4, 30, tzinfo=moment.tzinfo('America/New_York'))
>>> XL_TO_DATE(39448)
datetime.datetime(2008, 1, 1, 0, 0, tzinfo=moment.tzinfo('America/New_York'))
>>> XL_TO_DATE(40982.0625)
datetime.datetime(2012, 3, 14, 1, 30, tzinfo=moment.tzinfo('America/New_York'))
#### YEAR
__YEAR__(date)
#
Returns the year corresponding to a date as an integer.
Same as `date.year`.
>>> YEAR(DATE(2011, 4, 15))
2011
>>> YEAR("5/31/2030")
2030
>>> YEAR(datetime.datetime(1900, 1, 1))
1900
#### YEARFRAC
__YEARFRAC__(start_date, end_date, basis=0)
#
Calculates the fraction of the year represented by the number of whole days between two dates.
Basis is the type of day count basis to use.
* `0` (default) - US (NASD) 30/360
* `1` - Actual/actual
* `2` - Actual/360
* `3` - Actual/365
* `4` - European 30/360
* `-1` - Actual/actual (Google Sheets variation)
This function is useful for financial calculations. For compatibility with Excel, it defaults to
using the NASD standard calendar. For use in non-financial settings, option `-1` is
likely the best choice.
See >>> "%.8f" % YEARFRAC(DATE(2012, 1, 1), DATE(2012, 7, 30))
'0.58055556'
Fraction between same dates, using the Actual/Actual basis argument. Because 2012 is a Leap
year, it has a 366 day basis.
>>> "%.8f" % YEARFRAC(DATE(2012, 1, 1), DATE(2012, 7, 30), 1)
'0.57650273'
Fraction between same dates, using the Actual/365 basis argument. Uses a 365 day basis.
>>> "%.8f" % YEARFRAC(DATE(2012, 1, 1), DATE(2012, 7, 30), 3)
'0.57808219'
Info#
#### CELL
__CELL__(info_type, reference)
#
Returns the requested information about the specified cell. This is not implemented in Grist
NoteThis function is not currently implemented in Grist.
#### ISBLANK
__ISBLANK__(value)
#
Returns whether a value refers to an empty cell. It isn't implemented in Grist. To check for an
empty string, use `value == ""`.
NoteThis function is not currently implemented in Grist.
#### ISEMAIL
__ISEMAIL__(value)
#
Returns whether a value is a valid email address.
Note that checking email validity is not an exact science. The technical standard considers many
email addresses valid that are not used in practice, and would not be considered valid by most
users. Instead, we follow Google Sheets implementation, with some differences, noted below.
>>> ISEMAIL("Abc.123@example.com")
True
>>> ISEMAIL("Bob_O-Reilly+tag@example.com")
True
>>> ISEMAIL("John Doe")
False
>>> ISEMAIL("john@aol...com")
False
#### ISERR
__ISERR__(value)
#
Checks whether a value is an error. In other words, it returns true
if using `value` directly would raise an exception.
NOTE: Grist implements this by automatically wrapping the argument to use lazy evaluation.
A more Pythonic approach to checking for errors is:
try:
... value ...
except Exception, err:
... do something about the error ...
For example:
>>> ISERR("Hello")
False
#### ISERROR
__ISERROR__(value)
#
Checks whether a value is an error or an invalid value. It is similar to `ISERR`, but also
returns true for an invalid value such as NaN or a text value in a Numeric column.
NOTE: Grist implements this by automatically wrapping the argument to use lazy evaluation.
>>> ISERROR("Hello")
False
>>> ISERROR(AltText("fail"))
True
>>> ISERROR(float('nan'))
True
#### ISLOGICAL
__ISLOGICAL__(value)
#
Checks whether a value is `True` or `False`.
>>> ISLOGICAL(True)
True
>>> ISLOGICAL(False)
True
>>> ISLOGICAL(0)
False
>>> ISLOGICAL(None)
False
>>> ISLOGICAL("Test")
False
#### ISNA
__ISNA__(value)
#
Checks whether a value is the error `#N/A`.
>>> ISNA(float('nan'))
True
>>> ISNA(0.0)
False
>>> ISNA('text')
False
>>> ISNA(float('-inf'))
False
#### ISNONTEXT
__ISNONTEXT__(value)
#
Checks whether a value is non-textual.
>>> ISNONTEXT("asdf")
False
>>> ISNONTEXT("")
False
>>> ISNONTEXT(AltText("text"))
False
>>> ISNONTEXT(17.0)
True
>>> ISNONTEXT(None)
True
>>> ISNONTEXT(datetime.date(2011, 1, 1))
True
#### ISNUMBER
__ISNUMBER__(value)
#
Checks whether a value is a number.
>>> ISNUMBER(17)
True
>>> ISNUMBER(-123.123423)
True
>>> ISNUMBER(False)
True
>>> ISNUMBER(float('nan'))
True
>>> ISNUMBER(float('inf'))
True
>>> ISNUMBER('17')
False
>>> ISNUMBER(None)
False
>>> ISNUMBER(datetime.date(2011, 1, 1))
False
#### ISREF
__ISREF__(value)
#
Checks whether a value is a table record.
For example, if a column `person` is of type Reference to the `People` table,
then `ISREF($person)` is `True`.
Similarly, `ISREF(People.lookupOne(name=$name))` is `True`. For any other type of value,
`ISREF()` would evaluate to `False`.
>>> ISREF(17)
False
>>> ISREF("Roger")
False
#### ISREFLIST
__ISREFLIST__(value)
#
Checks whether a value is a [`RecordSet`](#recordset),
the type of values in Reference List columns.
For example, if a column `people` is of type Reference List to the `People` table,
then `ISREFLIST($people)` is `True`.
Similarly, `ISREFLIST(People.lookupRecords(name=$name))` is `True`. For any other type of value,
`ISREFLIST()` would evaluate to `False`.
>>> ISREFLIST(17)
False
>>> ISREFLIST("Roger")
False
#### ISTEXT
__ISTEXT__(value)
#
Checks whether a value is text.
>>> ISTEXT("asdf")
True
>>> ISTEXT("")
True
>>> ISTEXT(AltText("text"))
True
>>> ISTEXT(17.0)
False
>>> ISTEXT(None)
False
>>> ISTEXT(datetime.date(2011, 1, 1))
False
#### ISURL
__ISURL__(value)
#
Checks whether a value is a valid URL. It does not need to be fully qualified, or to include
"http://" and "www". It does not follow a standard, but attempts to work similarly to ISURL in
Google Sheets, and to return True for text that is likely a URL.
Valid protocols include ftp, http, https, gopher, mailto, news, telnet, and aim.
>>> ISURL("http://www.getgrist.com")
True
>>> ISURL("https://foo.com/test_(wikipedia)#cite-1")
True
>>> ISURL("mailto://user@example.com")
True
>>> ISURL("http:///a")
False
#### N
__N__(value)
#
Returns the value converted to a number. True/False are converted to 1/0. A date is converted to
Excel-style serial number of the date. Anything else is converted to 0.
>>> N(7)
7
>>> N(7.1)
7.1
>>> N("Even")
0
>>> N("7")
0
>>> N(True)
1
>>> N(datetime.datetime(2011, 4, 17))
40650.0
#### NA
__NA__()
#
Returns the "value not available" error, `#N/A`.
>>> math.isnan(NA())
True
#### PEEK
__PEEK__(func)
#
Evaluates the given expression without creating dependencies
or requiring that referenced values are up to date, using whatever value it finds in a cell.
This is useful for preventing circular reference errors, particularly in trigger formulas.
For example, if the formula for `A` depends on `$B` and the formula for `B` depends on `$A`,
then normally this would raise a circular reference error because each value needs to be
calculated before the other. But if `A` uses `PEEK($B)` then it will simply get the value
already stored in `$B` without requiring that `$B` is first calculated to the latest value.
Therefore `A` will be calculated first, and `B` can use `$A` without problems.
#### RECORD
__RECORD__(record_or_list, dates_as_iso=False, expand_refs=0)
#
Returns a Python dictionary with all fields in the given record. If a list of records is given,
returns a list of corresponding Python dictionaries.
If dates_as_iso is set, Date and DateTime values are converted to string using ISO 8601 format.
If expand_refs is set to 1 or higher, Reference values are replaced with a RECORD representation
of the referenced record, expanding the given number of levels.
Error values present in cells of the record are replaced with None value, and a special key of
"_error_" gets added containing the error messages for those cells. For example:
`{"Ratio": None, "_error_": {"Ratio": "ZeroDivisionError: integer division or modulo by zero"}}`
Note that care is needed to avoid circular references when using RECORD(), since it creates a
dependency on every cell in the record. In case of RECORD(rec), the cell containing this call
will be omitted from the resulting dictionary.
For example:
RECORD($Person)
RECORD(rec)
RECORD(People.lookupOne(First_Name="Alice"))
RECORD(People.lookupRecords(Department="HR"))
#### REQUEST
__REQUEST__(url, params=None, headers=None, method='GET', data=None, json=None)
#
NoteThis function is not currently implemented in Grist.
#### TYPE
__TYPE__(value)
#
Returns a number associated with the type of data passed into the function. This is not
implemented in Grist. Use `isinstance(value, type)` or `type(value)`.
NoteThis function is not currently implemented in Grist.
Logical#
#### AND
__AND__(logical_expression, *logical_expressions)
#
Returns True if all of the arguments are logically true, and False if any are false.
Same as `all([value1, value2, ...])`.
>>> AND(1)
True
>>> AND(0)
False
>>> AND(1, 1)
True
>>> AND(1,2,3,4)
True
>>> AND(1,2,3,4,0)
False
#### FALSE
__FALSE__()
#
Returns the logical value `False`. You may also use the value `False` directly. This
function is provided primarily for compatibility with other spreadsheet programs.
>>> FALSE()
False
#### IF
__IF__(logical_expression, value_if_true, value_if_false)
#
Returns one value if a logical expression is `True` and another if it is `False`.
The equivalent Python expression is:
value_if_true if logical_expression else value_if_false
Since Grist supports multi-line formulas, you may also use Python blocks such as:
if logical_expression:
return value_if_true
else:
return value_if_false
NOTE: Grist follows Excel model by only evaluating one of the value expressions, by
automatically wrapping the expressions to use lazy evaluation. This allows `IF(False, 1/0, 1)`
to evaluate to `1` rather than raise an exception.
>>> IF(12, "Yes", "No")
'Yes'
>>> IF(None, "Yes", "No")
'No'
>>> IF(True, 0.85, 0.0)
0.85
>>> IF(False, 0.85, 0.0)
0.0
#### IFERROR
__IFERROR__(value, value_if_error='')
#
Returns the first argument if it is not an error value, otherwise returns the second argument if
present, or a blank if the second argument is absent.
NOTE: Grist handles values that raise an exception by wrapping them to use lazy evaluation.
>>> IFERROR(float('nan'), "**NAN**")
'**NAN**'
>>> IFERROR(17.17, "**NAN**")
17.17
>>> IFERROR("Text")
'Text'
>>> IFERROR(AltText("hello"))
''
#### NOT
__NOT__(logical_expression)
#
`True`. Same as `not logical_expression`.
>>> NOT(123)
False
>>> NOT(0)
True
#### OR
__OR__(logical_expression, *logical_expressions)
#
Returns True if any of the arguments is logically true, and false if all of the
arguments are false.
Same as `any([value1, value2, ...])`.
>>> OR(1)
True
>>> OR(0)
False
>>> OR(1, 1)
True
>>> OR(0, 1)
True
>>> OR(0, 0)
False
>>> OR(0,False,0.0,"",None)
False
>>> OR(0,None,3,0)
True
#### TRUE
__TRUE__()
#
Returns the logical value `True`. You may also use the value `True` directly. This
function is provided primarily for compatibility with other spreadsheet programs.
>>> TRUE()
True
Lookup#
#### lookupOne
UserTable.__lookupOne__(Field_In_Lookup_Table=value, ...)
#
Returns a [Record](#record) matching the given field=value arguments. The value may be any
expression,
most commonly a field in the current row (e.g. `$SomeField`) or a constant (e.g. a quoted string
like `"Some Value"`).
For example:
People.lookupOne(First_Name="Lewis", Last_Name="Carroll")
People.lookupOne(Email=$Work_Email)
Learn more about [lookupOne](references-lookups.md#lookupone).
If multiple records are found, the first match is returned. You may set the optional `order_by`
parameter to the column ID by which to sort the matches, to determine which of them is
returned as the first one. By default, the record with the lowest row ID is returned.
See [`lookupRecords`](#lookuprecords) for details of all available options and behavior of
`order_by` (and of its legacy alternative, `sort_by`).
For example:
Tasks.lookupOne(Project=$id, order_by="Priority") # Task with the smallest Priority.
Rates.lookupOne(Person=$id, order_by="-Date") # Rate with the latest Date.
#### lookupRecords
UserTable.__lookupRecords__(Field_In_Lookup_Table=value, ...)
#
Returns a [RecordSet](#recordset) matching the given field=value arguments. The value may be
any expression,
most commonly a field in the current row (e.g. `$SomeField`) or a constant (e.g. a quoted string
like `"Some Value"`) (examples below).
For example:
People.lookupRecords(Email=$Work_Email)
People.lookupRecords(First_Name="George", Last_Name="Washington")
You may set the optional `order_by` parameter to the column ID by which to sort the results.
You can prefix the column ID with "-" to reverse the order. You can also specify multiple
column IDs as a tuple (e.g. `order_by=("Account", "-Date")`).
For example:
Transactions.lookupRecords(Account=$Account, order_by="Date")
Transactions.lookupRecords(Account=$Account, order_by="-Date")
Transactions.lookupRecords(Active=True, order_by=("Account", "-Date"))
For records with equal `order_by` fields, the results are sorted according to how they appear
in views (which is determined by the special `manualSort` column). You may set `order_by=None`
to match the order of records in unsorted views.
By default, with no `order_by`, records are sorted by row ID, as if with `order_by="id"`.
For backward compatibility, `sort_by` may be used instead of `order_by`, but only allows a
single field, and falls back to row ID (rather than `manualSort`).
See [RecordSet](#recordset) for useful properties offered by the returned object. In
particular, methods like [`.find.le`](#find_) allow searching for nearest values.
See [CONTAINS](#contains) for an example utilizing `UserTable.lookupRecords` to find records
where a field of a list type (such as `Choice List` or `Reference List`) contains the given
value.
Learn more about [lookupRecords](references-lookups.md#lookuprecords).
#### ADDRESS
__ADDRESS__(row, column, absolute_relative_mode, use_a1_notation, sheet)
#
Returns a cell reference as a string.
NoteThis function is not currently implemented in Grist.
#### CHOOSE
__CHOOSE__(index, choice1, choice2)
#
Returns an element from a list of choices based on index.
NoteThis function is not currently implemented in Grist.
#### COLUMN
__COLUMN__(cell_reference=None)
#
Returns the column number of a specified cell, with `A=1`.
NoteThis function is not currently implemented in Grist.
#### COLUMNS
__COLUMNS__(range)
#
Returns the number of columns in a specified array or range.
NoteThis function is not currently implemented in Grist.
#### CONTAINS
__CONTAINS__(value, match_empty=no_match_empty)
#
Use this marker with [UserTable.lookupRecords](#lookuprecords) to find records
where a field of a list type (such as `Choice List` or `Reference List`) contains the given value.
For example:
MoviesTable.lookupRecords(genre=CONTAINS("Drama"))
will return records in `MoviesTable` where the column `genre`
is a list or other container such as `["Comedy", "Drama"]`,
i.e. `"Drama" in $genre`.
Note that the column being looked up (e.g. `genre`)
must have values of a container type such as list, tuple, or set.
In particular the values mustn't be strings, e.g. `"Comedy-Drama"` won't match
even though `"Drama" in "Comedy-Drama"` is `True` in Python.
It also won't match substrings within container elements, e.g. `["Comedy-Drama"]`.
You can optionally pass a second argument `match_empty` to indicate a value that
should be matched against empty lists in the looked up column.
For example, given this formula:
MoviesTable.lookupRecords(genre=CONTAINS(g, match_empty=''))
If `g` is `''` (i.e. equal to `match_empty`) then the column `genre` in the returned records
will either be an empty list (or other container) or a list containing `g` as usual.
#### GETPIVOTDATA
__GETPIVOTDATA__(value_name, any_pivot_table_cell, original_column_1, pivot_item_1=None, *args)
#
Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings.
NoteThis function is not currently implemented in Grist.
#### HLOOKUP
__HLOOKUP__(search_key, range, index, is_sorted)
#
Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
NoteThis function is not currently implemented in Grist.
#### HYPERLINK
__HYPERLINK__(url, link_label)
#
Creates a hyperlink inside a cell.
NoteThis function is not currently implemented in Grist.
#### INDEX
__INDEX__(reference, row, column)
#
Returns the content of a cell, specified by row and column offset.
NoteThis function is not currently implemented in Grist.
#### INDIRECT
__INDIRECT__(cell_reference_as_string)
#
Returns a cell reference specified by a string.
NoteThis function is not currently implemented in Grist.
#### LOOKUP
__LOOKUP__(search_key, search_range_or_search_result_array, result_range=None)
#
Looks through a row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column.
NoteThis function is not currently implemented in Grist.
#### MATCH
__MATCH__(search_key, range, search_type)
#
Returns the relative position of an item in a range that matches a specified value.
NoteThis function is not currently implemented in Grist.
#### OFFSET
__OFFSET__(cell_reference, offset_rows, offset_columns, height, width)
#
Returns a range reference shifted a specified number of rows and columns from a starting cell reference.
NoteThis function is not currently implemented in Grist.
#### ROW
__ROW__(cell_reference)
#
Returns the row number of a specified cell.
NoteThis function is not currently implemented in Grist.
#### ROWS
__ROWS__(range)
#
Returns the number of rows in a specified array or range.
NoteThis function is not currently implemented in Grist.
#### SELF_HYPERLINK
__SELF_HYPERLINK__(label=None, page=None, **kwargs)
#
Creates a link to the current document. All parameters are optional.
The returned string is in URL format, optionally preceded by a label and a space
(the format expected for Grist Text columns with the HyperLink option enabled).
A numeric page number can be supplied, which will create a link to the
specified page. To find the numeric page number you need, visit a page
and examine its URL for a `/p/NN` part.
Any number of arguments of the form `LinkKey_NAME` may be provided, to set
`user.LinkKey.NAME` values that will be available in access rules. For example,
if a rule allows users to view rows when `user.LinkKey.Code == rec.Code`,
we might want to create links with `SELF_HYPERLINK(LinkKey_Code=$Code)`.
>>> SELF_HYPERLINK()
u'https://docs.getgrist.com/sbaltsirg/Example'
>>> SELF_HYPERLINK(label='doc')
u'doc https://docs.getgrist.com/sbaltsirg/Example'
>>> SELF_HYPERLINK(page=2)
u'https://docs.getgrist.com/sbaltsirg/Example/p/2'
>>> SELF_HYPERLINK(LinkKey_Code='X1234')
u'https://docs.getgrist.com/sbaltsirg/Example?Code_=X1234'
>>> SELF_HYPERLINK(label='order', page=3, LinkKey_Code='X1234', LinkKey_Name='Bi Ngo')
u'order https://docs.getgrist.com/sbaltsirg/Example/p/3?Code_=X1234&Name_=Bi+Ngo'
>>> SELF_HYPERLINK(Linky_Link='Link')
Traceback (most recent call last):
...
TypeError: unexpected keyword argument 'Linky_Link' (not of form LinkKey_NAME)
#### VLOOKUP
__VLOOKUP__(table, **field_value_pairs)
#
Vertical lookup. Searches the given table for a record matching the given `field=value`
arguments. If multiple records match, returns one of them. If none match, returns the special
empty record.
The returned object is a record whose fields are available using `.field` syntax. For example,
`VLOOKUP(Employees, EmployeeID=$EmpID).Salary`.
Note that `VLOOKUP` isn't commonly needed in Grist, since [Reference columns](col-refs.md) are the
best way to link data between tables, and allow simple efficient usage such as `$Person.Age`.
`VLOOKUP` is exactly quivalent to `table.lookupOne(**field_value_pairs)`. See
[lookupOne](#lookupone).
For example:
VLOOKUP(People, First_Name="Lewis", Last_Name="Carroll")
VLOOKUP(People, First_Name="Lewis", Last_Name="Carroll").Age
Math#
#### ABS
__ABS__(value)
#
Returns the absolute value of a number.
>>> ABS(2)
2
>>> ABS(-2)
2
>>> ABS(-4)
4
#### ACOS
__ACOS__(value)
#
Returns the inverse cosine of a value, in radians.
>>> round(ACOS(-0.5), 9)
2.094395102
>>> round(ACOS(-0.5)*180/PI(), 10)
120.0
#### ACOSH
__ACOSH__(value)
#
Returns the inverse hyperbolic cosine of a number.
>>> ACOSH(1)
0.0
>>> round(ACOSH(10), 7)
2.9932228
#### ARABIC
__ARABIC__(roman_numeral)
#
Computes the value of a Roman numeral.
>>> ARABIC("LVII")
57
>>> ARABIC('mcmxii')
1912
#### ASIN
__ASIN__(value)
#
Returns the inverse sine of a value, in radians.
>>> round(ASIN(-0.5), 9)
-0.523598776
>>> round(ASIN(-0.5)*180/PI(), 10)
-30.0
>>> round(DEGREES(ASIN(-0.5)), 10)
-30.0
#### ASINH
__ASINH__(value)
#
Returns the inverse hyperbolic sine of a number.
>>> round(ASINH(-2.5), 9)
-1.647231146
>>> round(ASINH(10), 9)
2.99822295
#### ATAN
__ATAN__(value)
#
Returns the inverse tangent of a value, in radians.
>>> round(ATAN(1), 9)
0.785398163
>>> ATAN(1)*180/PI()
45.0
>>> DEGREES(ATAN(1))
45.0
#### ATAN2
__ATAN2__(x, y)
#
Returns the angle between the x-axis and a line segment from the origin (0,0) to specified
coordinate pair (`x`,`y`), in radians.
>>> round(ATAN2(1, 1), 9)
0.785398163
>>> round(ATAN2(-1, -1), 9)
-2.35619449
>>> ATAN2(-1, -1)*180/PI()
-135.0
>>> DEGREES(ATAN2(-1, -1))
-135.0
>>> round(ATAN2(1,2), 9)
1.107148718
#### ATANH
__ATANH__(value)
#
Returns the inverse hyperbolic tangent of a number.
>>> round(ATANH(0.76159416), 9)
1.00000001
>>> round(ATANH(-0.1), 9)
-0.100335348
#### CEILING
__CEILING__(value, factor=1)
#
Rounds a number up to the nearest multiple of factor, or the nearest integer if the factor is
omitted or 1.
>>> CEILING(2.5, 1)
3
>>> CEILING(-2.5, -2)
-4
>>> CEILING(-2.5, 2)
-2
>>> CEILING(1.5, 0.1)
1.5
>>> CEILING(0.234, 0.01)
0.24
#### COMBIN
__COMBIN__(n, k)
#
Returns the number of ways to choose some number of objects from a pool of a given size of
objects.
>>> COMBIN(8,2)
28
>>> COMBIN(4,2)
6
>>> COMBIN(10,7)
120
#### COS
__COS__(angle)
#
Returns the cosine of an angle provided in radians.
>>> round(COS(1.047), 7)
0.5001711
>>> round(COS(60*PI()/180), 10)
0.5
>>> round(COS(RADIANS(60)), 10)
0.5
#### COSH
__COSH__(value)
#
Returns the hyperbolic cosine of any real number.
>>> round(COSH(4), 6)
27.308233
>>> round(COSH(EXP(1)), 7)
7.6101251
#### DEGREES
__DEGREES__(angle)
#
Converts an angle value in radians to degrees.
>>> round(DEGREES(ACOS(-0.5)), 10)
120.0
>>> DEGREES(PI())
180.0
#### EVEN
__EVEN__(value)
#
Rounds a number up to the nearest even integer, rounding away from zero.
>>> EVEN(1.5)
2
>>> EVEN(3)
4
>>> EVEN(2)
2
>>> EVEN(-1)
-2
#### EXP
__EXP__(exponent)
#
Returns Euler's number, e (~2.718) raised to a power.
>>> round(EXP(1), 8)
2.71828183
>>> round(EXP(2), 7)
7.3890561
#### FACT
__FACT__(value)
#
Returns the factorial of a number.
>>> FACT(5)
120
>>> FACT(1.9)
1
>>> FACT(0)
1
>>> FACT(1)
1
>>> FACT(-1)
Traceback (most recent call last):
...
ValueError: factorial() not defined for negative values
#### FACTDOUBLE
__FACTDOUBLE__(value)
#
Returns the "double factorial" of a number.
>>> FACTDOUBLE(6)
48
>>> FACTDOUBLE(7)
105
>>> FACTDOUBLE(3)
3
>>> FACTDOUBLE(4)
8
#### FLOOR
__FLOOR__(value, factor=1)
#
Rounds a number down to the nearest integer multiple of specified significance.
>>> FLOOR(3.7,2)
2
>>> FLOOR(-2.5,-2)
-2
>>> FLOOR(2.5,-2)
Traceback (most recent call last):
...
ValueError: factor argument invalid
>>> FLOOR(1.58,0.1)
1.5
>>> FLOOR(0.234,0.01)
0.23
#### GCD
__GCD__(value1, *more_values)
#
Returns the greatest common divisor of one or more integers.
>>> GCD(5, 2)
1
>>> GCD(24, 36)
12
>>> GCD(7, 1)
1
>>> GCD(5, 0)
5
>>> GCD(0, 5)
5
>>> GCD(5)
5
>>> GCD(14, 42, 21)
7
#### INT
__INT__(value)
#
Rounds a number down to the nearest integer that is less than or equal to it.
>>> INT(8.9)
8
>>> INT(-8.9)
-9
>>> 19.5-INT(19.5)
0.5
#### LCM
__LCM__(value1, *more_values)
#
Returns the least common multiple of one or more integers.
>>> LCM(5, 2)
10
>>> LCM(24, 36)
72
>>> LCM(0, 5)
0
>>> LCM(5)
5
>>> LCM(10, 100)
100
>>> LCM(12, 18)
36
>>> LCM(12, 18, 24)
72
#### LN
__LN__(value)
#
Returns the the logarithm of a number, base e (Euler's number).
>>> round(LN(86), 7)
4.4543473
>>> round(LN(2.7182818), 7)
1.0
>>> round(LN(EXP(3)), 10)
3.0
#### LOG
__LOG__(value, base=10)
#
Returns the the logarithm of a number given a base.
>>> LOG(10)
1.0
>>> LOG(8, 2)
3.0
>>> round(LOG(86, 2.7182818), 7)
4.4543473
#### LOG10
__LOG10__(value)
#
Returns the the logarithm of a number, base 10.
>>> round(LOG10(86), 9)
1.934498451
>>> LOG10(10)
1.0
>>> LOG10(100000)
5.0
>>> LOG10(10**5)
5.0
#### MOD
__MOD__(dividend, divisor)
#
Returns the result of the modulo operator, the remainder after a division operation.
>>> MOD(3, 2)
1
>>> MOD(-3, 2)
1
>>> MOD(3, -2)
-1
>>> MOD(-3, -2)
-1
#### MROUND
__MROUND__(value, factor)
#
Rounds one number to the nearest integer multiple of another.
>>> MROUND(10, 3)
9
>>> MROUND(-10, -3)
-9
>>> round(MROUND(1.3, 0.2), 10)
1.4
>>> MROUND(5, -2)
Traceback (most recent call last):
...
ValueError: factor argument invalid
#### MULTINOMIAL
__MULTINOMIAL__(value1, *more_values)
#
Returns the factorial of the sum of values divided by the product of the values' factorials.
>>> MULTINOMIAL(2, 3, 4)
1260
>>> MULTINOMIAL(3)
1
>>> MULTINOMIAL(1,2,3)
60
>>> MULTINOMIAL(0,2,4,6)
13860
#### NUM
__NUM__(value)
#
For a Python floating-point value that's actually an integer, returns a Python integer type.
Otherwise, returns the value unchanged. This is helpful sometimes when a value comes from a
Numeric Grist column (represented as floats), but when int values are actually expected.
>>> NUM(-17.0)
-17
>>> NUM(1.5)
1.5
>>> NUM(4)
4
>>> NUM("NA")
'NA'
#### ODD
__ODD__(value)
#
Rounds a number up to the nearest odd integer.
>>> ODD(1.5)
3
>>> ODD(3)
3
>>> ODD(2)
3
>>> ODD(-1)
-1
>>> ODD(-2)
-3
#### PI
__PI__()
#
Returns the value of Pi to 14 decimal places.
>>> round(PI(), 9)
3.141592654
>>> round(PI()/2, 9)
1.570796327
>>> round(PI()*9, 8)
28.27433388
#### POWER
__POWER__(base, exponent)
#
Returns a number raised to a power.
>>> POWER(5,2)
25.0
>>> round(POWER(98.6,3.2), 3)
2401077.222
>>> round(POWER(4,5.0/4), 9)
5.656854249
#### PRODUCT
__PRODUCT__(factor1, *more_factors)
#
Returns the result of multiplying a series of numbers together. Each argument may be a number or
an array.
>>> PRODUCT([5,15,30])
2250
>>> PRODUCT([5,15,30], 2)
4500
>>> PRODUCT(5,15,[30],[2])
4500
#### QUOTIENT
__QUOTIENT__(dividend, divisor)
#
Returns one number divided by another, without the remainder.
>>> QUOTIENT(5, 2)
2
>>> QUOTIENT(4.5, 3.1)
1
>>> QUOTIENT(-10, 3)
-3
#### RADIANS
__RADIANS__(angle)
#
Converts an angle value in degrees to radians.
>>> round(RADIANS(270), 6)
4.712389
#### RAND
__RAND__()
#
Returns a random number between 0 inclusive and 1 exclusive.
#### RANDBETWEEN
__RANDBETWEEN__(low, high)
#
Returns a uniformly random integer between two values, inclusive.
#### ROMAN
__ROMAN__(number, form_unused=None)
#
Formats a number in Roman numerals. The second argument is ignored in this implementation.
>>> ROMAN(499,0)
'CDXCIX'
>>> ROMAN(499.2,0)
'CDXCIX'
>>> ROMAN(57)
'LVII'
>>> ROMAN(1912)
'MCMXII'
#### ROUND
__ROUND__(value, places=0)
#
Rounds a number to a certain number of decimal places,
by default to the nearest whole number if the number of places is not given.
Rounds away from zero ('up' for positive numbers)
in the case of a tie, i.e. when the last digit is 5.
>>> ROUND(1.4)
1.0
>>> ROUND(1.5)
2.0
>>> ROUND(2.5)
3.0
>>> ROUND(-2.5)
-3.0
>>> ROUND(2.15, 1)
2.2
>>> ROUND(-1.475, 2)
-1.48
>>> ROUND(21.5, -1)
20.0
>>> ROUND(626.3,-3)
1000.0
>>> ROUND(1.98,-1)
0.0
>>> ROUND(-50.55,-2)
-100.0
>>> ROUND(0)
0.0
#### ROUNDDOWN
__ROUNDDOWN__(value, places=0)
#
Rounds a number to a certain number of decimal places, always rounding down towards zero.
>>> ROUNDDOWN(3.2, 0)
3
>>> ROUNDDOWN(76.9,0)
76
>>> ROUNDDOWN(3.14159, 3)
3.141
>>> ROUNDDOWN(-3.14159, 1)
-3.1
>>> ROUNDDOWN(31415.92654, -2)
31400
#### ROUNDUP
__ROUNDUP__(value, places=0)
#
Rounds a number to a certain number of decimal places, always rounding up away from zero.
>>> ROUNDUP(3.2,0)
4
>>> ROUNDUP(76.9,0)
77
>>> ROUNDUP(3.14159, 3)
3.142
>>> ROUNDUP(-3.14159, 1)
-3.2
>>> ROUNDUP(31415.92654, -2)
31500
#### SERIESSUM
__SERIESSUM__(x, n, m, a)
#
Given parameters x, n, m, and a, returns the power series sum a_1*x^n + a_2*x^(n+m)
+ ... + a_i*x^(n+(i-1)m), where i is the number of entries in range `a`.
>>> SERIESSUM(1,0,1,1)
1
>>> SERIESSUM(2,1,0,[1,2,3])
12
>>> SERIESSUM(-3,1,1,[2,4,6])
-132
>>> round(SERIESSUM(PI()/4,0,2,[1,-1./FACT(2),1./FACT(4),-1./FACT(6)]), 6)
0.707103
#### SIGN
__SIGN__(value)
#
Given an input number, returns `-1` if it is negative, `1` if positive, and `0` if it is zero.
>>> SIGN(10)
1
>>> SIGN(4.0-4.0)
0
>>> SIGN(-0.00001)
-1
#### SIN
__SIN__(angle)
#
Returns the sine of an angle provided in radians.
>>> round(SIN(PI()), 10)
0.0
>>> SIN(PI()/2)
1.0
>>> round(SIN(30*PI()/180), 10)
0.5
>>> round(SIN(RADIANS(30)), 10)
0.5
#### SINH
__SINH__(value)
#
Returns the hyperbolic sine of any real number.
>>> round(2.868*SINH(0.0342*1.03), 7)
0.1010491
#### SQRT
__SQRT__(value)
#
Returns the positive square root of a positive number.
>>> SQRT(16)
4.0
>>> SQRT(-16)
Traceback (most recent call last):
...
ValueError: math domain error
>>> SQRT(ABS(-16))
4.0
#### SQRTPI
__SQRTPI__(value)
#
Returns the positive square root of the product of Pi and the given positive number.
>>> round(SQRTPI(1), 6)
1.772454
>>> round(SQRTPI(2), 6)
2.506628
#### SUBTOTAL
__SUBTOTAL__(function_code, range1, range2)
#
Returns a subtotal for a vertical range of cells using a specified aggregation function.
NoteThis function is not currently implemented in Grist.
#### SUM
__SUM__(value1, *more_values)
#
Returns the sum of a series of numbers. Each argument may be a number or an array.
Non-numeric values are ignored.
>>> SUM([5,15,30])
50
>>> SUM([5.,15,30], 2)
52.0
>>> SUM(5,15,[30],[2])
52
#### SUMIF
__SUMIF__(records, criterion, sum_range)
#
Returns a conditional sum across a range.
NoteThis function is not currently implemented in Grist.
#### SUMIFS
__SUMIFS__(sum_range, criteria_range1, criterion1, *args)
#
Returns the sum of a range depending on multiple criteria.
NoteThis function is not currently implemented in Grist.
#### SUMPRODUCT
__SUMPRODUCT__(array1, *more_arrays)
#
Multiplies corresponding components in two equally-sized arrays,
and returns the sum of those products.
>>> SUMPRODUCT([3,8,1,4,6,9], [2,6,5,7,7,3])
156
>>> SUMPRODUCT([], [], [])
0
>>> SUMPRODUCT([-0.25], [-2], [-3])
-1.5
>>> SUMPRODUCT([-0.25, -0.25], [-2, -2], [-3, -3])
-3.0
#### SUMSQ
__SUMSQ__(value1, value2)
#
Returns the sum of the squares of a series of numbers and/or cells.
NoteThis function is not currently implemented in Grist.
#### TAN
__TAN__(angle)
#
Returns the tangent of an angle provided in radians.
>>> round(TAN(0.785), 8)
0.99920399
>>> round(TAN(45*PI()/180), 10)
1.0
>>> round(TAN(RADIANS(45)), 10)
1.0
#### TANH
__TANH__(value)
#
Returns the hyperbolic tangent of any real number.
>>> round(TANH(-2), 6)
-0.964028
>>> TANH(0)
0.0
>>> round(TANH(0.5), 6)
0.462117
#### TRUNC
__TRUNC__(value, places=0)
#
Truncates a number to a certain number of significant digits by omitting less significant
digits.
>>> TRUNC(8.9)
8
>>> TRUNC(-8.9)
-8
>>> TRUNC(0.45)
0
#### UUID
__UUID__()
#
Generate a random UUID-formatted string identifier.
Since UUID() produces a different value each time it's called, it is best to use it in
[trigger formula](formulas.md#trigger-formulas) for new records.
This would only calculate UUID() once and freeze the calculated value. By contrast, a regular
formula may get recalculated any time the document is reloaded, producing a different value for
UUID() each time.
Schedule#
#### SCHEDULE
__SCHEDULE__(schedule, start=None, count=10, end=None)
#
Returns the list of `datetime` objects generated according to the `schedule` string. Starts at
`start`, which defaults to NOW(). Generates at most `count` results (10 by default). If `end` is
given, stops there.
The schedule has the format "INTERVAL: SLOTS, ...". For example:
annual: Jan-15, Apr-15, Jul-15 -- Three times a year on given dates at midnight.
annual: 1/15, 4/15, 7/15 -- Same as above.
monthly: /1 2pm, /15 2pm -- The 1st and the 15th of each month, at 2pm.
3-months: /10, +1m /20 -- Every 3 months on the 10th of month 1, 20th of month 2.
weekly: Mo 9am, Tu 9am, Fr 2pm -- Three times a week at specified times.
2-weeks: Mo, +1w Tu -- Every 2 weeks on Monday of week 1, Tuesday of week 2.
daily: 07:30, 21:00 -- Twice a day at specified times.
2-day: 12am, 4pm, +1d 8am -- Three times every two days, evenly spaced.
hourly: :15, :45 -- 15 minutes before and after each hour.
4-hour: :00, 1:20, 2:40 -- Three times every 4 hours, evenly spaced.
10-minute: +0s -- Every 10 minutes on the minute.
INTERVAL must be either of the form `N-unit` where `N` is a number and `unit` is one of `year`,
`month`, `week`, `day`, `hour`; or one of the aliases: `annual`, `monthly`, `weekly`, `daily`,
`hourly`, which mean `1-year`, `1-month`, etc.
SLOTS support the following units:
`Jan-15` or `1/15` -- Month and day of the month; available when INTERVAL is year-based.
`/15` -- Day of the month, available when INTERVAL is month-based.
`Mon`, `Mo`, `Friday` -- Day of the week (or abbreviation), when INTERVAL is week-based.
10am, 1:30pm, 15:45 -- Time of day, available for day-based or longer intervals.
:45, :00 -- Minutes of the hour, available when INTERVAL is hour-based.
+1d, +15d -- How many days to add to start of INTERVAL.
+1w -- How many weeks to add to start of INTERVAL.
+1m -- How many months to add to start of INTERVAL.
The SLOTS are always relative to the INTERVAL rather than to `start`. Week-based intervals start
on Sunday. E.g. `weekly: +1d, +4d` is the same as `weekly: Mon, Thu`, and generates times on
Mondays and Thursdays regardless of `start`.
The first generated time is determined by the *unit* of the INTERVAL without regard to the
multiple. E.g. both "2-week: Mon" and "3-week: Mon" start on the first Monday after `start`, and
then generate either every second or every third Monday after that. Similarly, `24-hour: :00`
starts with the first top-of-the-hour after `start` (not with midnight), and then repeats every
24 hours. To start with the midnight after `start`, use `daily: 0:00`.
For interval units of a day or longer, if time-of-day is not specified, it defaults to midnight.
The time zone of `start` determines the time zone of the generated times.
>>> def show(dates): return [d.strftime("%Y-%m-%d %H:%M") for d in dates]
>>> start = datetime(2018, 9, 4, 14, 0); # 2pm on Tue, Sep 4 2018.
>>> show(SCHEDULE('annual: Jan-15, Apr-15, Jul-15, Oct-15', start=start, count=4))
['2018-10-15 00:00', '2019-01-15 00:00', '2019-04-15 00:00', '2019-07-15 00:00']
>>> show(SCHEDULE('annual: 1/15, 4/15, 7/15', start=start, count=4))
['2019-01-15 00:00', '2019-04-15 00:00', '2019-07-15 00:00', '2020-01-15 00:00']
>>> show(SCHEDULE('monthly: /1 2pm, /15 5pm', start=start, count=4))
['2018-09-15 17:00', '2018-10-01 14:00', '2018-10-15 17:00', '2018-11-01 14:00']
>>> show(SCHEDULE('3-months: /10, +1m /20', start=start, count=4))
['2018-09-10 00:00', '2018-10-20 00:00', '2018-12-10 00:00', '2019-01-20 00:00']
>>> show(SCHEDULE('weekly: Mo 9am, Tu 9am, Fr 2pm', start=start, count=4))
['2018-09-07 14:00', '2018-09-10 09:00', '2018-09-11 09:00', '2018-09-14 14:00']
>>> show(SCHEDULE('2-weeks: Mo, +1w Tu', start=start, count=4))
['2018-09-11 00:00', '2018-09-17 00:00', '2018-09-25 00:00', '2018-10-01 00:00']
>>> show(SCHEDULE('daily: 07:30, 21:00', start=start, count=4))
['2018-09-04 21:00', '2018-09-05 07:30', '2018-09-05 21:00', '2018-09-06 07:30']
>>> show(SCHEDULE('2-day: 12am, 4pm, +1d 8am', start=start, count=4))
['2018-09-04 16:00', '2018-09-05 08:00', '2018-09-06 00:00', '2018-09-06 16:00']
>>> show(SCHEDULE('hourly: :15, :45', start=start, count=4))
['2018-09-04 14:15', '2018-09-04 14:45', '2018-09-04 15:15', '2018-09-04 15:45']
>>> show(SCHEDULE('4-hour: :00, +1H :20, +2H :40', start=start, count=4))
['2018-09-04 14:00', '2018-09-04 15:20', '2018-09-04 16:40', '2018-09-04 18:00']
Stats#
#### AVEDEV
__AVEDEV__(value1, value2)
#
Calculates the average of the magnitudes of deviations of data from a dataset's mean.
NoteThis function is not currently implemented in Grist.
#### AVERAGE
__AVERAGE__(value, *more_values)
#
Returns the numerical average value in a dataset, ignoring non-numerical values.
Each argument may be a value or an array. Values that are not numbers, including logical
and blank values, and text representations of numbers, are ignored.
>>> AVERAGE([2, -1.0, 11])
4.0
>>> AVERAGE([2, -1, 11, "Hello"])
4.0
>>> AVERAGE([2, -1, "Hello", DATE(2015,1,1)], True, [False, "123", "", 11])
4.0
>>> AVERAGE(False, True)
Traceback (most recent call last):
...
ZeroDivisionError: float division by zero
#### AVERAGEA
__AVERAGEA__(value, *more_values)
#
Returns the numerical average value in a dataset, counting non-numerical values as 0.
Each argument may be a value of an array. Values that are not numbers, including dates and text
representations of numbers, are counted as 0 (zero). Logical value of True is counted as 1, and
False as 0.
>>> AVERAGEA([2, -1.0, 11])
4.0
>>> AVERAGEA([2, -1, 11, "Hello"])
3.0
>>> AVERAGEA([2, -1, "Hello", DATE(2015,1,1)], True, [False, "123", "", 11.5])
1.5
>>> AVERAGEA(False, True)
0.5
#### AVERAGEIF
__AVERAGEIF__(criteria_range, criterion, average_range=None)
#
Returns the average of a range depending on criteria.
NoteThis function is not currently implemented in Grist.
#### AVERAGEIFS
__AVERAGEIFS__(average_range, criteria_range1, criterion1, *args)
#
Returns the average of a range depending on multiple criteria.
NoteThis function is not currently implemented in Grist.
#### AVERAGE_WEIGHTED
__AVERAGE_WEIGHTED__(pairs)
#
Given a list of (value, weight) pairs, finds the average of the values weighted by the
corresponding weights. Ignores any pairs with a non-numerical value or weight.
If you have two lists, of values and weights, use the Python built-in zip() function to create a
list of pairs.
>>> AVERAGE_WEIGHTED(((95, .25), (90, .1), ("X", .5), (85, .15), (88, .2), (82, .3), (70, None)))
87.7
>>> AVERAGE_WEIGHTED(zip([95, 90, "X", 85, 88, 82, 70], [25, 10, 50, 15, 20, 30, None]))
87.7
>>> AVERAGE_WEIGHTED(zip([95, 90, False, 85, 88, 82, 70], [.25, .1, .5, .15, .2, .3, True]))
87.7
#### BINOMDIST
__BINOMDIST__(num_successes, num_trials, prob_success, cumulative)
#
Calculates the probability of drawing a certain number of successes (or a maximum number of
successes) in a certain number of tries given a population of a certain size containing a
certain number of successes, with replacement of draws.
NoteThis function is not currently implemented in Grist.
#### CONFIDENCE
__CONFIDENCE__(alpha, standard_deviation, pop_size)
#
Calculates the width of half the confidence interval for a normal distribution.
NoteThis function is not currently implemented in Grist.
#### CORREL
__CORREL__(data_y, data_x)
#
Calculates r, the Pearson product-moment correlation coefficient of a dataset.
NoteThis function is not currently implemented in Grist.
#### COUNT
__COUNT__(value, *more_values)
#
Returns the count of numerical and date/datetime values in a dataset,
ignoring other types of values.
Each argument may be a value or an array. Values that are not numbers or dates, including logical
and blank values, and text representations of numbers, are ignored.
>>> COUNT([2, -1.0, 11])
3
>>> COUNT([2, -1, 11, "Hello"])
3
>>> COUNT([DATE(2000, 1, 1), DATE(2000, 1, 2), DATE(2000, 1, 3), "Hello"])
3
>>> COUNT([2, -1, "Hello", DATE(2015,1,1)], True, [False, "123", "", 11.5])
4
>>> COUNT(False, True)
0
#### COUNTA
__COUNTA__(value, *more_values)
#
Returns the count of all values in a dataset, including non-numerical values.
Each argument may be a value or an array.
>>> COUNTA([2, -1.0, 11])
3
>>> COUNTA([2, -1, 11, "Hello"])
4
>>> COUNTA([2, -1, "Hello", DATE(2015,1,1)], True, [False, "123", "", 11.5])
9
>>> COUNTA(False, True)
2
#### COVAR
__COVAR__(data_y, data_x)
#
Calculates the covariance of a dataset.
NoteThis function is not currently implemented in Grist.
#### CRITBINOM
__CRITBINOM__(num_trials, prob_success, target_prob)
#
Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria.
NoteThis function is not currently implemented in Grist.
#### DEVSQ
__DEVSQ__(value1, value2)
#
Calculates the sum of squares of deviations based on a sample.
NoteThis function is not currently implemented in Grist.
#### EXPONDIST
__EXPONDIST__(x, lambda_, cumulative)
#
Returns the value of the exponential distribution function with a specified lambda at a specified value.
NoteThis function is not currently implemented in Grist.
#### FDIST
__FDIST__(x, degrees_freedom1, degrees_freedom2)
#
Calculates the right-tailed F probability distribution (degree of diversity) for two data sets
with given input x. Alternately called Fisher-Snedecor distribution or Snedecor's F
distribution.
NoteThis function is not currently implemented in Grist.
#### FISHER
__FISHER__(value)
#
Returns the Fisher transformation of a specified value.
NoteThis function is not currently implemented in Grist.
#### FISHERINV
__FISHERINV__(value)
#
Returns the inverse Fisher transformation of a specified value.
NoteThis function is not currently implemented in Grist.
#### FORECAST
__FORECAST__(x, data_y, data_x)
#
Calculates the expected y-value for a specified x based on a linear regression of a dataset.
NoteThis function is not currently implemented in Grist.
#### F_DIST
__F_DIST__(x, degrees_freedom1, degrees_freedom2, cumulative)
#
Calculates the left-tailed F probability distribution (degree of diversity) for two data sets
with given input x. Alternately called Fisher-Snedecor distribution or Snedecor's F
distribution.
NoteThis function is not currently implemented in Grist.
#### F_DIST_RT
__F_DIST_RT__(x, degrees_freedom1, degrees_freedom2)
#
Calculates the right-tailed F probability distribution (degree of diversity) for two data sets
with given input x. Alternately called Fisher-Snedecor distribution or Snedecor's F
distribution.
NoteThis function is not currently implemented in Grist.
#### GEOMEAN
__GEOMEAN__(value1, value2)
#
Calculates the geometric mean of a dataset.
NoteThis function is not currently implemented in Grist.
#### HARMEAN
__HARMEAN__(value1, value2)
#
Calculates the harmonic mean of a dataset.
NoteThis function is not currently implemented in Grist.
#### HYPGEOMDIST
__HYPGEOMDIST__(num_successes, num_draws, successes_in_pop, pop_size)
#
Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws.
NoteThis function is not currently implemented in Grist.
#### INTERCEPT
__INTERCEPT__(data_y, data_x)
#
Calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0).
NoteThis function is not currently implemented in Grist.
#### KURT
__KURT__(value1, value2)
#
Calculates the kurtosis of a dataset, which describes the shape, and in particular the "peakedness" of that dataset.
NoteThis function is not currently implemented in Grist.
#### LARGE
__LARGE__(data, n)
#
Returns the nth largest element from a data set, where n is user-defined.
NoteThis function is not currently implemented in Grist.
#### LOGINV
__LOGINV__(x, mean, standard_deviation)
#
Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value.
NoteThis function is not currently implemented in Grist.
#### LOGNORMDIST
__LOGNORMDIST__(x, mean, standard_deviation)
#
Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value.
NoteThis function is not currently implemented in Grist.
#### MAX
__MAX__(value, *more_values)
#
Returns the maximum value in a dataset, ignoring values other than numbers and dates/datetimes.
Each argument may be a value or an array. Values that are not numbers or dates, including logical
and blank values, and text representations of numbers, are ignored. Returns 0 if the arguments
contain no numbers or dates.
>>> MAX([2, -1.5, 11.5])
11.5
>>> MAX([2, -1.5, "Hello"], True, [False, "123", "", 11.5])
11.5
>>> MAX(True, -123)
-123
>>> MAX("123", -123)
-123
>>> MAX("Hello", "123", True, False)
0
>>> MAX(DATE(2015, 1, 1), DATE(2015, 1, 2))
datetime.date(2015, 1, 2)
>>> MAX(DATE(2015, 1, 1), datetime.datetime(2015, 1, 1, 12, 34, 56))
datetime.datetime(2015, 1, 1, 12, 34, 56)
>>> MAX(DATE(2015, 1, 2), datetime.datetime(2015, 1, 1, 12, 34, 56))
datetime.date(2015, 1, 2)
#### MAXA
__MAXA__(value, *more_values)
#
Returns the maximum numeric value in a dataset.
Each argument may be a value of an array. Values that are not numbers, including dates and text
representations of numbers, are counted as 0 (zero). Logical value of True is counted as 1, and
False as 0. Returns 0 if the arguments contain no numbers.
>>> MAXA([2, -1.5, 11.5])
11.5
>>> MAXA([2, -1.5, "Hello", DATE(2015, 1, 1)], True, [False, "123", "", 11.5])
11.5
>>> MAXA(True, -123)
1
>>> MAXA("123", -123)
0
>>> MAXA("Hello", "123", DATE(2015, 1, 1))
0
#### MEDIAN
__MEDIAN__(value, *more_values)
#
Returns the median value in a numeric dataset, ignoring non-numerical values.
Each argument may be a value or an array. Values that are not numbers, including logical
and blank values, and text representations of numbers, are ignored.
Produces an error if the arguments contain no numbers.
The median is the middle number when all values are sorted. So half of the values in the dataset
are less than the median, and half of the values are greater. If there is an even number of
values in the dataset, returns the average of the two numbers in the middle.
>>> MEDIAN(1, 2, 3, 4, 5)
3
>>> MEDIAN(3, 5, 1, 4, 2)
3
>>> MEDIAN(range(10))
4.5
>>> MEDIAN("Hello", "123", DATE(2015, 1, 1), 12.3)
12.3
>>> MEDIAN("Hello", "123", DATE(2015, 1, 1))
Traceback (most recent call last):
...
ValueError: MEDIAN requires at least one number
#### MIN
__MIN__(value, *more_values)
#
Returns the minimum value in a dataset, ignoring values other than numbers and dates/datetimes.
Each argument may be a value or an array. Values that are not numbers or dates, including logical
and blank values, and text representations of numbers, are ignored. Returns 0 if the arguments
contain no numbers or dates.
>>> MIN([2, -1.5, 11.5])
-1.5
>>> MIN([2, -1.5, "Hello"], True, [False, "123", "", 11.5])
-1.5
>>> MIN(True, 123)
123
>>> MIN("-123", 123)
123
>>> MIN("Hello", "123", True, False)
0
>>> MIN(DATE(2015, 1, 1), DATE(2015, 1, 2))
datetime.date(2015, 1, 1)
>>> MIN(DATE(2015, 1, 1), datetime.datetime(2015, 1, 1, 12, 34, 56))
datetime.date(2015, 1, 1)
>>> MIN(DATE(2015, 1, 2), datetime.datetime(2015, 1, 1, 12, 34, 56))
datetime.datetime(2015, 1, 1, 12, 34, 56)
#### MINA
__MINA__(value, *more_values)
#
Returns the minimum numeric value in a dataset.
Each argument may be a value of an array. Values that are not numbers, including dates and text
representations of numbers, are counted as 0 (zero). Logical value of True is counted as 1, and
False as 0. Returns 0 if the arguments contain no numbers.
>>> MINA([2, -1.5, 11.5])
-1.5
>>> MINA([2, -1.5, "Hello", DATE(2015, 1, 1)], True, [False, "123", "", 11.5])
-1.5
>>> MINA(True, 123)
1
>>> MINA("-123", 123)
0
>>> MINA("Hello", "123", DATE(2015, 1, 1))
0
#### MODE
__MODE__(value1, value2)
#
Returns the most commonly occurring value in a dataset.
NoteThis function is not currently implemented in Grist.
#### NEGBINOMDIST
__NEGBINOMDIST__(num_failures, num_successes, prob_success)
#
Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials.
NoteThis function is not currently implemented in Grist.
#### NORMDIST
__NORMDIST__(x, mean, standard_deviation, cumulative)
#
Returns the value of the normal distribution function (or normal cumulative distribution
function) for a specified value, mean, and standard deviation.
NoteThis function is not currently implemented in Grist.
#### NORMINV
__NORMINV__(x, mean, standard_deviation)
#
Returns the value of the inverse normal distribution function for a specified value, mean, and standard deviation.
NoteThis function is not currently implemented in Grist.
#### NORMSDIST
__NORMSDIST__(x)
#
Returns the value of the standard normal cumulative distribution function for a specified value.
NoteThis function is not currently implemented in Grist.
#### NORMSINV
__NORMSINV__(x)
#
Returns the value of the inverse standard normal distribution function for a specified value.
NoteThis function is not currently implemented in Grist.
#### PEARSON
__PEARSON__(data_y, data_x)
#
Calculates r, the Pearson product-moment correlation coefficient of a dataset.
NoteThis function is not currently implemented in Grist.
#### PERCENTILE
__PERCENTILE__(data, percentile)
#
Returns the value at a given percentile of a dataset.
NoteThis function is not currently implemented in Grist.
#### PERCENTRANK
__PERCENTRANK__(data, value, significant_digits=None)
#
Returns the percentage rank (percentile) of a specified value in a dataset.
NoteThis function is not currently implemented in Grist.
#### PERCENTRANK_EXC
__PERCENTRANK_EXC__(data, value, significant_digits=None)
#
Returns the percentage rank (percentile) from 0 to 1 exclusive of a specified value in a dataset.
NoteThis function is not currently implemented in Grist.
#### PERCENTRANK_INC
__PERCENTRANK_INC__(data, value, significant_digits=None)
#
Returns the percentage rank (percentile) from 0 to 1 inclusive of a specified value in a dataset.
NoteThis function is not currently implemented in Grist.
#### PERMUT
__PERMUT__(n, k)
#
Returns the number of ways to choose some number of objects from a pool of a given size of objects, considering order.
NoteThis function is not currently implemented in Grist.
#### POISSON
__POISSON__(x, mean, cumulative)
#
Returns the value of the Poisson distribution function (or Poisson cumulative distribution
function) for a specified value and mean.
NoteThis function is not currently implemented in Grist.
#### PROB
__PROB__(data, probabilities, low_limit, high_limit=None)
#
Given a set of values and corresponding probabilities, calculates the probability that a value chosen at random falls between two limits.
NoteThis function is not currently implemented in Grist.
#### QUARTILE
__QUARTILE__(data, quartile_number)
#
Returns a value nearest to a specified quartile of a dataset.
NoteThis function is not currently implemented in Grist.
#### RANK_AVG
__RANK_AVG__(value, data, is_ascending=None)
#
Returns the rank of a specified value in a dataset. If there is more than one entry of the same value in the dataset, the average rank of the entries will be returned.
NoteThis function is not currently implemented in Grist.
#### RANK_EQ
__RANK_EQ__(value, data, is_ascending=None)
#
Returns the rank of a specified value in a dataset. If there is more than one entry of the same value in the dataset, the top rank of the entries will be returned.
NoteThis function is not currently implemented in Grist.
#### RSQ
__RSQ__(data_y, data_x)
#
Calculates the square of r, the Pearson product-moment correlation coefficient of a dataset.
NoteThis function is not currently implemented in Grist.
#### SKEW
__SKEW__(value1, value2)
#
Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean.
NoteThis function is not currently implemented in Grist.
#### SLOPE
__SLOPE__(data_y, data_x)
#
Calculates the slope of the line resulting from linear regression of a dataset.
NoteThis function is not currently implemented in Grist.
#### SMALL
__SMALL__(data, n)
#
Returns the nth smallest element from a data set, where n is user-defined.
NoteThis function is not currently implemented in Grist.
#### STANDARDIZE
__STANDARDIZE__(value, mean, standard_deviation)
#
Calculates the normalized equivalent of a random variable given mean and standard deviation of the distribution.
NoteThis function is not currently implemented in Grist.
#### STDEV
__STDEV__(value, *more_values)
#
Calculates the standard deviation based on a sample, ignoring non-numerical values.
>>> STDEV([2, 5, 8, 13, 10])
4.277849927241488
>>> STDEV([2, 5, 8, 13, 10, True, False, "Test"])
4.277849927241488
>>> STDEV([2, 5, 8, 13, 10], 3, 12, 15)
4.810702354423639
>>> STDEV([2, 5, 8, 13, 10], [3, 12, 15])
4.810702354423639
>>> STDEV([5])
Traceback (most recent call last):
...
ZeroDivisionError: float division by zero
#### STDEVA
__STDEVA__(value, *more_values)
#
Calculates the standard deviation based on a sample, setting text to the value `0`.
>>> STDEVA([2, 5, 8, 13, 10])
4.277849927241488
>>> STDEVA([2, 5, 8, 13, 10, True, False, "Test"])
4.969550137731641
>>> STDEVA([2, 5, 8, 13, 10], 1, 0, 0)
4.969550137731641
>>> STDEVA([2, 5, 8, 13, 10], [1, 0, 0])
4.969550137731641
>>> STDEVA([5])
Traceback (most recent call last):
...
ZeroDivisionError: float division by zero
#### STDEVP
__STDEVP__(value, *more_values)
#
Calculates the standard deviation based on an entire population, ignoring non-numerical values.
>>> STDEVP([2, 5, 8, 13, 10])
3.8262252939417984
>>> STDEVP([2, 5, 8, 13, 10, True, False, "Test"])
3.8262252939417984
>>> STDEVP([2, 5, 8, 13, 10], 3, 12, 15)
4.5
>>> STDEVP([2, 5, 8, 13, 10], [3, 12, 15])
4.5
>>> STDEVP([5])
0.0
#### STDEVPA
__STDEVPA__(value, *more_values)
#
Calculates the standard deviation based on an entire population, setting text to the value `0`.
>>> STDEVPA([2, 5, 8, 13, 10])
3.8262252939417984
>>> STDEVPA([2, 5, 8, 13, 10, True, False, "Test"])
4.648588495446763
>>> STDEVPA([2, 5, 8, 13, 10], 1, 0, 0)
4.648588495446763
>>> STDEVPA([2, 5, 8, 13, 10], [1, 0, 0])
4.648588495446763
>>> STDEVPA([5])
0.0
#### STEYX
__STEYX__(data_y, data_x)
#
Calculates the standard error of the predicted y-value for each x in the regression of a dataset.
NoteThis function is not currently implemented in Grist.
#### TDIST
__TDIST__(x, degrees_freedom, tails)
#
Calculates the probability for Student's t-distribution with a given input (x).
NoteThis function is not currently implemented in Grist.
#### TINV
__TINV__(probability, degrees_freedom)
#
Calculates the inverse of the two-tailed TDIST function.
NoteThis function is not currently implemented in Grist.
#### TRIMMEAN
__TRIMMEAN__(data, exclude_proportion)
#
Calculates the mean of a dataset excluding some proportion of data from the high and low ends of the dataset.
NoteThis function is not currently implemented in Grist.
#### TTEST
__TTEST__(range1, range2, tails, type)
#
Returns the probability associated with t-test. Determines whether two samples are likely to have come from the same two underlying populations that have the same mean.
NoteThis function is not currently implemented in Grist.
#### T_INV
__T_INV__(probability, degrees_freedom)
#
Calculates the negative inverse of the one-tailed TDIST function.
NoteThis function is not currently implemented in Grist.
#### T_INV_2T
__T_INV_2T__(probability, degrees_freedom)
#
Calculates the inverse of the two-tailed TDIST function.
NoteThis function is not currently implemented in Grist.
#### VAR
__VAR__(value1, value2)
#
Calculates the variance based on a sample.
NoteThis function is not currently implemented in Grist.
#### VARA
__VARA__(value1, value2)
#
Calculates an estimate of variance based on a sample, setting text to the value `0`.
NoteThis function is not currently implemented in Grist.
#### VARP
__VARP__(value1, value2)
#
Calculates the variance based on an entire population.
NoteThis function is not currently implemented in Grist.
#### VARPA
__VARPA__(value1, value2)
#
Calculates the variance based on an entire population, setting text to the value `0`.
NoteThis function is not currently implemented in Grist.
#### WEIBULL
__WEIBULL__(x, shape, scale, cumulative)
#
Returns the value of the Weibull distribution function (or Weibull cumulative distribution
function) for a specified shape and scale.
NoteThis function is not currently implemented in Grist.
#### ZTEST
__ZTEST__(data, value, standard_deviation)
#
Returns the two-tailed P-value of a Z-test with standard distribution.
NoteThis function is not currently implemented in Grist.
Text#
#### CHAR
__CHAR__(table_number)
#
Convert a number into a character according to the current Unicode table.
Same as `unichr(number)`.
>>> CHAR(65)
u'A'
>>> CHAR(33)
u'!'
#### CLEAN
__CLEAN__(text)
#
Returns the text with the non-printable characters removed.
This removes both characters with values 0 through 31, and other Unicode characters in the
"control characters" category.
>>> CLEAN(CHAR(9) + "Monthly report" + CHAR(10))
u'Monthly report'
#### CODE
__CODE__(string)
#
Returns the numeric Unicode map value of the first character in the string provided.
Same as `ord(string[0])`.
>>> CODE("A")
65
>>> CODE("!")
33
>>> CODE("!A")
33
#### CONCAT
__CONCAT__(string, *more_strings)
#
Joins together any number of text strings into one string. Also available under the name
`CONCATENATE`. Similar to the Python expression `"".join(array_of_strings)`.
>>> CONCAT("Stream population for ", "trout", " ", "species", " is ", 32, "/mile.")
u'Stream population for trout species is 32/mile.'
>>> CONCAT("In ", 4, " days it is ", datetime.date(2016,1,1))
u'In 4 days it is 2016-01-01'
>>> CONCAT("abc")
u'abc'
>>> CONCAT(0, "abc")
u'0abc'
>>> assert CONCAT(2, u" crème ", u"brûlée") == u'2 crème brûlée'
#### CONCATENATE
__CONCATENATE__(string, *more_strings)
#
Joins together any number of text strings into one string. Also available under the name
`CONCAT`. Similar to the Python expression `"".join(array_of_strings)`.
>>> CONCATENATE("Stream population for ", "trout", " ", "species", " is ", 32, "/mile.")
u'Stream population for trout species is 32/mile.'
>>> CONCATENATE("In ", 4, " days it is ", datetime.date(2016,1,1))
u'In 4 days it is 2016-01-01'
>>> CONCATENATE("abc")
u'abc'
>>> CONCATENATE(0, "abc")
u'0abc'
>>> assert CONCATENATE(2, u" crème ", u"brûlée") == u'2 crème brûlée'
>>> assert CONCATENATE(2, " crème ", u"brûlée") == u'2 crème brûlée'
>>> assert CONCATENATE(2, " crème ", "brûlée") == u'2 crème brûlée'
#### DOLLAR
__DOLLAR__(number, decimals=2)
#
Formats a number into a formatted dollar amount, with decimals rounded to the specified place (.
If decimals value is omitted, it defaults to 2.
>>> DOLLAR(1234.567)
'$1,234.57'
>>> DOLLAR(1234.567, -2)
'$1,200'
>>> DOLLAR(-1234.567, -2)
'($1,200)'
>>> DOLLAR(-0.123, 4)
'($0.1230)'
>>> DOLLAR(99.888)
'$99.89'
>>> DOLLAR(0)
'$0.00'
>>> DOLLAR(10, 0)
'$10'
#### EXACT
__EXACT__(string1, string2)
#
Tests whether two strings are identical. Same as `string2 == string2`.
>>> EXACT("word", "word")
True
>>> EXACT("Word", "word")
False
>>> EXACT("w ord", "word")
False
#### FIND
__FIND__(find_text, within_text, start_num=1)
#
Returns the position at which a string is first found within text.
Find is case-sensitive. The returned position is 1 if within_text starts with find_text.
Start_num specifies the character at which to start the search, defaulting to 1 (the first
character of within_text).
If find_text is not found, or start_num is invalid, raises ValueError.
>>> FIND("M", "Miriam McGovern")
1
>>> FIND("m", "Miriam McGovern")
6
>>> FIND("M", "Miriam McGovern", 3)
8
>>> FIND(" #", "Hello world # Test")
12
>>> FIND("gle", "Google", 1)
4
>>> FIND("GLE", "Google", 1)
Traceback (most recent call last):
...
ValueError: substring not found
>>> FIND("page", "homepage")
5
>>> FIND("page", "homepage", 6)
Traceback (most recent call last):
...
ValueError: substring not found
#### FIXED
__FIXED__(number, decimals=2, no_commas=False)
#
Formats a number with a fixed number of decimal places (2 by default), and commas.
If no_commas is True, then omits the commas.
>>> FIXED(1234.567, 1)
'1,234.6'
>>> FIXED(1234.567, -1)
'1,230'
>>> FIXED(-1234.567, -1, True)
'-1230'
>>> FIXED(44.332)
'44.33'
>>> FIXED(3521.478, 2, False)
'3,521.48'
>>> FIXED(-3521.478, 1, True)
'-3521.5'
>>> FIXED(3521.478, 0, True)
'3521'
>>> FIXED(3521.478, -2, True)
'3500'
#### LEFT
__LEFT__(string, num_chars=1)
#
Returns a substring of length num_chars from the beginning of the given string. If num_chars is
omitted, it is assumed to be 1. Same as `string[:num_chars]`.
>>> LEFT("Sale Price", 4)
'Sale'
>>> LEFT('Swededn')
'S'
>>> LEFT('Text', -1)
Traceback (most recent call last):
...
ValueError: num_chars invalid
#### LEN
__LEN__(text)
#
Returns the number of characters in a text string, or the number of items in a list. Same as
[`len`](https://docs.python.org/3/library/functions.html#len) in python.
See [Record Set](#recordset) for an example of using `len` on a list of records.
>>> LEN("Phoenix, AZ")
11
>>> LEN("")
0
>>> LEN(" One ")
11
#### LOWER
__LOWER__(text)
#
Converts a specified string to lowercase. Same as `text.lower()`.
>>> LOWER("E. E. Cummings")
'e. e. cummings'
>>> LOWER("Apt. 2B")
'apt. 2b'
#### MID
__MID__(text, start_num, num_chars)
#
Returns a segment of a string, starting at start_num. The first character in text has
start_num 1.
>>> MID("Fluid Flow", 1, 5)
'Fluid'
>>> MID("Fluid Flow", 7, 20)
'Flow'
>>> MID("Fluid Flow", 20, 5)
''
>>> MID("Fluid Flow", 0, 5)
Traceback (most recent call last):
...
ValueError: start_num invalid
#### PHONE_FORMAT
__PHONE_FORMAT__(value, country=None, format=None)
#
Formats a phone number.
With no optional arguments, the number must start with "+" and the international dialing prefix,
and will be formatted as an international number, e.g. `+12345678901` becomes `+1 234-567-8901`.
The `country` argument allows specifying a 2-letter country code (e.g. "US" or "GB") for
interpreting phone numbers that don't start with "+". E.g. `PHONE_FORMAT('2025555555', 'US')`
would be seen as a US number and formatted as "(202) 555-5555". Phone numbers that start with
"+" ignore `country`. E.g. `PHONE_FORMAT('+33555555555', 'US')` is a French number because '+33'
is the international prefix for France.
The `format` argument specifies the output format, according to this table:
- `"#"` or `"NATL"` (default) - use the national format, without the international dialing
prefix, when possible. E.g. `(234) 567-8901` for "US", or `02 34 56 78 90` for "FR". If
`country` is omitted, or the number does not correspond to the given country, the
international format is used instead.
- `"+"` or `"INTL"` - international format, e.g. `+1 234-567-8901` or
`+33 2 34 56 78 90`.
- `"*"` or `"E164"` - E164 format, like international but with no separators, e.g.
`+12345678901`.
- `"tel"` or `"RFC3966"` - format suitable to use as a [hyperlink](col-types.md#hyperlinks),
e.g. 'tel:+1-234-567-8901'.
When specifying the `format` argument, you may omit the `country` argument. I.e.
`PHONE_FORMAT(value, "tel")` is equivalent to `PHONE_FORMAT(value, None, "tel")`.
For more details, see the [phonenumbers](https://github.com/daviddrysdale/python-phonenumbers)
Python library, which underlies this function.
>>> PHONE_FORMAT("+12345678901")
u'+1 234-567-8901'
>>> PHONE_FORMAT("2345678901", "US")
u'(234) 567-8901'
>>> PHONE_FORMAT("2345678901", "GB")
u'023 4567 8901'
>>> PHONE_FORMAT("2345678901", "GB", "+")
u'+44 23 4567 8901'
>>> PHONE_FORMAT("+442345678901", "GB")
u'023 4567 8901'
>>> PHONE_FORMAT("+12345678901", "GB")
u'+1 234-567-8901'
>>> PHONE_FORMAT("(234) 567-8901")
Traceback (most recent call last):
...
NumberParseException: (0) Missing or invalid default region.
>>> PHONE_FORMAT("(234)567 89-01", "US", "tel")
u'tel:+1-234-567-8901'
>>> PHONE_FORMAT("2/3456/7890", "FR", '#')
u'02 34 56 78 90'
>>> PHONE_FORMAT("+33234567890", '#')
u'+33 2 34 56 78 90'
>>> PHONE_FORMAT("+33234567890", 'tel')
u'tel:+33-2-34-56-78-90'
>>> PHONE_FORMAT("tel:+1-234-567-8901", country="US", format="*")
u'+12345678901'
>>> PHONE_FORMAT(33234567890)
Traceback (most recent call last):
...
TypeError: Phone number must be a text value. If formatting a value from a Numeric column, convert that column to Text first.
#### PROPER
__PROPER__(text)
#
Capitalizes each word in a specified string. It converts the first letter of each word to
uppercase, and all other letters to lowercase. Same as `text.title()`.
>>> PROPER('this is a TITLE')
'This Is A Title'
>>> PROPER('2-way street')
'2-Way Street'
>>> PROPER('76BudGet')
'76Budget'
#### REGEXEXTRACT
__REGEXEXTRACT__(text, regular_expression)
#
Extracts the first part of text that matches regular_expression.
>>> REGEXEXTRACT("Google Doc 101", "[0-9]+")
'101'
>>> REGEXEXTRACT("The price today is $826.25", "[0-9]*\.[0-9]+[0-9]+")
'826.25'
If there is a parenthesized expression, it is returned instead of the whole match.
>>> REGEXEXTRACT("(Content) between brackets", "\(([A-Za-z]+)\)")
'Content'
>>> REGEXEXTRACT("Foo", "Bar")
Traceback (most recent call last):
...
ValueError: REGEXEXTRACT text does not match
#### REGEXMATCH
__REGEXMATCH__(text, regular_expression)
#
Returns whether a piece of text matches a regular expression.
>>> REGEXMATCH("Google Doc 101", "[0-9]+")
True
>>> REGEXMATCH("Google Doc", "[0-9]+")
False
>>> REGEXMATCH("The price today is $826.25", "[0-9]*\.[0-9]+[0-9]+")
True
>>> REGEXMATCH("(Content) between brackets", "\(([A-Za-z]+)\)")
True
>>> REGEXMATCH("Foo", "Bar")
False
#### REGEXREPLACE
__REGEXREPLACE__(text, regular_expression, replacement)
#
Replaces all parts of text matching the given regular expression with replacement text.
>>> REGEXREPLACE("Google Doc 101", "[0-9]+", "777")
'Google Doc 777'
>>> REGEXREPLACE("Google Doc", "[0-9]+", "777")
'Google Doc'
>>> REGEXREPLACE("The price is $826.25", "[0-9]*\.[0-9]+[0-9]+", "315.75")
'The price is $315.75'
>>> REGEXREPLACE("(Content) between brackets", "\(([A-Za-z]+)\)", "Word")
'Word between brackets'
>>> REGEXREPLACE("Foo", "Bar", "Baz")
'Foo'
#### REPLACE
__REPLACE__(text, position, length, new_text)
#
Replaces part of a text string with a different text string. Position is counted from 1.
>>> REPLACE("abcdefghijk", 6, 5, "*")
'abcde*k'
>>> REPLACE("2009", 3, 2, "10")
'2010'
>>> REPLACE('123456', 1, 3, '@')
'@456'
>>> REPLACE('foo', 1, 0, 'bar')
'barfoo'
>>> REPLACE('foo', 0, 1, 'bar')
Traceback (most recent call last):
...
ValueError: position invalid
#### REPT
__REPT__(text, number_times)
#
Returns specified text repeated a number of times. Same as `text * number_times`.
The result of the REPT function cannot be longer than 32767 characters, or it raises a
ValueError.
>>> REPT("*-", 3)
'*-*-*-'
>>> REPT('-', 10)
'----------'
>>> REPT('-', 0)
''
>>> len(REPT('---', 10000))
30000
>>> REPT('---', 11000)
Traceback (most recent call last):
...
ValueError: number_times invalid
>>> REPT('-', -1)
Traceback (most recent call last):
...
ValueError: number_times invalid
#### RIGHT
__RIGHT__(string, num_chars=1)
#
Returns a substring of length num_chars from the end of a specified string. If num_chars is
omitted, it is assumed to be 1. Same as `string[-num_chars:]`.
>>> RIGHT("Sale Price", 5)
'Price'
>>> RIGHT('Stock Number')
'r'
>>> RIGHT('Text', 100)
'Text'
>>> RIGHT('Text', -1)
Traceback (most recent call last):
...
ValueError: num_chars invalid
#### SEARCH
__SEARCH__(find_text, within_text, start_num=1)
#
Returns the position at which a string is first found within text, ignoring case.
Find is case-sensitive. The returned position is 1 if within_text starts with find_text.
Start_num specifies the character at which to start the search, defaulting to 1 (the first
character of within_text).
If find_text is not found, or start_num is invalid, raises ValueError.
>>> SEARCH("e", "Statements", 6)
7
>>> SEARCH("margin", "Profit Margin")
8
>>> SEARCH(" ", "Profit Margin")
7
>>> SEARCH('"', 'The "boss" is here.')
5
>>> SEARCH("gle", "Google")
4
>>> SEARCH("GLE", "Google")
4
#### SUBSTITUTE
__SUBSTITUTE__(text, old_text, new_text, instance_num=None)
#
Replaces existing text with new text in a string. It is useful when you know the substring of
text to replace. Use REPLACE when you know the position of text to replace.
If instance_num is given, it specifies which occurrence of old_text to replace. If omitted, all
occurrences are replaced.
Same as `text.replace(old_text, new_text)` when instance_num is omitted.
>>> SUBSTITUTE("Sales Data", "Sales", "Cost")
u'Cost Data'
>>> SUBSTITUTE("Quarter 1, 2008", "1", "2", 1)
u'Quarter 2, 2008'
>>> SUBSTITUTE("Quarter 1, 2011", "1", "2", 3)
u'Quarter 1, 2012'
#### T
__T__(value)
#
Returns value if value is text, or the empty string when value is not text.
>>> T('Text')
u'Text'
>>> T(826)
u''
>>> T('826')
u'826'
>>> T(False)
u''
>>> T('100 points')
u'100 points'
>>> T(AltText('Text'))
u'Text'
>>> T(float('nan'))
u''
#### TASTEME
__TASTEME__(food)
#
For any given piece of text, decides if it is tasty or not.
This is not serious. It appeared as an Easter egg, and is kept as such. It is in fact a puzzle
to figure out the underlying simple rule. It has been surprisingly rarely cracked, even after
reading the source code, which is freely available and may entertain Python fans.
>>> TASTEME('Banana')
True
>>> TASTEME('Garlic')
False
#### TEXT
__TEXT__(number, format_type)
#
Converts a number into text according to a specified format. It is not yet implemented in
Grist. You can use the similar Python functions str() to convert numbers into strings, and
optionally format() to specify the number format.
NoteThis function is not currently implemented in Grist.
#### TRIM
__TRIM__(text)
#
Removes all spaces from text except for single spaces between words. Note that TRIM does not
remove other whitespace such as tab or newline characters.
>>> TRIM(" First Quarter\n Earnings ")
'First Quarter\n Earnings'
>>> TRIM("")
''
#### UPPER
__UPPER__(text)
#
Converts a specified string to uppercase. Same as `text.upper()`.
>>> UPPER("e. e. cummings")
'E. E. CUMMINGS'
>>> UPPER("Apt. 2B")
'APT. 2B'
#### VALUE
__VALUE__(text)
#
Converts a string in accepted date, time or number formats into a number or date.
>>> VALUE("$1,000")
1000
>>> assert VALUE("16:48:00") - VALUE("12:00:00") == datetime.timedelta(0, 17280)
>>> VALUE("01/01/2012")
datetime.datetime(2012, 1, 1, 0, 0)
>>> VALUE("")
0
>>> VALUE(0)
0
>>> VALUE("826")
826
>>> VALUE("-826.123123123")
-826.123123123
>>> VALUE(float('nan'))
nan
>>> VALUE("Invalid")
Traceback (most recent call last):
...
ValueError: text cannot be parsed to a number
>>> VALUE("13/13/13")
Traceback (most recent call last):
...
ValueError: text cannot be parsed to a number