The following formula functions are currently supported in Workiva. You can also view these functions using the Formula Assistant in the Formula Bar.
Contents:
- 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
* Can be used with CHILDREFS function
** Must be used within another function
Note: Workiva spreadsheets only support English-language functions. Other languages aren't supported.
| 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 |
| EO MONTH | 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 numeric values in a set of values * |
| COUNTA | Counts the number of non-blank values in a set of values * |
| COUNTBLANK | Counts the number of blank values in a set of values * |
| COUNTIF | Counts the number of cells in a range that meet a certain condition |
| COUNTIFS | Counts the number of cells in a range that meet a 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 |
| 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 a value from 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 |
| 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 one value if a condition specified is TRUE and another value if it is FALSE * |
| IFERROR | Returns value if it is not an error; otherwise, returns value_if_error |
| 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 * |
| Function | Description |
| ABS | Returns the absolute value of a number |
| EXP | Returns e raised to the power of number |
| LN | Returns the natural logarithm of a 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, 101 excludes hidden rows for the average. |
| 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 |
| Function | Description |
| INT | Rounds a number down to the nearest 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 |
| ROUNDUP | Rounds a number up |
| TRUNC | Truncates a number to a specific number of digits |
| 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 |
| 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 * |
| Operator sign and name | Operator meaning | Description | Example |
| : Colon | Ranger operator |
Reference all the cells of two ranges. The range operator (:) refers all the cells between two ranges. |
Sum all the cells of the range D1 to D25, =SUM(D1:D25) |
| Space | Intersection operator |
Reference the common range of cells in two ranges. The intersection operator ( ) intersections cells of two ranges. |
Find the sum of the intersection cells of two ranges A1:B5 and B1:D5, =SUM(A1:B5 B1:D5) |
| , Comma | Union operator |
Combines multiple references into one. The union operator (,) combines two ranges in to one. |
Find the maximum of the all the cells of two ranges A1:B5 and B1:D5, =MAX(A1:B5,B1:D5) |
| Operator sign and name | Operator meaning | Description | Example |
| - Minus | Negation |
Negative number The negation operator (-) finds the negation value of number. |
25 can be converted to its negation value, -25 |
| % Percent | Percent |
Percentage number The percentage operator (%) finds the percentage number. |
Find the 25% of 1000, =25%*1000 |
| ^ Caret | Exponentiation |
Exponential value The first number before the caret is the base and the second number after the caret is exponent. The exponentiation operator (^) finds the exponential value of number. |
Square value of 25 can be found using exponentiation operator, =25^2 |
| / Forward slash | Division |
Division value The first number before the slash will be divided by the second number. Forward slash is the division operator (/) which divides any number with another number. |
Divide range B1 by A1, =B1/A1 |
| * Asterisk | Multiplication |
Multiplication value We can multiply using the multiplication operator (*). |
Multiply two ranges, =A1*B1 |
| + Plus | Addition |
Addition value The addition operator (+) adds numbers together. |
Add range A1 and B1, =A1+B1 |
| - Minus | Subtraction |
Subtraction value The first number before the minus will be subtracted from the second number. The subtraction operator (-) subtracts numbers. |
Subtract 100 from range B5, =B5-100 |
| Operator sign and name | Operator meaning | Description | Example |
| & Ampersand | Ampersand |
Concatenates two strings into one |
Concatenate two cells to form a combined string, = A1&B1 |
| Operator sign and name | Operator meaning | Description | Example |
| = Equal to | Equal to comparison |
Checks if two expressions are equal |
=IF(A1=A2,”Equal”,”Not equal”) |
| > Greater than | Greater than comparision |
Checks if the first expression is greater than the second expressions |
=IF(A1>A2,”Bigger”,”Not bigger”) |
| < Less than | Less than comparison |
Checks if the first expression is less than the second expression |
=IF(A1<A2,”Smaller”,”Not smaller”) |
| >= Greater than or equal to | Greater than or equal to comparison |
Checks if the first expression is greater than or equal to the second expression |
=IF(A1>=A2,”Greater than or equal”,”Smaller”) |
| <= Less than or equal to | Less than or equal to comparison |
Checks if the first expression is less than or equal to the second expression |
=IF(A1<=A2,”Less than or equal”,”Greater”) |
| <> Not equal to | Not equal to comparison |
Checks if the two expressions aren't equal |
=IF(A1<>A2,”Not equal”,”Equal”) |