| 1 | Math | ABS | Returns the absolute value of a number. Negative numbers are converted to positive numbers, positive numbers are unaffected. | [number] - The number to calculate the absolute value of. | ABS (number) | ABS(-25) = 25 |
| 2 | Logical | AND | A logical function of more than one condition. AND returns either TRUE or FALSE. True is returned only if all conditions are TRUE | [logical_expression1] - An expression or reference to a cell containing an expression that represents a logical value, i.e. "TRUE" or "FALSE", or an expression that can be coerced to a logical value., [logical_expression2]… - [optional] repeatable. More expressions that represent logical values. | AND (logical1, [logical2], ...) | AND(2>0 , 1<10) = TRUE |
| 3 | Math | AVERAGE | Calculates the average (arithmetic mean) of the given numbers. | [number1] - A number that refers to numeric values., [number2]… - [optional] repeatable. Additional values to consider when calculating the average value. | AVERAGE (number1, [number2], ...) | AVERAGE(0.5, 2) = 1.25 |
| 4 | Math | CEILING | Rounds a given number up to the nearest specified multiple. | [number] - The number that should be rounded., [significance] - The multiple to use when rounding. | CEILING (number, significance) | CEILING(10, 3) = 12; CEILING(36, 7) = 42 |
| 5 | Text | CHOOSE | Returns a value from a list using a given position or index. | [index_num] - The value to choose. A number between 1 and 254., [value1] - The first value from which to choose., [value2] - [optional] The second value from which to choose. | CHOOSE (index_num, value1, [value2], ...) | CHOOSE(2, "red" , "blue" , "green") = "blue"" |
| 6 | Text | CLEAN | Takes a text string and returns text that has been cleaned of line breaks and other non-printable characters. | [text] - The text to clean. | CLEAN (text) | CLEAN("Monthly\n report") = "Monthly report" |
| 7 | Text | CONCATENATE | Concatenates joins up to 30 text items together and returns the result as text. | [text1] - The first text value to join, [text2] - The second text value to join together., [text3] - [optional] The third text value to join. | CONCATENATE (text1, text2, [text3], ...) | CONCATENATE(account123, "__", user123) = "account123__user123" |
| 8 | Math | CONSTANT | Return constant value | [value1] - The text or number value. | CONSTANT (value1) | CONSTANT("TEST") = "TEST"; CONSTANT(24.5) = 24.5 |
| 9 | Math | COUNT | Returns the count of the values that are numbers. | [value1] - An item, cell reference, or range., [value2] - [optional] An item, cell reference, or range. | COUNT (value1, [value2], ...) | COUNT("not_number", 12) = 1 |
| 10 | Math | COUNTA | Returns the count of values that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells. | [value1] - An item, cell reference, or range., [value2] - [optional] An item, cell reference, or range. | COUNTA (value1, [value2], ...) | COUNTA(2, 11, "Text") = 3 |
| 11 | Date | DATE | Creates a valid date as a string from individual year, month, and day components. | [year] - Number for year., [month] - Number for month., [day] - Number for day. | DATE (year, month, day) | DATE(65, 11, 2) = 24048 |
| 12 | Date | DATEVALUE | Convert a date string to a date value. | [date_string] - a string representing a date. | DATEVALUE(date_string) | DATEVALUE(Today()) = 44358 |
| 13 | Date | DAY | Returns the day of the month as a number between 1 to 31 from a given date. | [date] - A valid date as a string. | DAY (date) | DAY("2020-10-02") = 2 |
| 14 | Date | DAYS360 | Returns the number of days between two dates based on a 360-day year. Calculations based on a 360-day year comes from certain accounting calculations where all 12 months are considered to have 30 days. | [start_date] - The start date., [end_date] - The end date., [method] - [optional] The type of day count basis to use. FALSE (default) is US method, TRUE is European method. | DAYS360 (start_date, end_date, [method]) | DAYS360("2019-01-01", "2020-10-02") = 631 |
| 15 | Math | DELTA | Checks if two numeric values are equal. If values are equal,DELTA returns 1, otherwise, DELTA returns zero. Note: Use the VALUE() function to convert string to number. | [number1] - The first number., [number2] - [optional] The second number. | DELTA (number1, [number2]) | DELTA(2, 15) = 0; DELTA(2, 2) = 1 |
| 16 | Date | EDATE | Returns a date on the same day of the month, n months in the past or future. | [start_date] - Start date as a valid Excel date., [months] - Number of months before or after start_date. | EDATE (start_date, months) | EDATE("2020-10-5", 1) = "2020-11-5"; |
| 17 | Date | EOMONTH | Returns the last day of the month, n months in the past or future. | [start_date] - A date that represents the start date in a valid Excel format., [months] - The number of months before or after start_date. | EOMONTH (start_date, months) | EOMONTH("2020-10-02", 2) = "2020-12-31" |
| 18 | Text | EXACT | Tests whether two strings are identical (case sensitive). | [text1] - The first text string to compare., [text2] - The second text string to compare. | EXACT (text1, text2) | EXACT("Account1", "Account2") = FALSE; EXACT("User1", "User1") = TRUE |
| 19 | Logical | FALSE | Returns the value FALSE. | | FALSE () | FALSE() = FALSE |
| 20 | Text | FIND | Returns the position (as a number) of one text string inside another. | [find_text] - The text to find., [within_text] - The text to search within., [start_num] - [optional] The starting position in the text to search. Optional, defaults to 1. | FIND (find_text, within_text, [start_num]) | FIND("user1", "account123__user1") = 13 |
| 21 | Formatting | FIXED | Format number with fixed number of decimal places | [number] - The number to round and format., [decimals] - [optional] Number of decimals to use. Default is 2., [no_commas] - [optional] Suppress commas. TRUE = no commas, FALSE = commas. | FIXED (number, [decimals], [no_commas]) | FIXED(11, 2) = 11.00 |
| 22 | Math | FLOOR | Rounds a given number down to the nearest specified factor. | [number] - The number that should be rounded., [significance] - The multiple to use when rounding. | FLOOR (number, significance) | FLOOR(10, 3) = 9; FLOOR(36, 7) = 35 |
| 23 | Date | HOUR | Returns the hour component of a time as a number between 0-23. | [Time] - A valid time number. | HOUR (Time) | HOUR("11:40:59") = 11 |
| 24 | Logical | IF | Runs a logical test and returns one value for a TRUE result, and another for a FALSE result. Note: If the logical test in numeric, use the VALUE() function to convert string to number. | [logical_test] - A value or logical expression that can be evaluated as TRUE or FALSE., [value_if_true] - [optional] The value to return when logical_test evaluates to TRUE., [value_if_false] - [optional] The value to return when logical_test evaluates to FALSE. | IF (logical_test, [value_if_true], [value_if_false]) | IF(accountID = "Account123", 1, 2) = 1 |
| 25 | Logical | IFERROR | Returns a custom result when a formula generates an error, and a standard result when no error is detected. | [value] - The value, reference, or formula to check for an error., [value_if_error] - The value to return if an error is found. | IFERROR (value, value_if_error) | IFERROR(14/0, "error message") = "error message" |
| 26 | Logical | IFNULL | Check if value is null and returns one value for a TRUE result, and another for a FALSE result. | [value] - A value or logical expression to check if null,, [value_if_true] - The value to return when [value] evaluates to TRUE., [value_if_false] - The value to return when [value] evaluates to FALSE. | IFNULL (value, value_if_true, value_if_false) | IFNULL(null, "null value", "non-null value") = "null value"; IFNULL("test", "null value", "non-null value") = "non-null value" |
| 27 | Logical | ISBLANK | Returns TRUE when the value is empty, and FALSE when a value is not empty. | [value] - The value to check. | ISBLANK (value) | ISBLANK(11.2) = FALSE |
| 28 | Logical | ISERR | Returns TRUE for any error type except the #N/A error. | [value] - The value to check for any error but #N/A. | ISERR (value) | ISERR(12/0) = TRUE |
| 29 | Logical | ISEVEN | Returns TRUE when a numeric value is even, and FALSE for odd numbers. Note: Use the VALUE() function to convert string to number. | [value] - The numeric value to check. | ISEVEN (value) | ISEVEN(10) = TRUE; ISEVEN(11) = FALSE |
| 30 | Logical | ISLOGICAL | Returns TRUE when a value contains the logical values TRUE or FALSE, and returns FALSE for values that contain any other value, including empty value. | [value] - The value to test as logical. | ISLOGICAL (value) | ISLOGICAL(TRUE()) = TRUE |
| 31 | Logical | ISNA | Returns TRUE when a value contains the #N/A error and FALSE for any other value, or any other error type. | [value] - The value to check if #N/A. | ISNA (value) | ISNA("TEST") = FALSE |
| 32 | Logical | ISNONTEXT | Returns TRUE for any non-text value, for example, a number, a date, a time, etc. | [value] - The value to check. | ISNONTEXT (value) | ISNONTEXT(2) = TRUE |
| 33 | Logical | ISNULL | Returns TRUE when a value is null, otherwise return FALSE. | [value] - The value to check if null. | ISNULL (value) | ISNULL(null) = TRUE; ISNULL("test") = FALES |
| 34 | Logical | ISNUMBER | Returns TRUE when a value is a number, and FALSE if not. | [value] - The value to check. | ISNUMBER (value) | ISNUMBER("string-value") = FALSE |
| 35 | Logical | ISODD | Returns TRUE when a numeric value is odd, and FALSE for even numbers. | [value] - The numeric value to check. | ISODD (value) | ISODD(2) = FALSE; ISODD(11) = TRUE |
| 36 | Logical | ISTEXT | Returns TRUE when a cell contains a text, and FALSE if not. | [value] - The value to check. | ISTEXT (value) | ISTEXT("string-value") = TRUE |
| 37 | Text | SUBSTRINGBEFORE | Returns a substring before the first occurrence of the delimiter. If the text does not contain the delimiter, returns the default-value. | [text] - The text., [delimiter] - string to be used as delimiter, [default-value] - default value if no result | SUBSTRINGBEFORE (text,delimiter,default-value) | SUBSTRINGBEFORE("string-a ### string-b"," ### ","default-value") = "string-a" |
| 38 | Text | SUBSTRINGAFTER | Returns a substring after the first occurrence of the delimiter. If the text does not contain the delimiter, returns the default-value. | [text] - The text., [delimiter] - string to be used as delimiter, [default-value] - default value if no result | SUBSTRINGAFTER (text,delimiter,default-value) | SUBSTRINGAFTER("string-a ### string-b"," ### ","default-value") = "string-b" |
| 39 | Text | LEFT | Extracts a given number of characters from the left side of a supplied text string. LEFT function can get a long text parameter (up to 10K characters) if it is used alone and not as part of another formula. | [text] - The text from which to extract characters., [num_chars] - [optional] The number of characters to extract, starting on the left side of text. | LEFT (text, [num_chars]) | LEFT("Account123", 3) = "Acc" |
| 40 | Text | LEN | Returns the length of a given text string as the number of characters. | [text] - The text for which to calculate length. | LEN (text) | LEN("string-value") = 12 |
| 41 | Text | LOWER | Returns a lower-case version of a given text string. Numbers and punctuation are not affected. | [text] - The text that should be converted to lowercase. | LOWER (text) | LOWER("String-Value") = "string-value" |
| 42 | Math | MAX | Returns the largest numeric value in a range of values. Note: Use the VALUE() function to convert string to number. | [number1] - Number, reference to numeric value, or range that contains numeric values., [number2] - [optional] Number, reference to numeric value, or range that contains numeric values. | MAX (number1, [number2], ...) | MAX(2, 11) = 11 |
| 43 | Math | MAXA | Returns the largest numeric value in a range of values. Note: Use the VALUE() function to convert string to number | [value1] - Number, reference to numeric value, or range that contains numeric values., [value2] - [optional] Number, reference to numeric value, or range that contains numeric values. | MAXA (value1, [value2], ...) | MAXA(2, 11, TRUE()) = 11 |
| 44 | Text | MID | Extracts a given number of characters from the middle of a supplied text string. | [text] - The text to extract from., [start_num] - The location of the first character to extract., [num_chars] - The number of characters to extract. | MID (text, start_num, num_chars) | MID("Account123", 2, 3) = "cco" |
| 45 | Math | MIN | Returns the smallest numeric value in a range of values. Note: Use the VALUE() function to convert string to number. | [number1] - Number, reference to numeric value, or range that contains numeric values., [number2] - [optional] Number, reference to numeric value, or range that contains numeric values. | MIN (number1, [number2], ...) | MIN(2, 11, 65, ) = 2 |
| 46 | Math | MINA | Returns the smallest numeric value in a range of values. Note: Use the VALUE() function to convert string to number. | [value1] - Number, reference to numeric value, or range that contains numeric values., [value2] - [optional] Number, reference to numeric value, or range that contains numeric values. | MINA (value1, [value2], ...) | MINA(2, 11, 65, ) = 2; MINA(2, 11, 65, TRUE()) = 1 |
| 47 | Date | MINUTE | Extracts the minute component of a time as a number between 0-59. | [time] - A valid date or time. | MINUTE (time) | MINUTE("11:40:59") = 40 |
| 48 | Date | MONTH | Extracts the month from a given date as number between 1 to 12. | MONTH("2020-10-2") = 10, [date] - A valid Excel date. | MONTH (date) | MONTH (date) |
| 49 | Date | NETWORKDAYS | Calculates the number of working days between two dates. | [start_date] - The start date as serial number., [end_date] - The end date as serial number. | NETWORKDAYS (start_date, end_date) | NETWORKDAYS("2019-01-01", "2020-10-02") = 459; |
| 50 | Logical | NOT | Returns the opposite of a given logical or boolean value. When given TRUE, NOT returns FALSE.When given FALSE, NOT returns TRUE. | [logical] - A value or logical expression that can be evaluated as TRUE or FALSE. | NOT (logical) | NOT(TRUE()) = FALSE NOT(FALSE()) = TRUE |
| 51 | Date | NOW | Returns the current date as a string. | | NOW () | NOW() = "2020-10-02" |
| 52 | Math | ODD | Returns the next odd integer after rounding a given number up. | [number] - The number to round up to an odd integer. | ODD (number) | ODD(9.1) = 11; ODD(-2.1) = -3 |
| 53 | Logical | OR | Logical function to test multiple conditions. OR returns either TRUE if at least one condition is true, or FALSE if all conditions are FALSE. | [logical1] - The first condition or logical value to evaluate., [logical2] - [optional] The second condition or logical value to evaluate. | OR (logical1, [logical2], ...) | OR(TRUE(), FALSE()) = TRUE; OR(FALSE(), FALSE()) = FALSE |
| 54 | Text | PROPER | Capitalizes words given text string. | [text] - The text that should be converted to proper case. | PROPER (text) | PROPER("All renewals are secured") = "All Renewals Are Secured"" |
| 55 | Text | REPLACE | Replaces characters specified by location in a given text string with another text string. | [old_text] - The text to replace., [start_num] - The starting location in the text to search., [num_chars] - The number of characters to replace., [new_text] - The text to replace old_text with. | REPLACE (old_text, start_num, num_chars, new_text) | REPLACE("Account123", 8, 3, 456) = "Account456"" |
| 56 | Text | RIGHT | Extracts a given number of characters from the right side of a supplied text string. | [text] - The text from which to extract characters on the right., [num_chars] - [optional] The number of characters to extract, starting on the right. Optional, default = 1. | RIGHT (text, [num_chars]) | RIGHT("Account123",3)="123" |
| 57 | Math | ROUND | Returns a number rounded to a given number of digits. | [number] - The number to round., [num_digits] - The number of digits to which number should be rounded. | ROUND (number, num_digits) | ROUND(5.7845, 1) = 5.8; ROUND(5.7845, 2) = 5.78; ROUND(5.7845, 3) = 5.785 |
| 58 | Math | ROUNDDOWN | Returns a number rounded down to a given number of places. | [number] - The number to round down., [num_digits] - The number of digits to which number should be rounded down. | ROUNDDOWN (number, num_digits) | ROUNDDOWN(5.7899, 1) = 5.7; ROUNDDOWN(5.7899, 2) = 5.78; ROUNDDOWN(5.7899, 3) = 5.789 |
| 59 | Math | ROUNDUP | Returns a number rounded up to a given number of decimal places | [number] - The number to round up., [num_digits] - The number of digits to which number should be rounded up. | ROUNDUP (number, num_digits) | ROUNDUP(5.1242, 1) = 5.2; ROUNDUP(5.1242, 1) = 5.2; ROUNDUP(5.1242, 3) = 5.125 |
| 60 | Text | SEARCH | Returns the location of one text string inside another. | [find_text] - The text to find., [within_text] - The text to search within., [start_num] - [optional] Starting position in the text to search. Optional, defaults to 1. | SEARCH (find_text, within_text, [start_num]) | SEARCH("renew", "All renewals are secured")=5 |
| 61 | Date | SECOND | Returns the second component of a time as a number between 0-59. | [time] - A valid time in a format Excel recognizes. | SECOND (time) | SECOND("9:10:15") = 15 |
| 62 | Formatting | SIGN | Returns the sign of a number as +1, -1 or 0. If number is positive, SIGN returns 1. If number is negative, sign returns -1. If number is zero, SIGN returns 0. | [number] - The number to get the sign of. | SIGN (number) | SIGN(2) =1; SIGN(0) = 0; SIGN(-25) = -1 |
| 63 | Text | SUBSTITUTE | Replaces text in a given string by matching. | [text] - The text to change., [old_text] - The text to replace., [new_text] - The text to replace with., [instance] - [optional] The instance to replace. If not supplied, all instances are replaced. | SUBSTITUTE (text, old_text, new_text, [instance]) | SUBSTITUTE("952-455-7865", "-", " ") = "952 455 7865" |
| 64 | Math | SUBTOTAL | SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others. | [function_num] - A number that specifies which function to use in calculating subtotals within a list: 1 - AVERAGE, 2 - COUNT, 3 - COUNTA, 4 - MAX, 5 - MIN, 6 - PRODUCT, 7 - STDEV, 9 - SUM, [ref1] - A named range or reference to subtotal., [ref2] - [optional] A named range or reference to subtotal. | SUBTOTAL (function_num, ref1, [ref2], ...) | SUBTOTAL(1, 2, 11) = 6.5; SUBTOTAL(4, 2, 11) = 11 |
| 65 | Math | SUM | Returns the sum of values supplied. Note: Use the VALUE() function to convert string to number. | [number1] - The first value to sum., [number2] - [optional] The second value to sum., [number3] - [optional] The third value to sum. | SUM (number1, [number2], [number3], ...) | SUM(2,11) = 13 |
| 66 | Math | SUBTRACT | Returns the subtraction of values supplied. | [number1] - The first value to subtract., [number2] - The second value to sum. | SUBTRACT (number1, number2) | SUBTRACT(8,2) = 6 |
| 67 | Math | MULTIPLY | Returns the multiplication of values supplied. | [number1] - The first value to multiply., [number2] - The second value to multiply. | MULTIPLY (number1, number2) | MULTIPLY(8,2) = 16 |
| 68 | Math | DIVIDE | Returns the division of values supplied. | [number1] - The first value to divide., [number2] - The second value to divide. | DIVIDE (number1, number2) | DIVIDE(8,2) = 4 |
| 69 | Text | T | Returns text when given a text value and an empty string ("") for numbers, dates, and the logical values TRUE and FALSE. | [value] - The value to return as text. | T (value) | T("string-value") = "string-value"; T(TRUE()) = "" |
| 70 | Formatting | TEXT | Returns a number in a given number format, as text. | [value] - The number to convert., [format_text] - The number format to use. | TEXT (value, format_text) | TEXT(0.5, $0.00) = $0.50; TEXT(44106, m/d/yyyy) = 10/2/2020 |
| 71 | Date | TIME | Built-in function that allows you to create a time with individual hour, minute, and second components. | [hour] - The hour for the time you wish to create., [minute] - The minute for the time you wish to create., [second] - The second for the time you wish to create. | TIME (hour, minute, second) | TIME(2, 11, 2) = 0.0909953703703704 |
| 72 | Date | TODAY | Returns the current date as a string | | TODAY () | TODAY() = "2020-10-02" |
| 73 | Text | TRIM | Strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text. | [text] - The text from which to remove extra space. | TRIM (text) | TRIM(" string-with-space ") = "string-with-space" |
| 74 | Logical | TRUE | Returns the logical value TRUE. | | TRUE() | TRUE() = TRUE |
| 75 | Formatting | TRUNC | Returns a truncated number based on an (optional) number of digits. | [number] - The number to truncate., [num_digits] - [optional] The precision of the truncation (default is 0). | TRUNC (number, [num_digits]) | TRUNC(4.9) = 4; TRUNC(-3.51, 1) = -3.5 |
| 76 | Text | UPPER | Returns a upper-case version of a given text string. | [text] - The text thatto convert to upper case. | UPPER (text) | UPPER("string-value") = "STRING_VALUE" |
| 77 | Formatting | VALUE | Converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. | [text] - The text value to convert to a number. | VALUE (text) | VALUE("0.5") = 0.5 |
| 78 | Date | WEEKDAY | Takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. | [time] - The date for which you want to get the day of week., [return_type] - [optional] A number representing day of week mapping scheme. Default is 1. | WEEKDAY (time, [return_type]) | WEEKDAY("2020-10-02") = 6 (Friday); |
| 79 | Date | WEEKNUM | Takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday. | [time] - A valid Excel date format., [return_type] - [optional] The day the week begins. Default is 1. | WEEKNUM (time, [return_type]) | WEEKNUM("2020-10-02", 1) = 40; |
| 80 | Date | WORKDAY | Takes a date and returns the nearest working day in the future or past, based on an offset value you provide. | [start_date] - The date from which to start., [days] - The working days before or after start_date., [holidays] - [optional] A list dates that should be considered non-work days. | WORKDAY (start_date, days, [holidays]) | WORKDAY("2020-10-02", 2) = "2020-10-06" |
| 81 | Date | YEAR | Returns the year component of a date as a 4-digit number. | [date] - A valid date. | YEAR (date) | YEAR("2020-10-02") = 2020 |
| 82 | Date | YEARFRAC | Returns a decimal value that represents fractional years between two dates. | [start_date] - The start date., [end_date] - The end date., [basis] - [optional] The type of day count basis to use (see below). | YEARFRAC (start_date, end_date, [basis]) | YEARFRAC("2019-01-01", "2020-10-02") = 1.75277777777778 |