LiveDataset

1. Help Center
2. Advanced
3. • LiveDataset Overview
   • Using LiveDataset
4. Using Formulas and Functions

Using Formulas and Functions

updated 22 Nov 2018

A formula can be used in a calculated column and will be automatically calculated for every row in the dataset.

To enter a formula, the column type must be set to Formula.

When a formula refers to a column, use "col" and the column ID number. In the example above the column ID for Sales is 2, so in the formula we use "col2".

Note: Whenever column is used in a formula, a constant value can also be used.

A formula can be as simple as

• =100
• ="Hello World"
• = col2 + col3

Formulas can contain numbers, text, and functions including

• Operators
• Math functions
• Text functions
• Date functions
• List and multiselect functions
• Logical and lookup functions

Formula Syntax

• A formula always starts with "=".
• Text must be enclosed in double quotes "like this".
• Columns are referenced using "col" and the column ID number.

• Arguments for a formula are encased in round brackets ( ) and separated by commas.

  e.g. =ROUND(col2,1) will take values from the column that has ID 2 and round them to 1 decimal place.

• The text operator "+" can be used to concatenate text values in columns and text constants

  Example: if col1 contains "Live" then the result of formula =col1+"Dataset" is "LiveDataset"

Simple math operators can be used

Operator	Description
+	Add
-	Subtract
*	Multiply
/	Divide
()	Use to specify precedence

Precedence examples:

• =100+400/200 following normal mathematical convention, division takes precedence and is calculated first
  =100+2 =102

• =(100+400)/200 as ( ) specifies the addition should take precedence it is calculated before dividing by 200
  =500/200 =2.5

Math Functions

Math functions should be used on columns containing numbers.

Function	Example
ABS(column) Absolute values of numbers in the column.	Formula: =ABS(col1) col1 result -21.5 21.5 17.31 17.31 44 44
CEILING(column) Rounds up numbers in the column.	Formula: =CEILING(col1) col1 result -21.5 -22 17.31 18 44 44
FLOOR(column) Rounds down numbers in the column.	Formula: =FLOOR(col1) col1 result -21.5 -21 17.31 17 44 44
MAX(column, column) The maximum value from the columns.	Formula: =MAX(col1,col2) col1 col2 result -21.5 79 79 17.31 -8.01 17.31 44 49.45 49.45
MIN(column, column) The minimum value from the columns.	Formula: =MIN(col1,col2) col1 col2 result -21.5 79 -21.5 17.31 -8.01 -8.01 44 49.45 44
ROUND(column) Rounds numbers in the column to the nearest integer.	Formula: =ROUND(col1) col1 result -21.5 -22 17.31 17 44 44
ROUND(column,[places]) Rounds numbers in the column to the number of places specified.	Formula: =ROUND(col1,1) col1 result -21.5 -21.5 17.31 17.3 44 44
VALUE(column) Extracts the numerical value from text.	Formula: =VALUE(col1) col1 result £1000 1000 -$345,832 -345832 44 Days 44

Text Functions

Text functions should be used on columns containing text.

Function	Example
LEFT(column, length) The first text characters from text values in the specified column. length specifies the number of characters.	Formula: =LEFT(col1,4) col1 result LiveDataset Live Spreadsheet Spre Database Data
MID(column, start, length) Returns text characters from the specified column. start specifies the starting point. length specifies the number of characters.	Formula: =MID(col1,5,4) col1 result LiveDataset Data Spreadsheet adsh Database base
RIGHT(column, length) The last text characters from the end of text values in the specified column. length specifies the number of characters.	Formula: =RIGHT(col1,3) col1 result LiveDataset set Spreadsheet eet Database ase
LEN(column) Calculates the number of characters in the specified column.	Formula: =LEN(col1) col1 result LiveDataset 11 Database 8   0
TRIM(column) Removes leading and trailing spaces from a specified column.	Formula: =TRIM(col1) col1 result  LiveDataset  LiveDataset Spreadsheet  Spreadsheet  Hello World Hello World
SEARCH(column, column, [startnum]) The index of the first column text inside the second column text. startnum specifies the start position in the text to search. Optional, defaults to 1 if not specifed.	Formula: =SEARCH(col1, col2) col1 col2 result Data LiveDataset 5 read Spreadsheet 3 E Database 8
TEXT(column) Converts numbers into text.	Formula: =TEXT(col1) + col2 col1 col2 result 2346 31 234631 23 0 230 480 29 48029
SUBSTITUTE(text, to_replace, replace_with, [nth]) text, to_replace, replace_with are either fixed text, or a text column. nth is an integer. Replaces (substitutes) occurrences of what is specified in the to_replace parameter that is found within the text parameter with what is specified in the replace_with parameter. If nth is not present, it will replace all occurrences, if it is present, it will only replace the nth occurrence, where the first occurance is 1.	Formula: =SUBSTITUTE(col1, col2, col3) col1 col2 col3 result abcb b d adcd Formula: =SUBSTITUTE(col1, col2, col3, col4) col1 col2 col3 col4 result abcb b d 1 adcb abcb b d 2 abcd
UPPER(column) Converts text to uppercase.	Formula: =UPPER(col1) col1 result LiveDataset LIVEDATASET Spreadsheet SPREADSHEET HELLO world HELLO WORLD
LOWER(column) Converts text to lowercase.	Formula: =LOWER(col1) col1 result LiveDataset livedataset Spreadsheet spreadsheet HELLO world hello world

Date Functions

Date functions should be used on columns containing dates.

Function	Example
DATEDIF(column, column, value) The number of seconds, minutes, hours, days, months or years between dates. Second column dates are 'subtracted' from first column. value must be one of: "S" for seconds "Mi" for minutes "H" for hours "D" for days "M" for months "Y" for years	Formula: =DATEDIF(col1,col2,"M") col1 col2 result 2016-12-31 2017-12-31 -12 2016-02-29 2012-12-31 48 2000-10-29 2000-12-29 -2 Formula: =DATEDIF(col1,col2,"H") col1 col2 result 2016-12-31 05:14 2016-12-31 10:36 -5 2016-02-29 17:52 2016-02-28 15:42 26 2000-10-29 11:39 2000-10-30 03:28 -15
MINUTE(column) The minute of the hour as a number.	Formula: =MINUTE(col1) col1 result 2016-12-31 05:14 14 2016-02-29 17:52 52 2000-10-29 11:39 39
HOUR(column) The hour of the day as a number. The value will be displayed in UTC time zone for consistency across users.	Formula: =HOUR(col1) col1 result 2016-12-31 05:14 5 2016-02-29 17:52 17 2000-10-29 11:39 11
DAY(column) The day of the month as a number.	Formula: =DAY(col1) col1 result 2016-12-31 31 2016-02-29 29 2000-10-29 29
DAYADD(column, value or column) Adds a number of days to a date. Use negative values to subtract days.	Formula: =DAYADD(col1,col2) col1 col2 result 2016-12-31 31 2017-01-31 2016-02-29 0 2016-02-29 2000-10-29 -29 2000-09-30
MONTHADD(column, value or column) Adds a number of months to a date. Use negative values to subtract months.	Formula: =MONTHADD(col1,col2) col1 col2 result 2016-12-31 2 2017-02-28 2016-02-29 0 2016-02-29 2021-10-31 -4 2021-06-30
TIMEADD(column, value or column, value or column) Adds a number of hours and minutes to a date. Use negative values to subtract days.	Formula: =TIMEADD(col1,col2,col3) col1 col2 col3 result 2016-12-31 05:14 7 20 2016-12-31 12:34 2016-02-29 17:52 0 7 2016-02-29 17:59 2000-10-29 11:39 -11 0 2000-10-29 00:39
EOMONTH(column, value or column) Returns the last day of the month, after adding or removing a month offset. Use negative values to subtract months.	Formula: =EOMONTH(col1,col2) col1 col2 result 2016-12-06 2 2017-02-28 2016-02-02 0 2016-02-29 2021-10-25 -4 2021-06-30
MONTH(column) The month for the dates in the column as a number.	Formula: =MONTH(col1) col1 result 2016-12-31 12 2016-02-29 2 2000-10-29 10
WEEKDAY(column) The day of the week for the dates in the column. result a number from 1 to 7.	Formula: =WEEKDAY(col1) col1 result 2016-12-31 7 2016-02-29 2 2000-10-29 1
WEEKNUM(column) The number of the week for the dates in the column. result a number from 1 to 53.	Formula: =WEEKNUM(col1) col1 result 2016-12-31 53 2016-02-29 10 2000-10-29 45
YEAR(column) The year for the dates in the column as a number.	Formula: =YEAR(col1) col1 result 2016-12-31 2016 2016-02-29 2016 2000-10-29 2000

List and Multiselect Functions

List and multiselect functions should be used on columns containing lists or multiple values.

Function	Example
COUNTMATCHES(column, value...) The number of values in the column that match the list provided.	Formula: =COUNTMATCHES(col1,"a","b","c") col1 result a,b 2 c,d 1 a,b,c,d 3 d,e 0
SIZE(column) Number of values in the column.	Formula: =SIZE(col1) col1 result a,b 2 c 1 a,b,c,d 4 0

Logical and Lookup Functions

Function	Example
IF(condition, true, false) Tests a condition and returns value from true or false. condition must include one of: == for equals. != for not equals. > for greater than. >= for greater than or equals. < for less than. <= for less than or equals. ISEMPTY(column) to check for empty values. ISNUMBER(column) to check if a value is a number. MATCHESANY(column, value…) to check if any of the values in the column are in the list provided. MATCHESALL(column, value…) to check if all of the values in the column are in the list provided. ROLE(dataset.group) to check the current user’s permissions. See below for details. conditions can be combined using: AND to test for 2 conditions to both be true. OR to test for at least one of the conditions to be true. XOR to test for exactly one condition to be true. NOT to test for the condition to NOT be true. true is the value to return if condition is met. false is the value to return if condition is not met. TIP You can compare dates, numbers, and text.	Formula: =IF(col1>100,col1/100,0) col1 result 250 2.5   89 0 Formula: =IF(ISEMPTY(col1),"??",col1) col1 result 250 250   ?? 89 89 Formula: =IF(col1<100 AND NOT ISEMPTY(col1),"ok","") col1 result 250   89 ok Formula: =IF(MATCHESANY(col1,"a","b","c"), "true, "false") col1 result a,b true c,d true a,b,c,d true d,e false Formula: =IF(MATCHESALL(col1,"a","b","c"), "true, "false") col1 result a,b true a,b,c true c,d false a,b,c,d false d,e false
LINK(url) A hyperlink to the url. url can be constructed using other functions.	Formula: =LINK("http://report.com?id="+col1) col1 result 250 http://report.com?id=250   http://report.com?id= 89 http://report.com?id=89
VLOOKUP(dataset.column, criteria) Returns values from another dataset where criteria match between the current dataset and the other dataset. dataset.column is specified as "ds"dataset id."col"column id e.g. use ds111.col2 for column 2 in dataset 111. criteria is specified as dataset.column=column to find matching values between a column in the other dataset with a column in the current dataset. You can include multiple criteria. TIP Be careful to avoid creating a circular reference.	Other dataset (dataset id=111) col1 col2 France €EUR Germany €EUR UK £GBP USA $USD Current dataset Formula: =VLOOKUP(ds111.col2,ds111.col1=col1) col1 col2 USA $USD France €EUR USA $USD In this example, dataset 111 holds currencies for countries. The lookup formula matches col1 in the current dataset with col1 in dataset 111 and returns the value of col2 in dataset 111 for that row. So "USA" in current dataset is looked up in currencies dataset and returns "$USD". This is similar to performing a join between database tables. TIP A quick way to find a dataset id is to look at its url.
ROLE(dataset.group) Checks if the current user is part of a group within a people dataset. dataset.group is specified using the id of the dataset and group: "ds"dataset id."view_"view id e.g. use ds111.view_abc for group abc in people dataset 111. This function will also check the if the user has manually excluded the group from their roles.	Formula: =ROLE(ds111.view_abc) User in group User has role selected result true true true true false false false true false false false false TIP A quick way to find the group id to look at its url in the people dataset.