Function List#

Full power of Python#

Grist uses Python (version 2.7) for formulas. You can use nearly all features of Python and its standard library. See Python documentation. Grist code runs in a secure sandbox, so Python code cannot access anything other than your document.

Note that Python is case-sensitive, which applies to all functions, as well as Grist table and column names.

The table below lists Grist-specific functions for accessing your document data, as well as a suite of Excel-like functions provided by Grist. Don’t forget also that the entire Python’s standard library is available.

Category Functions
Grist Record or rec, $Field or rec.Field, $group or rec.group, RecordSet, UserTable, all, lookupOne, lookupRecords
Date DATE, DATEADD, DATEDIF, DATEVALUE, DATE_TO_XL, DAY, DAYS, DTIME, EDATE, EOMONTH, HOUR, ISOWEEKNUM, MINUTE, MONTH, NOW, SECOND, TODAY, WEEKDAY, WEEKNUM, XL_TO_DATE, YEAR, YEARFRAC
Info CELL, ISBLANK, ISEMAIL, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, ISURL, N, NA, TYPE
Logical AND, FALSE, IF, IFERROR, NOT, OR, TRUE
Lookup lookupOne, lookupRecords, ADDRESS, CHOOSE, COLUMN, COLUMNS, GETPIVOTDATA, HLOOKUP, HYPERLINK, INDEX, INDIRECT, LOOKUP, MATCH, OFFSET, ROW, ROWS, VLOOKUP
Math ABS, ACOS, ACOSH, ARABIC, ASIN, ASINH, ATAN, ATAN2, ATANH, CEILING, COMBIN, COS, COSH, DEGREES, EVEN, EXP, FACT, FACTDOUBLE, FLOOR, GCD, INT, LCM, LN, LOG, LOG10, MOD, MROUND, MULTINOMIAL, ODD, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SERIESSUM, SIGN, SIN, SINH, SQRT, SQRTPI, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, TAN, TANH, TRUNC
Schedule SCHEDULE
Stats AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, AVERAGE_WEIGHTED, BINOMDIST, CONFIDENCE, CORREL, COUNT, COUNTA, COVAR, CRITBINOM, DEVSQ, EXPONDIST, FDIST, FISHER, FISHERINV, FORECAST, F_DIST, F_DIST_RT, GEOMEAN, HARMEAN, HYPGEOMDIST, INTERCEPT, KURT, LARGE, LOGINV, LOGNORMDIST, MAX, MAXA, MEDIAN, MIN, MINA, MODE, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PEARSON, PERCENTILE, PERCENTRANK, PERCENTRANK_EXC, PERCENTRANK_INC, PERMUT, POISSON, PROB, QUARTILE, RANK, RANK_AVG, RANK_EQ, RSQ, SKEW, SLOPE, SMALL, STANDARDIZE, STDEV, STDEVA, STDEVP, STDEVPA, STEYX, TDIST, TINV, TRIMMEAN, TTEST, T_INV, T_INV_2T, VAR, VARA, VARP, VARPA, WEIBULL, ZTEST
Text CHAR, CLEAN, CODE, CONCATENATE, CONCATENATE, DOLLAR, EXACT, FIND, FIXED, LEFT, LEN, LOWER, MID, PROPER, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, T, TEXT, TRIM, UPPER, VALUE

Grist#

class Record #

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 or rec.Field #

$Field#

Access the field named “Field” of the current record. E.g. $First_Name or rec.First_Name.
$group #

$group#

In a summary view, $group is a special field containing the list of Records that are summarized by the current summary line. E.g. len($group) is the count of those records.

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

class RecordSet #

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

class UserTable #

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.

UserTable.all #

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)

UserTable.lookupOne(self, **field_value_pairs) #

lookupOne#

Returns a Record matching the given field=value arguments. If multiple records match, returns one of them. If none match, returns the special empty record.

For example:

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

UserTable.lookupRecords(self, **field_value_pairs) #

lookupRecords#

Returns the Records from this table that match the given field=value arguments. If sort_by=field is given, sort the results by that field.

For example:

People.lookupRecords(Last_Name="Johnson", sort_by="First_Name")
People.lookupRecords(First_Name="George", Last_Name="Washington")

See RecordSet for useful properties offered by the returned object.

Date#

DATE(year, month, day) #

DATE#

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(start_date, days=0, months=0, years=0, weeks=0) #

DATEADD#

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(start_date, end_date, unit) #

DATEDIF#

Calculates the number of days, months, or years between two dates. Unit indicates the type of information that you want returned:

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(date_string, tz=None) #

DATEVALUE#

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_value) #

DATE_TO_XL#

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

DAY#

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(end_date, start_date) #

DAYS#

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(value, tz=None) #

DTIME#

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(start_date, months) #

EDATE#

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(start_date, months) #

EOMONTH#

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

HOUR#

Same as time.hour.

>>> HOUR(XL_TO_DATE(0.75))
18
>>> HOUR("7/18/2011 7:45")
7
>>> HOUR("4/21/2012")
0

ISOWEEKNUM(date) #

ISOWEEKNUM#

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

MINUTE#

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

MONTH#

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

NOW(tz=None) #

NOW#

Returns the datetime object for the current time.
SECOND(time) #

SECOND#

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#

Returns the date object for the current date.
WEEKDAY(date, return_type=1) #

WEEKDAY#

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.

>>> 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(date, return_type=1) #

WEEKNUM#

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.

>>> 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(value, tz=None) #

XL_TO_DATE#

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

YEAR#

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(start_date, end_date, basis=0) #

YEARFRAC#

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

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 (actual/actual) is likely the correct choice.

See https://en.wikipedia.org/wiki/360-day_calendar for explanation of the US 30/360 and European 30/360 methods. See http://www.dwheeler.com/yearfrac/ for analysis of Excel’s particular implementation.

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'

Info#

CELL(info_type, reference) #

CELL#

Returns the requested information about the specified cell. This is not implemented in Grist

NoteThis function is not currently implemented in Grist.

ISBLANK(value) #

ISBLANK#

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

ISEMAIL#

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

ISERR#

Checks whether a value is an error other than an invalid value. It isn’t implemented in Grist. To check if a cell had an error, use

try:
  ... value ...
except Exception, err:
  ... do something about the error ...

NoteThis function is not currently implemented in Grist.

ISERROR(value) #

ISERROR#

Checks whether a value is an error or an invalid value. It currently only returns True for invalid values, False for valid ones. Errors that cause an exception have to be dealt with using try...except (see also ISERR).

>>> ISERROR(AltText(""))
True
>>> ISERROR(AltText("fail"))
True
>>> ISERROR(float('nan'))
True
>>> [ISERROR(v) for v in [0, None, "", "Test", 17.0]]
[False, False, False, False, False]

ISLOGICAL(value) #

ISLOGICAL#

Checks whether a value is True or False.

>>> ISLOGICAL(True)
True
>>> ISLOGICAL(False)
True
>>> ISLOGICAL(0)
False
>>> ISLOGICAL(None)
False
>>> ISLOGICAL("Test")
False

ISNA(value) #

ISNA#

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

ISNONTEXT#

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

ISNUMBER#

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

ISREF#

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

ISTEXT(value) #

ISTEXT#

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

ISURL#

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

N#

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

TYPE(value) #

TYPE#

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(logical_expression, *logical_expressions) #

AND#

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(logical_expression, value_if_true, value_if_false) #

IF#

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
>>> 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(value, value_if_error=”“) #

IFERROR#

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.

>>> IFERROR(float('nan'), "**NAN**")
'**NAN**'
>>> IFERROR(17.17, "**NAN**")
17.17
>>> IFERROR("Text")
'Text'
>>> IFERROR(AltText("hello"))
''

NOT(logical_expression) #

NOT#

True. Same as not logical_expression.

>>> NOT(123)
False
>>> NOT(0)
True

OR(logical_expression, *logical_expressions) #

OR#

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#

UserTable.lookupOne(self, **field_value_pairs) #

lookupOne#

Returns a Record matching the given field=value arguments. If multiple records match, returns one of them. If none match, returns the special empty record.

For example:

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

UserTable.lookupRecords(self, **field_value_pairs) #

lookupRecords#

Returns the Records from this table that match the given field=value arguments. If sort_by=field is given, sort the results by that field.

For example:

People.lookupRecords(Last_Name="Johnson", sort_by="First_Name")
People.lookupRecords(First_Name="George", Last_Name="Washington")

See RecordSet for useful properties offered by the returned object.

ADDRESS(row, column, absolute_relative_mode, use_a1_notation, sheet) #

ADDRESS#

Returns a cell reference as a string.

NoteThis function is not currently implemented in Grist.

CHOOSE(index, choice1, choice2) #

CHOOSE#

Returns an element from a list of choices based on index.

NoteThis function is not currently implemented in Grist.

COLUMN(cell_reference=None) #

COLUMN#

Returns the column number of a specified cell, with A=1.

NoteThis function is not currently implemented in Grist.

COLUMNS(range) #

COLUMNS#

Returns the number of columns in a specified array or range.

NoteThis function is not currently implemented in Grist.

GETPIVOTDATA(value_name, any_pivot_table_cell, original_column_1, pivot_item_1=None, *args) #

GETPIVOTDATA#

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(search_key, range, index, is_sorted) #

HLOOKUP#

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.

INDEX(reference, row, column) #

INDEX#

Returns the content of a cell, specified by row and column offset.

NoteThis function is not currently implemented in Grist.

INDIRECT(cell_reference_as_string) #

INDIRECT#

Returns a cell reference specified by a string.

NoteThis function is not currently implemented in Grist.

LOOKUP(search_key, search_range_or_search_result_array, result_range=None) #

LOOKUP#

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(search_key, range, search_type) #

MATCH#

Returns the relative position of an item in a range that matches a specified value.

NoteThis function is not currently implemented in Grist.

OFFSET(cell_reference, offset_rows, offset_columns, height, width) #

OFFSET#

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

ROW#

Returns the row number of a specified cell.

NoteThis function is not currently implemented in Grist.

ROWS(range) #

ROWS#

Returns the number of rows in a specified array or range.

NoteThis function is not currently implemented in Grist.

VLOOKUP(table, **field_value_pairs) #

VLOOKUP#

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

Math#

ABS(value) #

ABS#

Returns the absolute value of a number.

>>> ABS(2)
2
>>> ABS(-2)
2
>>> ABS(-4)
4

ACOS(value) #

ACOS#

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

ACOSH#

Returns the inverse hyperbolic cosine of a number.

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

ARABIC(roman_numeral) #

ARABIC#

Computes the value of a Roman numeral.

>>> ARABIC("LVII")
57
>>> ARABIC('mcmxii')
1912

ASIN(value) #

ASIN#

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

ASINH#

Returns the inverse hyperbolic sine of a number.

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

ATAN(value) #

ATAN#

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(x, y) #

ATAN2#

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

ATANH#

Returns the inverse hyperbolic tangent of a number.

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

CEILING(value, factor=1) #

CEILING#

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(n, k) #

COMBIN#

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

COS#

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

COSH#

Returns the hyperbolic cosine of any real number.

>>> round(COSH(4), 6)
27.308233
>>> round(COSH(EXP(1)), 7)
7.6101251

DEGREES(angle) #

DEGREES#

Converts an angle value in radians to degrees.

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

EVEN(value) #

EVEN#

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

EXP#

Returns Euler’s number, e (~2.718) raised to a power.

>>> round(EXP(1), 8)
2.71828183
>>> round(EXP(2), 7)
7.3890561

FACT(value) #

FACT#

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

FACTDOUBLE#

Returns the “double factorial” of a number.

>>> FACTDOUBLE(6)
48
>>> FACTDOUBLE(7)
105
>>> FACTDOUBLE(3)
3
>>> FACTDOUBLE(4)
8

FLOOR(value, factor=1) #

FLOOR#

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(value1, *more_values) #

GCD#

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

INT#

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(value1, *more_values) #

LCM#

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

LN#

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(value, base=10) #

LOG#

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

LOG10#

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(dividend, divisor) #

MOD#

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(value, factor) #

MROUND#

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(value1, *more_values) #

MULTINOMIAL#

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

ODD(value) #

ODD#

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(base, exponent) #

POWER#

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(factor1, *more_factors) #

PRODUCT#

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(dividend, divisor) #

QUOTIENT#

Returns one number divided by another.

>>> QUOTIENT(5, 2)
2
>>> QUOTIENT(4.5, 3.1)
1
>>> QUOTIENT(-10, 3)
-3

RADIANS(angle) #

RADIANS#

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(low, high) #

RANDBETWEEN#

Returns a uniformly random integer between two values, inclusive.
ROMAN(number, form_unused=None) #

ROMAN#

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(value, places=0) #

ROUND#

Rounds a number to a certain number of decimal places according to standard rules.

>>> ROUND(2.15, 1)      # Excel actually gives the more correct 2.2
2.1
>>> ROUND(2.149, 1)
2.1
>>> 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

ROUNDDOWN(value, places=0) #

ROUNDDOWN#

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(value, places=0) #

ROUNDUP#

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(x, n, m, a) #

SERIESSUM#

Given parameters x, n, m, and a, returns the power series sum a_1x^n + a_2x^(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(value) #

SIGN#

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

SIN#

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

SINH#

Returns the hyperbolic sine of any real number.

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

SQRT(value) #

SQRT#

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

SQRTPI#

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(function_code, range1, range2) #

SUBTOTAL#

Returns a subtotal for a vertical range of cells using a specified aggregation function.

NoteThis function is not currently implemented in Grist.

SUM(value1, *more_values) #

SUM#

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(records, criterion, sum_range) #

SUMIF#

Returns a conditional sum across a range.

NoteThis function is not currently implemented in Grist.

SUMIFS(sum_range, criteria_range1, criterion1, *args) #

SUMIFS#

Returns the sum of a range depending on multiple criteria.

NoteThis function is not currently implemented in Grist.

SUMPRODUCT(array1, *more_arrays) #

SUMPRODUCT#

Multiplies corresponding components in the given 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(value1, value2) #

SUMSQ#

Returns the sum of the squares of a series of numbers and/or cells.

NoteThis function is not currently implemented in Grist.

TAN(angle) #

TAN#

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

TANH#

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(value, places=0) #

TRUNC#

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

Schedule#

SCHEDULE(schedule, start=None, count=10, end=None) #

SCHEDULE#

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(value1, value2) #

AVEDEV#

Calculates the average of the magnitudes of deviations of data from a dataset’s mean.

NoteThis function is not currently implemented in Grist.

AVERAGE(value, *more_values) #

AVERAGE#

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(value, *more_values) #

AVERAGEA#

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(criteria_range, criterion, average_range=None) #

AVERAGEIF#

Returns the average of a range depending on criteria.

NoteThis function is not currently implemented in Grist.

AVERAGEIFS(average_range, criteria_range1, criterion1, *args) #

AVERAGEIFS#

Returns the average of a range depending on multiple criteria.

NoteThis function is not currently implemented in Grist.

AVERAGE_WEIGHTED(pairs) #

AVERAGE_WEIGHTED#

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(num_successes, num_trials, prob_success, cumulative) #

BINOMDIST#

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(alpha, standard_deviation, pop_size) #

CONFIDENCE#

Calculates the width of half the confidence interval for a normal distribution.

NoteThis function is not currently implemented in Grist.

CORREL(data_y, data_x) #

CORREL#

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

NoteThis function is not currently implemented in Grist.

COUNT(value, *more_values) #

COUNT#

Returns the count of numerical values 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.

>>> COUNT([2, -1.0, 11])
3
>>> COUNT([2, -1, 11, "Hello"])
3
>>> COUNT([2, -1, "Hello", DATE(2015,1,1)], True, [False, "123", "", 11.5])
3
>>> COUNT(False, True)
0

COUNTA(value, *more_values) #

COUNTA#

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(data_y, data_x) #

COVAR#

Calculates the covariance of a dataset.

NoteThis function is not currently implemented in Grist.

CRITBINOM(num_trials, prob_success, target_prob) #

CRITBINOM#

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(value1, value2) #

DEVSQ#

Calculates the sum of squares of deviations based on a sample.

NoteThis function is not currently implemented in Grist.

EXPONDIST(x, lambda_, cumulative) #

EXPONDIST#

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(x, degrees_freedom1, degrees_freedom2) #

FDIST#

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

FISHER#

Returns the Fisher transformation of a specified value.

NoteThis function is not currently implemented in Grist.

FISHERINV(value) #

FISHERINV#

Returns the inverse Fisher transformation of a specified value.

NoteThis function is not currently implemented in Grist.

FORECAST(x, data_y, data_x) #

FORECAST#

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(x, degrees_freedom1, degrees_freedom2, cumulative) #

F_DIST#

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(x, degrees_freedom1, degrees_freedom2) #

F_DIST_RT#

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(value1, value2) #

GEOMEAN#

Calculates the geometric mean of a dataset.

NoteThis function is not currently implemented in Grist.

HARMEAN(value1, value2) #

HARMEAN#

Calculates the harmonic mean of a dataset.

NoteThis function is not currently implemented in Grist.

HYPGEOMDIST(num_successes, num_draws, successes_in_pop, pop_size) #

HYPGEOMDIST#

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(data_y, data_x) #

INTERCEPT#

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(value1, value2) #

KURT#

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(data, n) #

LARGE#

Returns the nth largest element from a data set, where n is user-defined.

NoteThis function is not currently implemented in Grist.

LOGINV(x, mean, standard_deviation) #

LOGINV#

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(x, mean, standard_deviation) #

LOGNORMDIST#

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(value, *more_values) #

MAX#

Returns the maximum 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. Returns 0 if the arguments contain no numbers.

>>> MAX([2, -1.5, 11.5])
11.5
>>> MAX([2, -1.5, "Hello", DATE(2015, 1, 1)], True, [False, "123", "", 11.5])
11.5
>>> MAX(True, -123)
-123
>>> MAX("123", -123)
-123
>>> MAX("Hello", "123", DATE(2015, 1, 1))
0

MAXA(value, *more_values) #

MAXA#

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(value, *more_values) #

MEDIAN#

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(xrange(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(value, *more_values) #

MIN#

Returns the minimum 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. Returns 0 if the arguments contain no numbers.

>>> MIN([2, -1.5, 11.5])
-1.5
>>> MIN([2, -1.5, "Hello", DATE(2015, 1, 1)], True, [False, "123", "", 11.5])
-1.5
>>> MIN(True, 123)
123
>>> MIN("-123", 123)
123
>>> MIN("Hello", "123", DATE(2015, 1, 1))
0

MINA(value, *more_values) #

MINA#

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(value1, value2) #

MODE#

Returns the most commonly occurring value in a dataset.

NoteThis function is not currently implemented in Grist.

NEGBINOMDIST(num_failures, num_successes, prob_success) #

NEGBINOMDIST#

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(x, mean, standard_deviation, cumulative) #

NORMDIST#

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(x, mean, standard_deviation) #

NORMINV#

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

NORMSDIST#

Returns the value of the standard normal cumulative distribution function for a specified value.

NoteThis function is not currently implemented in Grist.

NORMSINV(x) #

NORMSINV#

Returns the value of the inverse standard normal distribution function for a specified value.

NoteThis function is not currently implemented in Grist.

PEARSON(data_y, data_x) #

PEARSON#

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

NoteThis function is not currently implemented in Grist.

PERCENTILE(data, percentile) #

PERCENTILE#

Returns the value at a given percentile of a dataset.

NoteThis function is not currently implemented in Grist.

PERCENTRANK(data, value, significant_digits=None) #

PERCENTRANK#

Returns the percentage rank (percentile) of a specified value in a dataset.

NoteThis function is not currently implemented in Grist.

PERCENTRANK_EXC(data, value, significant_digits=None) #

PERCENTRANK_EXC#

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(data, value, significant_digits=None) #

PERCENTRANK_INC#

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(n, k) #

PERMUT#

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(x, mean, cumulative) #

POISSON#

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(data, probabilities, low_limit, high_limit=None) #

PROB#

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(data, quartile_number) #

QUARTILE#

Returns a value nearest to a specified quartile of a dataset.

NoteThis function is not currently implemented in Grist.

RANK(value, data, is_ascending=None) #

RANK#

Returns the rank of a specified value in a dataset.

NoteThis function is not currently implemented in Grist.

RANK_AVG(value, data, is_ascending=None) #

RANK_AVG#

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(value, data, is_ascending=None) #

RANK_EQ#

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(data_y, data_x) #

RSQ#

Calculates the square of r, the Pearson product-moment correlation coefficient of a dataset.

NoteThis function is not currently implemented in Grist.

SKEW(value1, value2) #

SKEW#

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(data_y, data_x) #

SLOPE#

Calculates the slope of the line resulting from linear regression of a dataset.

NoteThis function is not currently implemented in Grist.

SMALL(data, n) #

SMALL#

Returns the nth smallest element from a data set, where n is user-defined.

NoteThis function is not currently implemented in Grist.

STANDARDIZE(value, mean, standard_deviation) #

STANDARDIZE#

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(value, *more_values) #

STDEV#

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(value, *more_values) #

STDEVA#

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(value, *more_values) #

STDEVP#

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(value, *more_values) #

STDEVPA#

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(data_y, data_x) #

STEYX#

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(x, degrees_freedom, tails) #

TDIST#

Calculates the probability for Student’s t-distribution with a given input (x).

NoteThis function is not currently implemented in Grist.

TINV(probability, degrees_freedom) #

TINV#

Calculates the inverse of the two-tailed TDIST function.

NoteThis function is not currently implemented in Grist.

TRIMMEAN(data, exclude_proportion) #

TRIMMEAN#

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(range1, range2, tails, type) #

TTEST#

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(probability, degrees_freedom) #

T_INV#

Calculates the negative inverse of the one-tailed TDIST function.

NoteThis function is not currently implemented in Grist.

T_INV_2T(probability, degrees_freedom) #

T_INV_2T#

Calculates the inverse of the two-tailed TDIST function.

NoteThis function is not currently implemented in Grist.

VAR(value1, value2) #

VAR#

Calculates the variance based on a sample.

NoteThis function is not currently implemented in Grist.

VARA(value1, value2) #

VARA#

Calculates an estimate of variance based on a sample, setting text to the value 0.

NoteThis function is not currently implemented in Grist.

VARP(value1, value2) #

VARP#

Calculates the variance based on an entire population.

NoteThis function is not currently implemented in Grist.

VARPA(value1, value2) #

VARPA#

Calculates the variance based on an entire population, setting text to the value 0.

NoteThis function is not currently implemented in Grist.

WEIBULL(x, shape, scale, cumulative) #

WEIBULL#

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(data, value, standard_deviation) #

ZTEST#

Returns the two-tailed P-value of a Z-test with standard distribution.

NoteThis function is not currently implemented in Grist.

Text#

CHAR(table_number) #

CHAR#

Convert a number into a character according to the current Unicode table. Same as unichr(number).

>>> CHAR(65)
u'A'
>>> CHAR(33)
u'!'

CLEAN(text) #

CLEAN#

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

CODE#

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

CONCATENATE(string, *more_strings) #

CONCATENATE#

Joins together any number of text strings into one string. Also available under the name CONCAT. Same as 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'
>>> CONCAT(0, "abc")
u'0abc'

CONCATENATE(string, *more_strings) #

CONCATENATE#

Joins together any number of text strings into one string. Also available under the name CONCAT. Same as 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'
>>> CONCAT(0, "abc")
u'0abc'

DOLLAR(number, decimals=2) #

DOLLAR#

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(string1, string2) #

EXACT#

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_text, within_text, start_num=1) #

FIND#

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(number, decimals=2, no_commas=False) #

FIXED#

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(string, num_chars=1) #

LEFT#

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

LEN#

Returns the number of characters in a text string. Same as len(text).

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

LOWER(text) #

LOWER#

Converts a specified string to lowercase. Same as text.lower().

>>> LOWER("E. E. Cummings")
'e. e. cummings'
>>> LOWER("Apt. 2B")
'apt. 2b'

MID(text, start_num, num_chars) #

MID#

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

PROPER(text) #

PROPER#

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(text, regular_expression) #

REGEXEXTRACT#

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(text, regular_expression) #

REGEXMATCH#

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(text, regular_expression, replacement) #

REGEXREPLACE#

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(old_text, start_num, num_chars, new_text) #

REPLACE#

Replaces part of a text string with a different text string. Start_num 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: start_num invalid

REPT(text, number_times) #

REPT#

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

SUBSTITUTE(text, old_text, new_text, instance_num=None) #

SUBSTITUTE#

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")
'Cost Data'
>>> SUBSTITUTE("Quarter 1, 2008", "1", "2", 1)
'Quarter 2, 2008'
>>> SUBSTITUTE("Quarter 1, 2011", "1", "2", 3)
'Quarter 1, 2012'

T(value) #

T#

Returns value if value is text, or the empty string when value is not text.

>>> T('Text')
'Text'
>>> T(826)
''
>>> T('826')
'826'
>>> T(False)
''
>>> T('100 points')
'100 points'
>>> T(AltText('Text'))
'Text'
>>> T(float('nan'))
''

TEXT(number, format_type) #

TEXT#

Converts a number into text according to a specified format. It is not yet implemented in Grist.

NoteThis function is not currently implemented in Grist.

TRIM(text) #

TRIM#

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

UPPER#

Converts a specified string to uppercase. Same as text.lower().

>>> UPPER("e. e. cummings")
'E. E. CUMMINGS'
>>> UPPER("Apt. 2B")
'APT. 2B'

VALUE(text) #

VALUE#

Converts a string in accepted date, time or number formats into a number or date.

>>> VALUE("$1,000")
1000
>>> 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