List of formulas and parameters
| Formula | Formula description | Summary | Example | Available? |
|---|---|---|---|---|
Include | INCLUDE(substring, string) | Returns TRUE if the string contains the substring, FALSE otherwise | INCLUDE("string", "is the word string in this sentence?) | Yes |
Timestamp_to_date | TIMESTAMPTODATE(timestamp) | Returns a date with the format "MM-DD-YYYY". Takes a UNIX timestamp (in seconds) as argument. | TIMESTAMPTODATE(308966400) | Yes |
Year | YEAR(DATE) | Returns the year of a specific date | YEAR("10/17/1979") | Yes |
Month | MONTH(DATE) | Returns the month of a specific date, in numeric format | MONTH("10/17/1979") | Yes |
Iferror | IFERROR(value, [value_if_error]) | Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent. | IFERROR(A1/A2, "Error") | Yes |
Ceiling | CEILING(value, factor) | Rounds a number down. Factor is optional. | FLOOR(23.25, 0.1) will return 23.3
FLOOR(23.25, 1) will return 24 | Yes |
Floor | FLOOR(value, factor) | Rounds a number down. Factor is optional. | FLOOR(23.25, 0.1) will return 23.2
FLOOR(23.25, 1) will return 23 | Yes |
Weeknum | WEEKNUM(DATE) | Returns the week number | WEEKNUM("10/17/1979") | Yes |
Average | AVERAGE(value1, [value2, …]) or AVERAGE(#column_name) | Returns the average of a series of numbers and/or cells or of a column | AVERAGE(A2:A100, 101) or AVERAGE(#price) | Yes |
Date | DATE(year, month, day) | Converts a provided year, month, and day into a date. | DATE(1979, 10, 17) | Yes |
Round | ROUND(value, [places]) | Rounds a number to a certain number of decimal places, always rounding down to the next valid increment. | ROUND(99.44, 1) | Yes |
Roundup | ROUNDUP(value, [places]) | Rounds a number to a certain number of decimal places, always rounding up to the next valid increment. | ROUNDUP(99.44, 1) | Yes |
Rounddown | ROUNDDOWN(value, [places]) | Rounds a number to a certain number of decimal places according to standard rules. | ROUNDDOWN(99.44, 1) | Yes |
PMT | PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) | Calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate. | PMT(1%, 360, 100000, 0, 0) | Yes |
Not | NOT(logical_expression) | Returns the opposite of a logical value - NOT(TRUE) returns FALSE; NOT(FALSE) returns TRUE. | NOT(TRUE) | Yes |
Int | INT(value) | Rounds a number down to the nearest integer that is less than or equal to it. | INT(24.8) | Yes |
And | AND(logical_expression1, [logical_expression2, …]) | Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. | AND(A1=1, A2=2) | Yes |
Or | OR(logical_expression1, [logical_expression2, …]) | Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. | OR(A1=1, A2=2) | Yes |
If | IF(logical_expression, value_if_true, value_if_false) | Returns one value if a logical expression is TRUE and another if it is FALSE. | IF(42>9, “all good”, “something is wrong in the matrix”) | Yes |
Min | MIN(value1, [value2, …]) or MIN(#column_name) | Returns the minimum value in a numeric dataset (can be a series of cells, range, or a table column) | MIN(A3, 5) or MIN(#selling_price) | Yes |
Max | MAX(value1, [value2, …]) or MAX(#column_name) | Returns the maximum value in a numeric dataset (can be a series of cells, range, or a table column) | MAX(A3, 42) or MAX(#selling_price) | Yes |
Networkdays | NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of net working days between two provided dates. | NETWORKDAYS(A1, B1, C1:C10) | Yes |
True | TRUE() | Returns the logical value TRUE | TRUE() | Yes |
False | FALSE() | Returns the logical value FALSE | FALSE() | Yes |
Today | TODAY() | Returns the current date. | TODAY() | Yes |
Sum | SUM(value1, [value2, …]) or SUM(#column_name) | Returns the sum of a series of numbers and/or cells or of a column | SUM(A2:A100, 101) or SUM(#price) | Yes |
Lookup | LOOKUP(#column_id_current_table, #column_id_second_table, #column_to_lookup) | Searches down a column for a key and returns the value of a specified cell in the row found.
Cannot be nested in another function. Nor can a function be nested in a lookup | LOOKUP(#customerid, ‘Sheet2!’#id, ‘Sheet2!’#name) | Yes |
Countuniqueifs | COUNTUNIQUEIFS(#column_name, #criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …]) | Counts the number of unique values in a column, filtered by a set of criteria applied to additional columns. | COUNTUNIQUEIFS(#user_name, #country, "France", #signup_date, ">"&E3) | Yes |
Countunique | COUNTUNIQUE(#column_name) | Counts the number of unique values in a column. | COUNTUNIQUE(#user_name) | Yes |
Countif | COUNTIF(#criteria_column, criterion) | Returns the count of a column depending on one criteria. | COUNTIF(#product_category, "Phones") | Yes |
Sumif | SUMIF(#sum_column, #criteria_column, criterion) | Returns the sum of a column depending on one criteria. | SUMIF(#price, #product_category, "Phones") | Yes |
Averageifs | AVERAGEIFS(#average_column, #criteria_column, criterion, ...) | Returns the average of a column depending on multiple criteria. | AVERAGEIFS(#selling_price, #product_category, "phones", #country, "France") | Yes |
Counta | COUNTA(value1, [value2, …]) or COUNTA(#column_name) | Returns the number of values in a series of numbers and/or cells or of a column | COUNTA(A2:A100, A101) or SUM(#id) | Yes |
Sumifs | SUMIFS(#sum_column, #criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …]) | Returns the sum of a column depending on multiple criteria. | SUMIFS(#price, #product_category, "Phones", #booking_date, ">"&E3) | Yes |
Countifs | COUNTIFS(#criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …]) | Returns the count of a column depending on multiple criteria. | COUNTIFS(#product_category, "Phones", #booking_date, ">"&E3) | Yes |