1---2title: "Functions"3sidebar_position: 34---56# Functions78<!-- Generated from the engine's function table by TestFunctionsDoc; do not edit. -->910012 has 150 functions. They follow Google Sheets' names, arguments and11semantics; `[brackets]` mark optional arguments. Function names are12case-insensitive, and 1-2-3's `@SUM(A1..A5)` spelling still works.1314Aliases: `AVG` for `AVERAGE`.1516## Everyday1718| Function | Description |19|---|---|20| `ABS(value)` | Absolute value |21| `AND(logical1, [logical2, ...])` | TRUE if all arguments are true |22| `AVERAGE(value1, [value2, ...])` | Average of numbers, ignoring text |23| `COUNT(value1, [value2, ...])` | Count of numeric values, skipping errors |24| `COUNTA(value1, [value2, ...])` | Count of non-empty values, errors included |25| `FALSE()` | The logical value FALSE |26| `IF(condition, value_if_true, [value_if_false])` | Choose a value by a condition |27| `IFERROR(value, [value_if_error])` | A fallback when a value is an error; blank without one |28| `INT(value)` | Round down to the nearest integer |29| `MAX(value1, [value2, ...])` | Largest number |30| `MIN(value1, [value2, ...])` | Smallest number |31| `MOD(dividend, divisor)` | Remainder, with the sign of the divisor: MOD(0.3, 0.1) is 0.1 as in Excel, where Sheets gives -5.55E-17 |32| `NA()` | The #N/A error |33| `NOT(logical)` | The opposite of a logical value |34| `OR(logical1, [logical2, ...])` | TRUE if any argument is true |35| `PI()` | The number pi |36| `ROUND(value, [places])` | Round to a number of decimal places, halves away from zero, on the digits a cell shows: ROUND(1.5, 14) is 1.5 |37| `SQRT(value)` | Square root |38| `SUM(value1, [value2, ...])` | Sum of numbers |39| `TRUE()` | The logical value TRUE |4041## Math4243| Function | Description |44|---|---|45| `CEILING(value, [factor])` | Round up to a multiple of factor |46| `EXP(exponent)` | e raised to a power |47| `FLOOR(value, [factor])` | Round down to a multiple of factor |48| `LN(value)` | Natural logarithm |49| `LOG(value, [base])` | Logarithm, base 10 by default |50| `LOG10(value)` | Base-10 logarithm |51| `POWER(base, exponent)` | A number raised to a power, odd roots of negatives included |52| `PRODUCT(factor1, [factor2, ...])` | Product of numbers |53| `QUOTIENT(dividend, divisor)` | Integer part of a division |54| `RAND()` | A random number from 0 up to 1, new on every change (recalculates on every change) |55| `RANDBETWEEN(low, high)` | A random integer between two values, new on every change (recalculates on every change) |56| `ROUNDDOWN(value, [places])` | Round toward zero |57| `ROUNDUP(value, [places])` | Round away from zero |58| `SIGN(value)` | 1, 0 or -1 by the sign of a number |59| `SUMIF(range, criterion, [sum_range])` | Sum of the cells that meet a condition |60| `SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])` | Sum of the cells that meet every condition |61| `SUMPRODUCT(array1, [array2, ...])` | Sum of the products of matching entries, TRUE counting as 1 |62| `TRUNC(value, [places])` | Drop decimals past a number of places |6364## Statistics6566| Function | Description |67|---|---|68| `AVERAGEIF(criteria_range, criterion, [average_range])` | Average of the cells that meet a condition |69| `AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])` | Average of the cells that meet every condition |70| `COUNTBLANK(range)` | Count of empty cells, including empty text |71| `COUNTIF(range, criterion)` | Count of the cells that meet a condition |72| `COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])` | Count of the cells that meet every condition |73| `LARGE(data, n)` | The nth largest number |74| `MEDIAN(value1, [value2, ...])` | Middle value of numbers |75| `MODE(value1, [value2, ...])` | Most common number (the first, on a tie) |76| `MODE.SNGL(value1, [value2, ...])` | Most common number, the first on a tie (MODE) |77| `PERCENTILE(data, percentile)` | The value at a percentile from 0 to 1, interpolated |78| `PERCENTILE.EXC(data, percentile)` | The value at a percentile strictly between 0 and 1, interpolated |79| `PERCENTILE.INC(data, percentile)` | The value at a percentile from 0 to 1, interpolated (PERCENTILE) |80| `QUARTILE(data, quartile)` | The minimum (0), a quartile (1 to 3) or the maximum (4) |81| `QUARTILE.EXC(data, quartile)` | A quartile from 1 to 3, between the numbers rather than at them |82| `QUARTILE.INC(data, quartile)` | The minimum (0), a quartile (1 to 3) or the maximum (4) (QUARTILE) |83| `RANK(value, data, [is_ascending])` | Rank of a number among others, largest first by default |84| `RANK.EQ(value, data, [is_ascending])` | Rank of a number among others, largest first by default (RANK) |85| `SMALL(data, n)` | The nth smallest number |86| `STDEV(value1, [value2, ...])` | Standard deviation of a sample |87| `STDEVP(value1, [value2, ...])` | Standard deviation of a whole population |88| `VAR(value1, [value2, ...])` | Variance of a sample |89| `VARP(value1, [value2, ...])` | Variance of a whole population |9091## Logic and information9293| Function | Description |94|---|---|95| `IFNA(value, value_if_na)` | A fallback when a value is #N/A |96| `IFS(condition1, value1, [condition2, value2, ...])` | The value of the first true condition |97| `ISBLANK(value)` | TRUE if a cell is empty |98| `ISERR(value)` | TRUE if a value is an error other than #N/A |99| `ISERROR(value)` | TRUE if a value is any error |100| `ISLOGICAL(value)` | TRUE if a value is TRUE or FALSE |101| `ISNA(value)` | TRUE if a value is #N/A |102| `ISNUMBER(value)` | TRUE if a value is a number |103| `ISTEXT(value)` | TRUE if a value is text |104| `SWITCH(expression, case1, value1, [case2, value2, ...], [default])` | The value for the first case equal to an expression |105| `XOR(logical1, [logical2, ...])` | TRUE if an odd number of arguments are true |106107## Text108109| Function | Description |110|---|---|111| `CONCAT(value1, value2)` | Join two values as text |112| `CONCATENATE(string1, [string2, ...])` | Join text, including every cell of ranges |113| `EXACT(string1, string2)` | TRUE if two texts are identical, case included |114| `FIND(search_for, text_to_search, [starting_at])` | Position of text, case-sensitive |115| `LEFT(string, [number_of_characters])` | The first characters of text |116| `LEN(text)` | Number of characters in text: an emoji is one, where Sheets counts two |117| `LOWER(text)` | Text in lower case |118| `MID(string, starting_at, extract_length)` | Characters from the middle of text |119| `PROPER(text)` | Text with each word capitalized |120| `REPLACE(text, position, length, new_text)` | Replace characters at a position |121| `REPT(text, number_of_repetitions)` | Text repeated a number of times |122| `RIGHT(string, [number_of_characters])` | The last characters of text |123| `SEARCH(search_for, text_to_search, [starting_at])` | Position of text, ignoring case, with * and ? wildcards |124| `SUBSTITUTE(text, search_for, replace_with, [occurrence_number])` | Replace occurrences of text |125| `TEXT(number, format)` | A number as text in a format, e.g. "$#,##0.00" or "yyyy-mm-dd" |126| `TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])` | Join text with a delimiter |127| `TRIM(text)` | Text without leading, trailing and repeated spaces |128| `UPPER(text)` | Text in upper case: ß is SS |129| `VALUE(text)` | Text as a number; dates and times too |130131## Split and regular expressions132133| Function | Description |134|---|---|135| `REGEXEXTRACT(text, regular_expression)` | The first match of a regular expression, or its capture groups across |136| `REGEXMATCH(text, regular_expression)` | TRUE if text matches a regular expression |137| `REGEXREPLACE(text, regular_expression, replacement)` | Text with every match replaced; $1 in the replacement is a capture group |138| `SPLIT(text, delimiter, [split_by_each], [remove_empty_text])` | Text split at a delimiter into cells across (each character of it by default) |139140## Lookup141142| Function | Description |143|---|---|144| `CHOOSE(index, choice1, [choice2, ...])` | The choice at a position |145| `COLUMNS(range)` | Number of columns in a range |146| `HLOOKUP(search_key, range, index, [is_sorted])` | Find a key in the first row and return a value from its column |147| `INDEX(reference, [row], [column])` | The value at a row and column of a range; row or column 0 for a whole column or row |148| `MATCH(search_key, range, [search_type])` | Position of a key in a row or column |149| `ROWS(range)` | Number of rows in a range |150| `VLOOKUP(search_key, range, index, [is_sorted])` | Find a key in the first column and return a value from its row |151| `XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])` | Find a key and return the matching entry of another range |152153## Arrays154155| Function | Description |156|---|---|157| `ARRAYFORMULA(array_formula)` | Compute a formula over arrays: ranges read whole, and functions of one value applied to each entry |158| `CHOOSECOLS(array, col_num1, [col_num2, ...])` | Columns of an array by position, negative from the end |159| `CHOOSEROWS(array, row_num1, [row_num2, ...])` | Rows of an array by position, negative from the end |160| `FILTER(range, condition1, [condition2, ...])` | The rows (or columns) of a range where every condition is true |161| `FLATTEN(range1, [range2, ...])` | Every entry of ranges in one column, row by row |162| `SEQUENCE(rows, [columns], [start], [step])` | An array of numbers counting up from start by step |163| `SORT(range, [sort_column], [is_ascending], [sort_column2, is_ascending2, ...])` | The rows of a range sorted by columns |164| `SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1, ...])` | The first n rows of a range after sorting |165| `TRANSPOSE(array_or_range)` | Rows as columns and columns as rows |166| `UNIQUE(range, [by_column], [exactly_once])` | The distinct rows (or columns) of a range, in order |167168## LET and LAMBDA169170| Function | Description |171|---|---|172| `BYCOL(array_or_range, LAMBDA)` | Each column of an array passed to a LAMBDA, one value per column |173| `BYROW(array_or_range, LAMBDA)` | Each row of an array passed to a LAMBDA, one value per row |174| `LAMBDA([name, ...], formula_expression)` | A function of names, called with values: LAMBDA(x, x*2)(3) |175| `LET(name1, value_expression1, [name2, value_expression2, ...], formula_expression)` | Name values for use in a formula; an error counts only where its name is used |176| `MAKEARRAY(rows, columns, LAMBDA)` | An array of a size, each entry a LAMBDA of its row and column |177| `MAP(array1, [array2, ...], LAMBDA)` | Each entry of arrays passed to a LAMBDA |178| `REDUCE(initial_value, array_or_range, LAMBDA)` | An array folded into one value by a LAMBDA of the total so far and each entry |179| `SCAN(initial_value, array_or_range, LAMBDA)` | The running totals of REDUCE, one for each entry |180181## Date and time182183| Function | Description |184|---|---|185| `DATE(year, month, day)` | A date from its parts; months and days past the end roll over |186| `DATEDIF(start_date, end_date, unit)` | Time between dates in "Y", "M", "D", "MD", "YM" or "YD" |187| `DATEVALUE(date_string)` | The date a text such as "2026-09-26" stands for |188| `DAY(date)` | Day of the month of a date |189| `DAYS(end_date, start_date)` | Number of days between two dates |190| `EDATE(start_date, months)` | The same day a number of months away |191| `EOMONTH(start_date, months)` | The last day of the month a number of months away |192| `HOUR(time)` | Hour of a time, 0 to 23 |193| `MINUTE(time)` | Minute of a time, 0 to 59 |194| `MONTH(date)` | Month of a date, 1 to 12 |195| `NETWORKDAYS(start_date, end_date, [holidays])` | Number of weekdays between two dates, counting both |196| `NOW()` | The current date and time, updated on every change (recalculates on every change) |197| `SECOND(time)` | Second of a time, 0 to 59 |198| `TIME(hour, minute, second)` | A time of day from its parts |199| `TIMEVALUE(time_string)` | The time of day a text such as "2:30 PM" stands for |200| `TODAY()` | Today's date, updated on every change (recalculates on every change) |201| `WEEKDAY(date, [type])` | Day of the week: 1 is Sunday, or Monday with type 2 |202| `YEAR(date)` | Year of a date |203204## Finance205206| Function | Description |207|---|---|208| `FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])` | Future value of a series of payments |209| `IRR(cashflow_amounts, [rate_guess])` | Internal rate of return of periodic cash flows |210| `NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])` | Number of periods to pay off a loan or reach a value |211| `NPV(discount, cashflow1, [cashflow2, ...])` | Net present value of periodic cash flows |212| `PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])` | Payment per period of a loan or investment |213| `PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])` | Present value of a series of payments |214| `RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])` | Interest rate per period of an annuity |215216## Links217218| Function | Description |219|---|---|220| `HYPERLINK(url, [link_label])` | A link that opens url, shown as its label |221222## JEV (hosted model)223224| Function | Description |225|---|---|226| `JEV.CLASSIFY(value, question, labels, [descriptions])` | The label that fits best, chosen by the JEV model (recalculates on every change) |227| `JEV.PROB(value, question, [yes_means], [no_means])` | The probability the answer is yes, from the JEV model (recalculates on every change) |228| `JEV.SCORE(value, question, levels)` | A score on an ordered rubric, from the JEV model (recalculates on every change) |229| `JEV.TEST(value, question, [yes_means], [no_means])` | TRUE or FALSE, answered by the JEV model (recalculates on every change) |230