Function reference

All 159 functions supported by the Cell by Cell engine, in plain English.

Math

• ABS: The absolute value: the number without its sign.
• CEILING: Rounds a number up to the nearest multiple (away from zero when both are negative).
• CEILING.MATH: Rounds a number up to the nearest multiple; mode rounds negative numbers away from zero.
• EVEN: Rounds away from zero to the next even integer.
• EXP: e raised to a power.
• FACT: Factorial: 1 × 2 × … × n.
• FLOOR: Rounds a number down to the nearest multiple (toward zero when both are negative).
• FLOOR.MATH: Rounds a number down to the nearest multiple; mode rounds negative numbers toward zero.
• GCD: Greatest common divisor.
• INT: Rounds down to the nearest whole number.
• LCM: Least common multiple.
• LN: Natural logarithm.
• LOG: Logarithm to a base (default 10).
• LOG10: Base-10 logarithm.
• MOD: The remainder after division.
• MROUND: Rounds to the nearest multiple of a number.
• ODD: Rounds away from zero to the next odd integer.
• PI: The number π (3.14159…).
• POWER: Raises a number to a power (same as ^).
• PRODUCT: Multiplies all the numbers together.
• QUOTIENT: The whole-number part of a division.
• RAND: A random number between 0 and 1. Changes on every edit.
• RANDBETWEEN: A random whole number between two limits.
• ROUND: Rounds a number to a given number of digits.
• ROUNDDOWN: Rounds a number towards zero.
• ROUNDUP: Rounds a number away from zero.
• SIGN: 1 for positive numbers, -1 for negative, 0 for zero.
• SQRT: Square root.
• SUM: Adds up numbers, cells and ranges.
• SUMIF: Adds the cells that meet one condition.
• SUMIFS: Adds the cells that meet every one of several conditions.
• SUMPRODUCT: Multiplies matching items in arrays, then adds the results.
• SUMSQ: Adds the squares of the numbers.
• TRUNC: Cuts off the decimal part of a number.

Logical

• AND: TRUE only if every condition is TRUE.
• FALSE: The logical value FALSE.
• IF: Returns one value if a condition is TRUE and another if it is FALSE.
• IFERROR: Returns a fallback value if a formula results in an error.
• IFNA: Returns a fallback value only for #N/A errors.
• IFS: Checks conditions in order and returns the value for the first TRUE one.
• LET: Names intermediate results so a formula is easier to read.
• NOT: Flips TRUE to FALSE and FALSE to TRUE.
• OR: TRUE if at least one condition is TRUE.
• SWITCH: Compares a value against a list and returns the matching result.
• TRUE: The logical value TRUE.
• XOR: TRUE if an odd number of conditions are TRUE.

Statistics

• AVERAGE: The arithmetic mean of the numbers.
• AVERAGEIF: Averages the cells that meet a condition.
• AVERAGEIFS: Averages the cells that meet every one of several conditions.
• COUNT: Counts cells that contain numbers.
• COUNTA: Counts cells that are not empty.
• COUNTBLANK: Counts empty cells in a range.
• COUNTIF: Counts the cells that meet a condition.
• COUNTIFS: Counts the rows that meet every one of several conditions.
• LARGE: The k-th largest value (k = 1 is the maximum).
• MAX: The largest number.
• MAXIFS: The largest value among cells that meet conditions.
• MEDIAN: The middle number of a sorted list.
• MIN: The smallest number.
• MINIFS: The smallest value among cells that meet conditions.
• MODE: The most frequently occurring number.
• PERCENTILE: The value below which a given percentage of the data falls.
• PERCENTILE.EXC: The value below which a percentage of the data falls, excluding 0% and 100%.
• QUARTILE: A quartile of the data (0 = min, 2 = median, 4 = max).
• QUARTILE.EXC: A quartile of the data (1, 2 or 3), using the exclusive method.
• RANK: The position of a number in a list (1 = largest by default).
• SMALL: The k-th smallest value (k = 1 is the minimum).
• STDEV: Standard deviation of a sample.
• STDEV.P: Standard deviation of an entire population.
• VAR: Variance of a sample.
• VAR.P: Variance of an entire population.

Text

• CHAR: The character for a character code.
• CLEAN: Removes non-printable characters.
• CODE: The character code of the first character.
• CONCAT: Joins text from several values or ranges.
• CONCATENATE: Joins several text values (older version of CONCAT).
• EXACT: TRUE if two text values are exactly the same, including case.
• FIND: Position of one text inside another (case-sensitive).
• LEFT: The first characters of a text value.
• LEN: The number of characters in a text value.
• LOWER: Converts text to lowercase.
• MID: Characters from the middle of a text value.
• PROPER: Capitalises The First Letter Of Each Word.
• REPLACE: Replaces characters at a given position.
• REPT: Repeats text a number of times.
• RIGHT: The last characters of a text value.
• SEARCH: Position of one text inside another (not case-sensitive, allows wildcards).
• SUBSTITUTE: Replaces specific text with new text.
• T: Returns the value if it is text, otherwise empty text.
• TEXT: Formats a number as text using a format code.
• TEXTAFTER: The text that comes after a delimiter.
• TEXTBEFORE: The text that comes before a delimiter.
• TEXTJOIN: Joins text with a separator between each item.
• TRIM: Removes extra spaces, leaving single spaces between words.
• UPPER: Converts text to UPPERCASE.
• VALUE: Converts text that looks like a number into a number.

Lookup

• CHOOSE: Picks a value from a list by its position.
• COLUMN: The column number of a reference (A = 1).
• COLUMNS: How many columns a range has.
• HLOOKUP: Finds a value in the first row of a table and returns a value from the same column.
• INDEX: The value at a given row and column of a range.
• MATCH: The position of a value in a row or column.
• ROW: The row number of a reference (or of the current cell).
• ROWS: How many rows a range has.
• VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.
• XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
• XMATCH: The position of a value in a list, with exact match by default.

Financial

• CUMIPMT: Total interest paid between two payment numbers.
• FV: The future value of an investment with regular payments.
• IPMT: The interest part of one loan payment.
• IRR: Internal rate of return for a series of cash flows.
• NPER: How many payment periods are needed to pay off a loan.
• NPV: Net present value of future cash flows at a discount rate.
• PMT: The payment for a loan with constant payments and a constant interest rate.
• PPMT: The principal (pay-down) part of one loan payment.
• PV: The present value: what a series of future payments is worth now.
• RATE: The interest rate per period of a loan or investment.
• SLN: Straight-line depreciation: the same loss of value every year.

Date & Time

• DATE: Builds a date from a year, month and day.
• DATEDIF: The difference between two dates in years, months or days.
• DATEVALUE: Converts a date written as text into a date serial number.
• DAY: The day of the month (1-31).
• DAYS: The number of days between two dates.
• EDATE: The same day a number of months before or after a date.
• EOMONTH: The last day of the month, a number of months away.
• HOUR: The hour of a time (0-23).
• ISOWEEKNUM: The ISO 8601 week number: weeks start on Monday, and week 1 contains the first Thursday of the year.
• MINUTE: The minute of a time (0-59).
• MONTH: The month of a date (1-12).
• NETWORKDAYS: Working days (Monday–Friday) between two dates, inclusive.
• NOW: The current date and time.
• SECOND: The second of a time (0-59).
• TIME: Builds a time from hours, minutes and seconds.
• TIMEVALUE: Converts a time written as text into a fraction of a day.
• TODAY: Today's date. Updates automatically.
• WEEKDAY: The day of the week as a number.
• WEEKNUM: The week number of the year.
• WORKDAY: The date a number of working days before or after a date.
• YEAR: The year of a date.
• YEARFRAC: The fraction of a year between two dates, using a day-count basis.

Dynamic arrays

• FILTER: Keeps only the rows that meet a condition. The result spills.
• SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.
• SORT: Sorts a range. The result spills.
• SORTBY: Sorts a range by the values in another range.
• TEXTSPLIT: Splits text into separate cells at a delimiter. The result spills.
• TRANSPOSE: Flips rows into columns and columns into rows.
• UNIQUE: Removes duplicates from a list. The result spills.

Information

• ISBLANK: TRUE if the cell is empty.
• ISERR: TRUE if the value is any error except #N/A.
• ISERROR: TRUE if the value is any error.
• ISEVEN: TRUE if the number is even.
• ISLOGICAL: TRUE if the value is TRUE or FALSE.
• ISNA: TRUE if the value is the #N/A error.
• ISNONTEXT: TRUE if the value is not text.
• ISNUMBER: TRUE if the value is a number (dates count too).
• ISODD: TRUE if the number is odd.
• ISTEXT: TRUE if the value is text.
• N: Converts a value to a number (text becomes 0).
• NA: Returns the #N/A error on purpose.