Function Reference#

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)

See 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

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).

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. 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.

Every table in the document is available to all formulas.

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)

For example:

People.lookupOne(First_Name="Lewis", Last_Name="Carroll")
People.lookupOne(Email=$Work_Email)

Learn more about 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 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.

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 for useful properties offered by the returned object. In particular, methods like .find.le allow searching for nearest values.

See 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.

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"))

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.

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)

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)

• “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("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'))

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(DATE(2011, 4, 15))
15

>>> DAY("5/31/2012")
31

>>> DAY(datetime.datetime(1900, 1, 1))
1

>>> 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

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(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(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(XL_TO_DATE(0.75))
18

>>> HOUR("7/18/2011 7:45")
7

>>> HOUR("4/21/2012")
0

>>> 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(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(DATE(2011, 4, 15))
4

>>> MONTH("5/31/2012")
5

>>> MONTH(datetime.datetime(1900, 1, 1))
1

• 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]

>>> SECOND(XL_TO_DATE(0.75))
0

>>> SECOND("7/18/2011 7:45:13")
13

>>> SECOND(datetime.time(12, 58, 59))
59

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

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

(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(DATE(2011, 4, 15))
2011

>>> YEAR("5/31/2030")
2030

>>> YEAR(datetime.datetime(1900, 1, 1))
1900

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 https://en.wikipedia.org/wiki/360-day_calendar for explanation of the US 30/360 and European 30/360 methods. See for analysis of Excel’s particular implementation.

Basis -1 is similar to 1, but differs from Excel when dates span both leap and non-leap years. It matches the calculation in Google Sheets, counting the days in each year as a fraction of that year’s length.

Fraction of the year between 1/1/2012 and 7/30/12, omitting the Basis argument.

>>> "%.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'

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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

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

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(True)
True

>>> ISLOGICAL(False)
True

>>> ISLOGICAL(0)
False

>>> ISLOGICAL(None)
False

>>> ISLOGICAL("Test")
False

>>> ISNA(float('nan'))
True

>>> ISNA(0.0)
False

>>> ISNA('text')
False

>>> ISNA(float('-inf'))
False

>>> ISNONTEXT("asdf")
False

>>> ISNONTEXT("")
False

>>> ISNONTEXT(AltText("text"))
False

>>> ISNONTEXT(17.0)
True

>>> ISNONTEXT(None)
True

>>> ISNONTEXT(datetime.date(2011, 1, 1))
True

>>> 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

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

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("asdf")
True

>>> ISTEXT("")
True

>>> ISTEXT(AltText("text"))
True

>>> ISTEXT(17.0)
False

>>> ISTEXT(None)
False

>>> ISTEXT(datetime.date(2011, 1, 1))
False

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(7)
7

>>> N(7.1)
7.1

>>> N("Even")
0

>>> N("7")
0

>>> N(True)
1

>>> N(datetime.datetime(2011, 4, 17))
40650.0

>>> math.isnan(NA())
True

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.

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"))

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

>>> 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

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

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(123)
False

>>> NOT(0)
True

>>> 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

For example:

People.lookupOne(First_Name="Lewis", Last_Name="Carroll")
People.lookupOne(Email=$Work_Email)

Learn more about 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 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.

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 for useful properties offered by the returned object. In particular, methods like .find.le allow searching for nearest values.

See 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.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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)

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 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.

For example:

VLOOKUP(People, First_Name="Lewis", Last_Name="Carroll")
VLOOKUP(People, First_Name="Lewis", Last_Name="Carroll").Age

>>> ABS(2)
2

>>> ABS(-2)
2

>>> ABS(-4)
4

>>> round(ACOS(-0.5), 9)
2.094395102

>>> round(ACOS(-0.5)*180/PI(), 10)
120.0

>>> ACOSH(1)
0.0

>>> round(ACOSH(10), 7)
2.9932228

>>> ARABIC("LVII")
57

>>> ARABIC('mcmxii')
1912

>>> round(ASIN(-0.5), 9)
-0.523598776

>>> round(ASIN(-0.5)*180/PI(), 10)
-30.0

>>> round(DEGREES(ASIN(-0.5)), 10)
-30.0

>>> round(ASINH(-2.5), 9)
-1.647231146

>>> round(ASINH(10), 9)
2.99822295

>>> round(ATAN(1), 9)
0.785398163

>>> ATAN(1)*180/PI()
45.0

>>> DEGREES(ATAN(1))
45.0

>>> 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

>>> round(ATANH(0.76159416), 9)
1.00000001

>>> round(ATANH(-0.1), 9)
-0.100335348

>>> 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(8,2)
28

>>> COMBIN(4,2)
6

>>> COMBIN(10,7)
120

>>> round(COS(1.047), 7)
0.5001711

>>> round(COS(60*PI()/180), 10)
0.5

>>> round(COS(RADIANS(60)), 10)
0.5

>>> round(COSH(4), 6)
27.308233

>>> round(COSH(EXP(1)), 7)
7.6101251

>>> round(DEGREES(ACOS(-0.5)), 10)
120.0

>>> DEGREES(PI())
180.0

>>> EVEN(1.5)
2

>>> EVEN(3)
4

>>> EVEN(2)
2

>>> EVEN(-1)
-2

>>> round(EXP(1), 8)
2.71828183

>>> round(EXP(2), 7)
7.3890561

>>> 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(6)
48

>>> FACTDOUBLE(7)
105

>>> FACTDOUBLE(3)
3

>>> FACTDOUBLE(4)
8

>>> 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(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(8.9)
8

>>> INT(-8.9)
-9

>>> 19.5-INT(19.5)
0.5

>>> 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

>>> round(LN(86), 7)
4.4543473

>>> round(LN(2.7182818), 7)
1.0

>>> round(LN(EXP(3)), 10)
3.0

>>> LOG(10)
1.0

>>> LOG(8, 2)
3.0

>>> round(LOG(86, 2.7182818), 7)
4.4543473

>>> round(LOG10(86), 9)
1.934498451

>>> LOG10(10)
1.0

>>> LOG10(100000)
5.0

>>> LOG10(10**5)
5.0

>>> MOD(3, 2)
1

>>> MOD(-3, 2)
1

>>> MOD(3, -2)
-1

>>> MOD(-3, -2)
-1

>>> 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(2, 3, 4)
1260

>>> MULTINOMIAL(3)
1

>>> MULTINOMIAL(1,2,3)
60

>>> MULTINOMIAL(0,2,4,6)
13860

>>> NUM(-17.0)
-17

>>> NUM(1.5)
1.5

>>> NUM(4)
4

>>> NUM("NA")
'NA'

>>> ODD(1.5)
3

>>> ODD(3)
3

>>> ODD(2)
3

>>> ODD(-1)
-1

>>> ODD(-2)
-3

>>> round(PI(), 9)
3.141592654

>>> round(PI()/2, 9)
1.570796327

>>> round(PI()*9, 8)
28.27433388

>>> POWER(5,2)
25.0

>>> round(POWER(98.6,3.2), 3)
2401077.222

>>> round(POWER(4,5.0/4), 9)
5.656854249

>>> PRODUCT([5,15,30])
2250

>>> PRODUCT([5,15,30], 2)
4500

>>> PRODUCT(5,15,[30],[2])
4500

>>> QUOTIENT(5, 2)
2

>>> QUOTIENT(4.5, 3.1)
1

>>> QUOTIENT(-10, 3)
-3

>>> round(RADIANS(270), 6)
4.712389

>>> ROMAN(499,0)
'CDXCIX'

>>> ROMAN(499.2,0)
'CDXCIX'

>>> ROMAN(57)
'LVII'

>>> ROMAN(1912)
'MCMXII'

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(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(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(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(10)
1

>>> SIGN(4.0-4.0)
0

>>> SIGN(-0.00001)
-1

>>> 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

>>> round(2.868*SINH(0.0342*1.03), 7)
0.1010491

>>> SQRT(16)
4.0

>>> SQRT(-16)
Traceback (most recent call last):
  ...
ValueError: math domain error

>>> SQRT(ABS(-16))
4.0

>>> round(SQRTPI(1), 6)
1.772454

>>> round(SQRTPI(2), 6)
2.506628

NoteThis function is not currently implemented in Grist.

>>> SUM([5,15,30])
50

>>> SUM([5.,15,30], 2)
52.0

>>> SUM(5,15,[30],[2])
52

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

>>> 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

NoteThis function is not currently implemented in Grist.

>>> round(TAN(0.785), 8)
0.99920399

>>> round(TAN(45*PI()/180), 10)
1.0

>>> round(TAN(RADIANS(45)), 10)
1.0

>>> round(TANH(-2), 6)
-0.964028

>>> TANH(0)
0.0

>>> round(TANH(0.5), 6)
0.462117

>>> TRUNC(8.9)
8

>>> TRUNC(-8.9)
-8

>>> TRUNC(0.45)
0

Since UUID() produces a different value each time it’s called, it is best to use it in trigger formula 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.

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']

NoteThis function is not currently implemented in Grist.

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

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

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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

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

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

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)

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

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

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)

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

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

>>> 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([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([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([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

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

NoteThis function is not currently implemented in Grist.

>>> CHAR(65)
u'A'

>>> CHAR(33)
u'!'

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("A")
65

>>> CODE("!")
33

>>> CODE("!A")
33

>>> 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("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(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("word", "word")
True

>>> EXACT("Word", "word")
False

>>> EXACT("w ord", "word")
False

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(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("Sale Price", 4)
'Sale'

>>> LEFT('Swededn')
'S'

>>> LEFT('Text', -1)
Traceback (most recent call last):
...
ValueError: num_chars invalid

>>> LEN("Phoenix, AZ")
11

>>> LEN("")
0

>>> LEN("     One   ")
11

>>> LOWER("E. E. Cummings")
'e. e. cummings'

>>> LOWER("Apt. 2B")
'apt. 2b'

>>> 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

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, 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 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('this is a TITLE')
'This Is A Title'

>>> PROPER('2-way street')
'2-Way Street'

>>> PROPER('76BudGet')
'76Budget'

>>> 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("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("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("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

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("Sale Price", 5)
'Price'

>>> RIGHT('Stock Number')
'r'

>>> RIGHT('Text', 100)
'Text'

>>> RIGHT('Text', -1)
Traceback (most recent call last):
...
ValueError: num_chars invalid

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

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('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''

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

NoteThis function is not currently implemented in Grist.

>>> TRIM(" First Quarter\n    Earnings     ")
'First Quarter\n Earnings'

>>> TRIM("")
''

>>> UPPER("e. e. cummings")
'E. E. CUMMINGS'

>>> UPPER("Apt. 2B")
'APT. 2B'

>>> 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