# Built-in Functions

These built-in functions can be used inside text elements by enclosing them in square brackets beginning with an equals sign, which signals that the expression is a formula.

For example:

``````Grand total: [=SUM(total)]
``````

The formula above uses the built-in SUM function to add up the values in the `total` data field.

## Aggregates

### SUM

``````=SUM(fieldName)
``````

Returns the sum of the values in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

### COUNT

``````=COUNT(fieldName)
``````

Returns the count of non-null values, including duplicates, in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

If `fieldName` is a calculation, returns the number of rows for which the calculation returns `true`.

### COUNTDISTINCT

``````=COUNTDISTINCT(fieldName)
``````

Works the same way as `COUNT`, but only one occurrence of each distinct value will be counted. For example, the `COUNTDISTINCT` of a list (A, B, A, B, C, C, C) is 3 (A, B, and C).

### AVERAGE

``````=AVERAGE(fieldName)
``````

Returns the average of the values in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

### MEDIAN

``````=MEDIAN(fieldName)
``````

Returns the median of the values in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

### MIN

``````=MIN(fieldName)
``````

Returns the minimum of the values in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

### MAX

``````=MAX(fieldName)
``````

Returns the maximum of the values in the `fieldName` field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

## Other Functions

### DATE

``````=DATE()
=DATE(dateStr)
=DATE(dateStr, formatStr)
``````

If no arguments are provided, returns today's date as a JavaScript date. If a `dateStr` argument is provided, parses the string to return the corresponding date. By default, the date format 'YYYY-MM-DD' is used for parsing. If the date string is in a different format, the format string may be provided as a second argument.

### Examples

``````=DATE()
// Returns today's date as a JavaScript date object
``````
``````=DATE('2017-02-21')
// Returns the given date, using the default format YYYY-MM-DD
``````
``````=DATE('2/21/2017', 'M/D/YYYY')
// Returns the given date, parsed using the specified custom format
``````

### FORMAT

``````=FORMAT(value, formatStr)
``````

Formats the given number or date into a string, using the specified format string.

### Examples

``````=FORMAT(DATE(), 'M/D/YY')
// Returns e.g. "2/21/17"
``````
``````=FORMAT(1234.5678, '\$#,##0.00')
// Returns "\$1,234.57"
``````

### LOOKUP

``````=LOOKUP(valueToFind, dataSourceId, fieldNameToSearch, fieldNameToReturn)
``````

Find the row containing a specific value in a specific field in a data source, and return the value from another field in the same row.

### Examples

Given a data source with id `main_table` containing the following data:

id name price
2 Apples 12.99
3 Oranges 15.95
4 Bananas 87.40

Then the following expression:

``````=LOOKUP(3, 'main_table', 'id', 'price')
``````

returns:

``````15.95
``````

The formula means: find the row that has value `3` in the `id` field of the `main_table` data source, and return the value from the `price` field of the same row.