The following formula functions are currently supported in Workiva. You can also view these functions using the Formula Assistant in the Formula Bar.
Contents
- Array functions
- Date functions
- Financial calculation functions
- Information gathering functions
- Lookup functions
- Logical functions
- Mathematical functions
- Rounding functions
- Text creation functions
- Statistical functions
- Reference operators
- Arithmetic operators
- Concatenation operator
- Comparison operators
Notes
- * Indicates that this function can be used with the CHILDREFS function
- ** Indicates that this function must be used within another function.
- Workiva spreadsheets only support English-language functions. Other languages are not supported.
- The Spreadsheet function compatibility reference page provides a guide to the formula functions supported by Workiva, Excel, Google Sheets, and Workiva Chains (for Data Prep)
Array functions
| Function | Description |
|---|---|
| ROWS | Determines the number of rows in a specified array or reference. |
| COLUMNS | Determines the number of columns in a specified array or reference. |
| CHOOSEROWS | Extracts specific rows from a range or array. |
| CHOOSECOLS | Extracts specific columns from a range or array. |
| TEXTSPLIT | Splits text strings into rows and columns based on specified delimiters. |
| HSTACK | Horizontally stacks multiple ranges or arrays into a single range. |
| VSTACK | Vertically stacks multiple ranges or arrays into a single range. |
| UNIQUE | Extracts a list of unique values in a list or range. |
| TRANSPOSE | Converts a vertical range of cells to a horizontal range, or vice versa. |
| TAKE | Extracts a specified number of rows or columns from the beginning or end of a range or array. |
| SORT | Dynamically sort a range or array by one or more columns in ascending or descending order. |
| SEQUENCE | Generates a list of sequential numbers in an array. |
| FILTER | Extracts rows from a range or array that meet specified conditions. |
| DROP | Removes a specified number of rows or columns from the beginning or end of an array or range. |
| MMULT | Calculates the matrix product of two arrays. |
| TOROW | Returns an array as a single row. |
| TOCOL | Returns an array as a single column. |
Date functions
| Function | Description |
|---|---|
| DATE | Returns a date from a given year, month, and day. |
| DATEVALUE | Returns a serial number based on a date written in MM/DD/YYYY format. |
| DAY | Returns the day of the month for a date. |
| DAYS | Returns the number of days between two dates. |
| DAYS360 | Returns the number of days between two dates based on a 360-day year (twelve 30-day months). |
| EDATE | Returns the serial date that is the indicated number of months before or after a specified date. |
| EOMONTH | Returns the end of the month for a given date, offset by a number of months. |
| MONTH | Returns the month of the year for a date. |
| NETWORKDAYS | Returns the number of whole working days between two dates. |
| NETWORKDAYS.INTL | Returns the number of whole working days between two dates with custom weekends and holidays. |
| WEEKDAY | Returns the day of the week corresponding to a date. |
| WORKDAY | Returns the serial date before or after a specified number of workdays. |
| WORKDAY.INTL | Returns the serial date before or after a specified number of workdays with custom weekend parameters. |
| YEAR | Returns the year for a date. |
| YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates. |
Financial calculation functions
| Function | Description |
|---|---|
| FV | Calculates the future value of an investment based on constant interest rate. |
| IRR | Calculates internal rate of return for a regular series of cash flows. |
| XIRR | Calculates an internal rate of return for cash flows that aren’t periodic. |
| NPV * | Calculates the net present value of an investment using discount rates, payments, and income. |
| XNPV | Calculates the net present value for cash flows that aren't periodic. |
| PMT | Calculates payment on a loan given constant interest rate and payments. |
| PV | Calculates the present value of an investment based on a constant interest rate. |
Information gathering functions
| Function | Description |
|---|---|
| COUNT * | Counts the number of Arabic numeric values in the supplied set of values. |
| COUNTA * | Counts the number of non-blank values in the supplied set of values. Space characters are not evaluated as blanks. |
| COUNTBLANK * | Counts the number of blank values in the supplied set of values. |
| COUNTIF | Counts the number of cells in a range that meet the supplied condition. |
| COUNTIFS | Counts the number of cells in a range that meet the supplied set of conditions. |
| ISBLANK | Returns TRUE or FALSE if the selection is empty. |
| ISERROR | Returns TRUE if the value is an error; otherwise returns FALSE. |
| ISNA | Returns TRUE or FALSE if the selection is #N/A. |
| ISNUMBER | Returns TRUE or False if the selection is a number. |
| VALUE | Returns a numerical value for a given text string representing a number or date. |
Lookup functions
| Function | Description |
|---|---|
| CELL | Returns information about a cell reference. |
| CHILDREFS ** | References selected range for each child sheet. |
| HLOOKUP | Finds given value in a row then returns a value from that column. |
| INDEX | Returns the value at the specified row/column coordinate. |
| MATCH | Finds an index in a one-dimensional range. |
| VLOOKUP | Finds given value in a column then returns the matching row value for that row. |
| XLOOKUP | Finds an index in the lookup range and returns the corresponding value in the return range. |
| XMATCH | Finds a value in the lookup range and returns the corresponding index. |
Logical functions
| Function | Description |
|---|---|
| AND * | Returns TRUE if all arguments are TRUE; otherwise returns FALSE. |
| CHOOSE * | Uses index_num to return a value from the list of value arguments. |
| IF * | Returns a supplied value if a condition specified is TRUE and a different supplied value if it is FALSE. |
| IFERROR | Returns first supplied value if it is not an error; otherwise returns second supplied value. |
| IFNA | Returns an alternate value if the first value is #N/A. |
| IFS * | Checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. |
| NOT | Returns FALSE if the expression is TRUE; Returns TRUE if the expression is FALSE. |
| OR * | Returns TRUE if any arguments are TRUE; returns FALSE if all arguments are FALSE. |
Mathematical functions
| Function | Description |
|---|---|
| ABS | Returns the absolute value of the supplied number. |
| EXP | Returns e raised to the power of the supplied number. |
| LN | Returns the natural logarithm of the supplied number. |
| MOD | Returns the remainder from a division. |
| POWER | Returns the result of an exponentiation. |
| PRODUCT * | Multiplies all the numbers in a selection of cells. |
| SIGN | Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. |
| SQRT | Returns a positive square root. |
| SUBTOTAL |
Creates a total from given values using the specified operation, ignoring any subtotals in the range. Filters are ignored for all totals. To exclude hidden rows, include a "10" in front of the operation. For example, |
| SUM * | Adds all the numbers in a selection of cells. |
| SUMIF | Adds the cells according to a condition in a corresponding range. |
| SUMIFS | Adds the cells according to a set of conditions in corresponding ranges. |
| SUMPRODUCT | Multiplies corresponding components in equal-sized ranges and returns the sum of the products. |
Rounding functions
| Function | Description |
|---|---|
| INT | Returns the nearest lower integer. |
| MROUND | Returns a number rounded to the desired multiple. |
| ROUND | Rounds a number to a specified number of digits. |
| ROUNDDOWN | Rounds a number down to the nearest integer. |
| ROUNDUP | Rounds a number up to the nearest integer. |
| TRUNC | Truncates a number to a specific number of digits. |
Text creation functions
| Function | Description |
|---|---|
| CODE | Returns the numeric code of the first character in the given text. |
| CONCATENATE * | Joins several text strings into one text string. |
| CONCATENATEIF | Joins cells with a separator according to a condition in a range. |
| CHAR | Returns the character associated with the given character code. |
| FIND | Returns the starting position of text within a string. |
| LEFT | Returns the first character or characters in some text. |
| LEN | Returns the number of characters in the argument. |
| LOWER | Returns the text with all characters in lowercase. |
| MID | Returns a selected portion of text. |
| PROPER | Returns the text with only the first letter in each word capitalized. |
| REPT | Repeats text a given number of times. |
| RIGHT | Returns the last character in some text. |
| SEARCH | Returns the starting position of text within a string. |
| SUBSTITUTE | Substitutes specific characters of the text with new text. |
| TEXT | Formats the given number as text by the format string. |
| TEXTJOIN * | Combines text from multiple ranges with a delimiter between each text value. |
| TRIM | Removes excess whitespace from text. |
| UNICHAR | Returns the character associated with the given Unicode number. |
| UNICODE | Returns the Unicode number of the first character in the given text. |
| UPPER | Returns the text with all characters capitalized. |
Statistical functions
| Function | Description |
|---|---|
| AVERAGE * | Returns the average (arithmetic mean) of a set. |
| AVERAGEA * | Finds the arithmetic mean of a set including text and logical values. |
| AVERAGEIFS | Returns the average of all cells that meet multiple criteria. |
| LARGE * | Returns the n-th largest value from a range. |
| MAX * | Returns the largest number in a set of values. |
| MAXA * | Returns the largest value in a set including text and logical values. |
| MAXIFS | Returns the maximum value of all cells that meet multiple criteria. |
| MEDIAN * | Returns the statistical median of a given set of numbers. |
| MIN * | Returns the smallest number in a set of values. |
| MINA * | Returns the smallest value in a set including text and logical values. |
| MINIFS | Returns the minimum value of all cells that meet multiple criteria. |
| PERCENTILE | Returns a percentile of values in a cell range. |
| PERCENTILE.EXC | Returns a percentile of values (0-1, exclusive) in a cell range. |
| PERCENTILE.INC | Returns a percentile of values (0-1, inclusive) in a cell range. |
| QUARTILE | Returns a quartile of values in a cell range. |
| QUARTILE.EXC | Returns a quartile of values (0-4, exclusive) in a cell range. |
| QUARTILE.INC | Returns a quartile of values (0-4, inclusive) in a cell range. |
| RANK * | Returns the rank of a given value in a corresponding range. |
| RANK.AVG * | Returns the statistical average of the rank of a given value in a corresponding range. |
| RANK.EQ | Returns the statistical rank of a given value in a corresponding range. |
| SMALL * | Returns the n-th smallest value from a range. |
| STDEV | Returns the standard deviation for a given set of values within a sample. |
| STDEVA * | Returns the standard deviation for a given set of values within a sample, where text or FALSE=0 and TRUE=1. |
| STDEVPA * | Returns the standard deviation for a given set of values within an entire population, where text or FALSE=0 and TRUE=1. |
| STDEV.S * | Returns the standard deviation for a given set of values within a sample. |
| STDEV.P * | Returns the sample standard deviation for a given set of values for an entire population. |
Reference operators
| Operator sign and name | Operator meaning | Description | Example |
|---|---|---|---|
| : (Colon) | Range operator | Reference all the cells of two ranges. | Sum all the cells in the range D1 to D25.=SUM(D1:D25)
|
| (Space) | Intersection operator | Reference the common range of cells in two ranges. | Find the sum of the intersection cells of the ranges A1:B5 and B1:D5, =SUM(A1:B5 B1:D5)
|
| , (Comma) | Union operator | Combines multiple references into one. | Find the maximum value from all the cells of the ranges A1:B5 and B1:D5, =MAX(A1:B5,B1:D5)
|
Arithmetic operators
| Operator sign and name | Operator meaning | Description | Example |
|---|---|---|---|
| - (Minus) | Negation | Returns the negative value of the supplied number. | Return 25 as a negative value, -25
|
| % (Percent) | Percent | Identifies a value as a percentage. | Returns 23% of 1000, =23%*1000
|
| ^ (Caret) | Exponentiation |
Returns the exponential value of the supplied number. The number before the caret is the base and the number after the caret is the exponent. |
Return the square value of 5. =5^2
|
| / (Forward slash) | Division | Divides the first value by the second value. | Divide B1 by A1, =B1/A1
|
| * (Asterisk) | Multiplication | Multiplies the first value by the second value. | Multiply A1 by B1, =A1*B1
|
| + (Plus) | Addition | Adds the first value to the second value. | Add A1 and B1, =A1+B1
|
| - (Minus) | Subtraction | Subtracts the second value from the first value. | Subtract 100 from B5. =B5-100
|
Concatenation operator
| Operator sign and name | Operator meaning | Description | Example |
|---|---|---|---|
| & (Ampersand) | Ampersand | Concatenates the supplied strings into one string. | Concatenate two cells to form a combined string, = A1&B1
|
Comparison operators
| Operator sign and name | Operator meaning | Description | Example |
|---|---|---|---|
| = (Equal to) | Equal to comparison | Checks if two values are equal. | =IF(A1=A2,”Equal”,”Not equal”) |
| > (Greater than) | Greater than comparison | Checks if the first value is greater than the second value. | =IF(A1>A2,”Bigger”,”Not bigger”) |
| < (Less than) | Less than comparison | Checks if the first value is less than the second value. | =IF(A1<A2,”Smaller”,”Not smaller”) |
| >= (Greater than or equal to) |
Greater than or equal to comparison | Checks if the first value is greater than or equal to the second value. | =IF(A1>=A2,”Greater than or equal”,”Smaller”) |
| <= (Less than or equal to) |
Less than or equal to comparison | Checks if the first value is less than or equal to the second value. | =IF(A1<=A2,”Less than or equal”,”Greater”) |
| <> (Not equal to) | Not equal to comparison | Checks if the two value aren't equal. | =IF(A1<>A2,”Not equal”,”Equal”) |