The following formulas are currently supported in Wdesk. You can also view formulas within Wdesk using the Formula Assistant in the Formula Bar.
Date functions
Formula | 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
Formula | 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 |
NPV | Calculates the net present value of an investment using discount rates, payments, and income |
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
Formula | 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 |
Lookup functions
Formula | Description |
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 |
Logical functions
Formula | 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 |
Mathematical functions
Formula | 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 | Create a total from given values using the specified operation, ignoring any subtotals in the range |
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
Formula | 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 |
Text creation functions
Formula | Description |
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 |
TRIM | Removes excess whitespace from text |
UPPER | Returns the text with all characters capitalized |
Statistical functions
Formula | 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 |
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 |