Formulas are expressions that calculate values in cells. Supported formulas and functions in Spreadsheet Editor are consistent with Excel, including:
For examples of how to programmatically use formulas, see the Edit Mode guide's Formula Bar section.
The following formulas are supported:
| Formula | Description |
|---|---|
| ABS | Absolute value of a number. |
| ACOS | Inverse cosine of a value, in radians. |
| ACOSH | Inverse hyperbolic cosine of a number. |
| ADDRESS | Cell reference as a string. |
| AND | Logical `and` operator. |
| ARABIC | Computes the value of a Roman numeral. |
| ASIN | Inverse sine of a value, in radians. |
| ASINH | Inverse hyperbolic sine of a number. |
| ATAN | Inverse tangent of a value, in radians. |
| ATAN2 | Arctangent of a value. |
| ATANH | Inverse hyperbolic tangent of a number. |
| AVERAGE | Numerical average value in a dataset, ignoring text. |
| AVERAGEIF | Average of values depending on criteria. |
| AVERAGEIFS | Average of values depending on multiple criteria. |
| BIN2DEC | Converts a signed binary number to decimal format. |
| BIN2HEX | Converts a binary number to hexadecimal. |
| BIN2OCT | Converts a binary number to octal. |
| BINOM_DIST | Binomial distribution probability. |
| CEILING | Rounds number up to nearest multiple of a factor. |
| CELL | Gets information about a cell. |
| CHAR | Gets character associated with number. |
| CHOOSE | An element from a list of choices based on index. |
| CLEAN | Removes non-printable characters from a piece of text. |
| CODE | Numeric unicode map value of character. |
| COLUMN | Column number of a specified cell. |
| COLUMNS | Number of columns in a specified array or range. |
| COMBIN | Number of combinations from a set of objects. |
| COMPLEX | Creates a complex number. |
| CONCAT | Concatenation of two values. |
| CONCATENATE | Appends strings to one another. |
| CONVERT | Unit conversion for numbers. |
| COS | Cosine of an angle provided in radians. |
| COSH | Hyperbolic cosine of any real number. |
| COUNT | The number of numeric values in dataset. |
| COUNTA | The number of values in a dataset. |
| COUNTBLANK | Number of empty values. |
| COUNTIF | A conditional count across a range. |
| COUNTIFS | Count values depending on multiple criteria. |
| COUNTUNIQUE | Counts number of unique values in a range. |
| COUPDAYBS | Number of days from first coupon to settlement. |
| COUPDAYS | Days in coupon period containing settlement date. |
| COUPNUM | Number of coupons between settlement and maturity. |
| CUMIPMT | Cumulative interest paid over a set of periods. |
| CUMPRINC | Cumulative principal paid over a set of periods. |
| DATE | Converts year/month/day into a date. |
| DATEDIF | Date difference. |
| DATEVALUE | Converts a date string to a date value. |
| DAVERAGE | Average of a set of values from a table-like range. |
| DAY | Day of the month that a specific date falls on. |
| DAYS | Number of days between two dates. |
| DAYS360 | Days between two dates on a 360-day year. |
| DB | Depreciation via declining balance method. |
| DCOUNT | Counts values from a table-like range. |
| DCOUNTA | Counts values and text from a table-like range. |
| DDB | Depreciation via double-declining balance method. |
| DEGREES | Converts an angle value in radians to degrees. |
| DELTA | Compare two numeric values. |
| DEVSQ | The sum of squares of deviations based on a sample. |
| DGET | Single value from a table-like range. |
| DISC | The discount rate of a security based on price. |
| DISCRETESPLINES | Generate sparklines discrete graph. |
| DMAX | Maximum of values from a table-like range. |
| DMIN | Minimum of values from a table-like range. |
| DOLLAR | Formats a number as currency specific to your spreadsheet locale. |
| DOLLARDE | Converts a decimal fraction to decimal value. |
| DOLLARFR | Converts a decimal value to decimal fraction. |
| DPRODUCT | Product of values from a table-like range. |
| DSTDEV | Standard deviation of population sample from table. |
| DSTDEVP | Standard deviation of entire population from table. |
| DSUM | Sum of values from a table-like range. |
| DURATION | Number of periods for an investment to reach a value. |
| DVAR | Variance of population sample from table-like range. |
| DVARP | Variance of a population from a table-like range. |
| EDATE | Date a number of months before/after another date. |
| EFFECT | Annual effective interest rate. |
| EOMONTH | Last day of a month before or after a date. |
| ERFC | Complementary gauss error function of a value. |
| EVEN | Rounds a number up to the nearest even integer. |
| EXACT | Tests whether two strings are identical. |
| EXP | Euler's number, e (~2.718) raised to a power. |
| FACT | Factorial of a number. |
| FACTDOUBLE | "double factorial" of a number. |
| FALSE | Logical value `false`. |
| FILTER | Filters a range based off provided conditions. |
| FIND | First position of string found in text, case-sensitive. |
| FINDB | Position at which a string is first found within text (binary). |
| FISHER | Fisher transformation of a specified value. |
| FISHERINV | Inverse fisher transformation of a specified value. |
| FIXED | Formats number with fixed number of decimal places. |
| FORECAST | Expected y-value based of linear regression. |
| FREQUENCY | The frequency distribution of array. |
| FV | Future value of an annuity investment. |
| FVSCHEDULE | Future value of principal from series of rates. |
| GAMMALN | Logarithm of gamma function. |
| GCD | Greatest common divisor of one or more integers. |
| GEOMEAN | The geometric mean of a dataset. |
| GETPIVOTDATA | Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings. |
| GROWTH | Fits points to exponential growth trend. |
| HARMEAN | The harmonic mean of a dataset. |
| HEX2BIN | Converts a hexadecimal number to binary. |
| HEX2DEC | Converts a hexadecimal number to decimal. |
| HEX2OCT | Converts a hexadecimal number to octal. |
| HLOOKUP | Horizontal lookup |
| HOUR | Hour component of a specific time. |
| HYPGEOMDIST | Hypergeometric distribution probability. |
| IF | Returns value depending on logical expression. |
| IFERROR | Value if it is not an error, otherwise 2nd argument. |
| IMABS | The absolute value of a complex number. |
| IMAGINARY | The imaginary coefficient of a complex number. |
| IMCONJUGATE | The complex conjugate of a number. |
| IMDIV | One complex number divided by another. |
| IMPRODUCT | Result of multiplying a series of complex numbers together. |
| IMREAL | The real coefficient of a complex number. |
| IMSUB | The difference between two complex numbers. |
| IMSUM | Sum of a series of complex numbers. |
| INDEX | Content of cell specified by row and column offset. |
| INDIRECT | A cell reference specified by a string. |
| INT | Rounds number down to nearest integer. |
| INTRATE | Calculates effective interest rate. |
| IPMT | Payment on interest for an investment. |
| IRR | Internal rate of return given periodic cashflows. |
| ISBLANK | Whether the referenced cell is empty. |
| ISDATE | Whether a value is a date. |
| ISERR | Whether a value is an error other than `#n/a`. |
| ISERROR | Whether a value is an error. |
| ISEVEN | Whether the provided value is even. |
| ISFORMULA | Whether a value is a formula. |
| ISLOGICAL | Whether a value is `true` or `false`. |
| ISNA | Whether a value is the error `#n/a`. |
| ISNONTEXT | Whether a value is non-textual. |
| ISNUMBER | Whether a value is a number. |
| ISODD | Whether the provided value is odd. |
| ISOWEEKNUM | ISO week number of the year. |
| ISREF | Whether a value is a valid cell reference. |
| ISTEXT | Whether a value is text. |
| JOIN | Concatenates elements of arrays with delimiter. |
| KURT | Kurtosis of a dataset. |
| LARGE | Nth largest element from a data set. |
| LCM | Least common multiple of one or more integers. |
| LEFT | Substring from beginning of specified string. |
| LEN | Length of a string. |
| LENB | Length of a string in bytes. |
| LINEST | Best-fit linear trend via least-squares. |
| LN | The logarithm of a number, base e (euler's number). |
| LOG | The logarithm of a number with respect to a base. |
| LOG10 | The logarithm of a number, base 10. |
| LOGEST | Best-fit exponential growth curve. |
| LOOKUP | Look up a value. |
| LOWER | Converts a specified string to lowercase. |
| MATCH | Position of item in range that matches value. |
| MAX | Maximum value in a numeric dataset. |
| MAXA | Maximum numeric value in a dataset. |
| MDETERM | Matrix determinant of a square matrix. |
| MDURATION | Modified Macaulay duration. |
| MEDIAN | Median value in a numeric dataset. |
| MIN | Minimum value in a numeric dataset. |
| MINA | Minimum numeric value in a dataset. |
| MINUTE | Minute component of a specific time. |
| MINVERSE | Multiplicative inverse of square matrix. |
| MIRR | Modified internal rate of return. |
| MMULT | The matrix product of two matrices. |
| MOD | Modulo (remainder) operator. |
| MONTH | Month of the year a specific date falls in. |
| MROUND | Rounds a number to the nearest integer multiple. |
| MULTINOMIAL | Multinomial distribution function. |
| N | Argument provided as a number. |
| NA | The `#N/A` error. |
| NETWORKDAYS | Net working days between two provided days. |
| NOMINAL | Annual nominal interest rate. |
| NOT | Returns opposite of provided logical value. |
| NOW | Current date and time as a date value. |
| NPER | Number of payment periods for an investment. |
| NPV | The net present value of an investment based on a series of periodic cash flows and a discount rate. |
| OCT2BIN | Converts an octal number to binary. |
| OCT2DEC | Converts a signed octal number to decimal format. |
| OCT2HEX | Converts an octal number to hexadecimal. |
| ODD | Rounds a number up to the nearest odd integer. |
| OFFSET | A range reference offset relative to a cell. |
| OR | Logical `or` operator. |
| PEARSON | Pearson Product-Moment Correlation Coefficient. |
| PERCENTRANK_INC | Percentage rank (percentile) from 0 to 1 inclusive. |
| PERMUT | Number of permutations from a number of objects. |
| PI | The number pi. |
| PMT | Periodic payment for an annuity investment. |
| POWER | A number raised to a power. |
| PRICE | Price of a security paying periodic interest. |
| PRICEDISC | Price of a discount security. |
| PRICEMAT | Price of security paying interest at maturity. |
| PROB | Probability values lie in a range. |
| PRODUCT | Result of multiplying a series of numbers together. |
| PROPER | Capitalizes each word in a specified string. |
| PV | Present value of an annuity investment. |
| QUOTIENT | One number divided by another. |
| RADIANS | Converts an angle value in degrees to radians. |
| RAND | A random number between 0 inclusive and 1 exclusive. |
| RANDARRAY | Returns a grid of random numbers. |
| RANDBETWEEN | Random integer between two values, inclusive. |
| RATE | Interest rate of an annuity investment. |
| RECEIVED | Amount received at maturity for a security. |
| REGEXEXTRACT | Extracts matching substrings with regular expression. |
| REGEXMATCH | Whether a piece of text matches regular expression. |
| REGEXREPLACE | Replaces text with regular expressions. |
| REPLACE | Replaces part of a text string with different text. |
| REPT | Specified text repeated a number of times. |
| RIGHT | A substring from the end of a specified string. |
| ROMAN | Formats a number in Roman numerals. |
| ROUND | Rounds a number according to standard rules. |
| ROUNDDOWN | Rounds down a number. |
| ROUNDUP | Rounds up a number. |
| ROW | Row number of a specified cell. |
| ROWS | Number of rows in a specified array or range. |
| RSQ | Square of the correlation coefficient. |
| SEARCH | First position of string found in text, ignoring case. |
| SECOND | Second component of a specific time. |
| SEQUENCE | Returns a grid of sequential numbers. |
| SERIASESSUM | Sum of a power series. |
| SIGN | Sign of a provided number (+/-/0). |
| SIN | Sine of an angle provided in radians. |
| SINH | Hyperbolic sine of any real number. |
| SKEW | Skewness of a dataset. |
| SLN | Depreciation of asset using the straight-line method. |
| SLOPE | Slope of line from linear regression of data. |
| SMALL | Nth smallest element in a data set. |
| SORT | Sorts rows of range by specified column. |
| SQRT | Positive square root of a positive number. |
| SQRTPI | Square root of the product of pi and number. |
| STANDARDIZE | Normalized equivalent of a random variable. |
| STDEVA | Standard deviation of sample (text as 0). |
| STDEVP | Standard deviation of an entire population. |
| STEYX | Standard error of predicted y-values in regression. |
| SUBSTITUTE | Replaces existing text with new text in a string. |
| SUBTOTAL | Subtotal for a range using a specific function. |
| SUM | Sum of a series of numbers and/or cells. |
| SUMIF | A conditional sum across a range. |
| SUMIFS | Sums a range depending on multiple criteria. |
| SUMPRODUCT | Sum of products of elements in two arrays. |
| SUMSQ | Sum of squares. |
| SUMX2MY2 | Sum of the differences of squares. |
| SUMX2PY2 | Sum of the sums of squares. |
| SUMXMY2 | Sum of the squares of differences. |
| T | String arguments as text. |
| TAN | Tangent of an angle provided in radians. |
| TANH | Hyperbolic tangent of any real number. |
| TBILLEQ | Equivalent rate of return for a Treasury bill. |
| TBILLPRICE | Price of US treasury bill. |
| TBILLYIELD | The yield of a US treasury bill based on price. |
| TEXT | Formats a number into text. |
| TIME | Converts hour/minute/second into a time. |
| TIMEVALUE | onverts a time string into its serial number representation. |
| TO_DATE | Converts a provided number to a date. |
| TO_DOLLARS | Converts a provided number to a dollar value. |
| TO_PERCENT | Converts a provided number to a percentage. |
| TO_PURE_NUMBER | Converts any numeric value to a pure number. |
| TO_TEXT | Converts a provided numeric value to a text value. |
| TODAY | Current date as a date value. |
| TRANSPOSE | Transposes the rows and columns of an array. |
| TREND | Fits points to linear trend derived via least-squares. |
| TRIM | Removes space characters. |
| TRIMMEAN | Mean of a dataset excluding high/low ends. |
| TRUE | Logical value `true`. |
| TRUNC | Truncates a number. |
| TYPE | Get the type of a value. |
| UNIQUE | Unique rows in the provided source range. |
| UPPER | Converts a specified string to uppercase. |
| VALUE | Converts a date/time/number string into a number. |
| VARA | Variance of sample (text as 0). |
| VARPA | Variance of entire population (text as 0). |
| VLOOKUP | Vertical lookup. |
| WEEKDAY | Day of the week of the date provided (as number). |
| WEEKNUM | Week number of the year. |
| WORKDAY | Number of working days from start date. |
| XIRR | Internal rate of return given non-periodic cashflows. |
| XNPV | Net present value given non-periodic cashflows. |
| YEAR | Year specified by a given date. |
| YEARFRAC | Exact number of years between two dates. |
| YIELD | Annual yield of a security paying periodic interest. |
| YIELDDISC | Annual yield of a discount security. |
Did you find this helpful?
Trial setup questions?
Ask experts on DiscordNeed other help?
Contact SupportPricing or product questions?
Contact Sales