Using functions and formulas with Zoom Sheets

DAYS

DAYS(end_date, start_date)

Returns the number of days between two dates.

YEAR

YEAR(date value)

Returns the year corresponding to the given date.

MONTH

MONTH(date_value)

Returns the month of a specific date in numeric format.

WEEKDAY

WEEKDAY(date_value, type)

Returns a number corresponding to the day of the week for a given date.

TODAY

TODAY()

Return the current date in date format.

TIMEVALUE

TIMEVALUE(time_string)

Returns the fractional representation of the time based on a 24-hour day.

TIME

TIME(hour, minute, second)

Convert the given hours, minutes, and seconds to time.

SECOND

SECOND(time_value)

Returns the seconds component of a specific time in numeric format.

NOW

NOW()

Returns the current date and time in date value format.

NETWORKDAYS

NETWORKDAYS(start_date, end_date, holidays)

Returns the number of net working days between the two dates provided.

WEEKNUM

WEEKNUM(date, [type])

Returns a number representing which week of the year the given date falls in.

MINUTE

MINUTE(time_value)

Returns the minute portion of a specific time in numeric format.

HOUR

HOUR(time_value)

Returns the hour component of a specific time in numeric format.

EOMONTH

EOMONTH(start_date, months)

Returns the last day of a month that is a specified number of months before or after another date.

EDATE

EDATE(start_date, months)

Returns a date that is a number of months before/after another date.

DAYS360

DAYS360(start_date, end_date, method)

Returns the difference between two dates based on a 360-day year (used for interest calculations).

DAY

DAY(date_value)

Returns the day of the month for a specific date in numeric format.

DATEVALUE

DATEVALUE(date_string)

Converts a date string in a known format to a date value.

DATEDIF

DATEDIF(start_date, end_date, unit)

Calculate the number of days, months, or years between two dates.

DATE

DATE(year, month, day)

Converts the provided year, month, and day into a date.

YEARFRAC

YEARFRAC(start_date, end_date, [basis])

Calculates and returns the number of years (including fractional parts) between two dates using the specified day count convention.

WORKDAY

WORKDAY(start_date, num_days, [holidays])

Calculate the end date after a given number of working days.

ISOWEEKNUM

ISOWEEKNUM(date)

Returns the ISO week number of the year for a specified date.

NETWORKDAYS.INTL

NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

Returns the net working days between two given dates (excluding specified weekends and holidays).

RANK.AVG

RANK.AVG(value, data, [is_ascending])

Returns the rank of a specified value in a dataset. If multiple identical values exist in the dataset, it returns the average of their average ranks.

TDIST

TDIST(x, degrees_freedom, tails)

Given an input value (x), calculate the probability of the Student's t-distribution.

RANK.EQ

RANK.EQ(value, data, [is_ascending])

Returns the rank of a specified value in a dataset. If multiple identical values exist in the dataset, it returns the highest rank among them.

PERCENTRANK.INC

PERCENTRANK.INC(data, value, [significant_digits])

Returns the percentile rank of a specified value in a dataset as a percentage (between 0 and 1, inclusive).

PERCENTRANK.EXC

PERCENTRANK.EXC(data, value, [significant_digits])

Returns the percentile rank of a specified value in a dataset as a percentage (between 0 and 1, excluding both endpoints).

AVERAGEIFS

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Returns the specified average based on multiple conditions.

AVERAGEIF

AVERAGEIF(criteria_range, criterion, [average_range])

Average value filtered by individual item conditions.

LOGNORM

LOGNORM(x, mean, standard_dev)

Returns the inverse of the lognormal cumulative distribution function for specified parameters, given the logarithm and standard deviation.

MODE

MODE(value 1, value 2)

Return the value that appears most frequently in the dataset.

WEIBULL

WEIBULL(x, shape, scale, cumulative)

Given the shape and scale, returns the value of the Weibull distribution function (Weibull cumulative distribution function).

COUNT

COUNT(value 1, [value 2, ...])

Returns the quantity contained in the dataset.

COUNTA

COUNTA(value1, [value2, ...])

Returns the count of non-null values in the dataset.

AVEDEV

AVEDEV(value 1, value 2)

Calculate the average of the absolute differences between the data and the dataset mean.

AVERAGE

AVERAGE(value 1, value 2)

Returns the arithmetic mean of the dataset, with text values treated as zero.

AVERAGEA

AVERAGEA(value 1, value 2)

Returns the arithmetic mean of the dataset.

VARA

VARA(value 1, value 2)

An estimate of variance calculated based on samples, treating text as 0.

BINOMDIST

BINOMDIST(number_s, trials, probability_s, cumulative)

For experiments with a given sample size, where each trial has only two possible outcomes, calculate the probability of a specified number of successes (or greater than or less than that number) occurring with equal probability.

CONFIDENCE

CONFIDENCE(alpha, standard_dev, size)

Calculate half the width of the confidence interval for a normal distribution.

CORREL

CORREL(data_y, data_x)

Calculate the Pearson correlation coefficient r for the given dataset.

COVAR

COVAR(data_y, data_x)

Calculate the covariance of the dataset.

CRITBINOM

CRITBINOM(trials, probability_s, alpha)

Calculates the minimum value for which the cumulative binomial distribution is greater than or equal to a specified value.

DEVSQ

DEVSQ(value 1, value 2)

Calculate the sum of squares of arithmetic deviations based on the sample.

EXPONDIST

EXPONDIST(x, lambda, cumulative)

Returns the value of the exponential distribution function with the specified Lambda for the specified value.

FISHER

FISHER(number)

Returns the Fisher transformation for a specified value.

FISHERINV

FISHERINV(value)

Returns the inverse of the Fisher transformation for a specified value.

FORECAST

FORECAST(x, data_y, data_x)

Based on linear regression of the dataset, calculate the predicted y value for a specified x.

GEOMEAN

GEOMEAN(value 1, value 2)

Calculate the geometric mean of the dataset.

HARMEAN

HARMEAN(value 1, value 2)

Calculate the harmonic mean of the dataset.

HYPGEOMDIST

HYPGEOMDIST(number_of_successes, number_of_trials, population_successes, population_size)

Given the success rate in a total sample population, calculate the probability of detecting a specified number of successes in a specified number of tests under the condition that samples are not replaced after each test.

INTERCEPT

INTERCEPT(data_y, data_x)

Calculate the y-value of the linear regression equation's intercept point on the Y-axis (x=0) for the dataset.

KURT

KURT(value 1, value 2)

Calculate the kurtosis of the dataset, which identifies the shape of the data distribution, particularly whether it is peaked, sharp, or flat.

LARGE

LARGE(data, n)

Return the nth largest element in the dataset, where n is specified by the user.

ZTEST

ZTEST(data, value, standard_deviation)

Returns the two-tailed P-value of a standard Z-test.

LOGNORMDIST

LOGNORMDIST(x, mean, standard_dev)

Given the mean and standard deviation, returns the lognormal cumulative distribution function for the specified parameters.

MAX

MAX(value 1, value 2)

Returns the maximum value in a dataset collection.

MAXA

MAXA(value 1, value 2)

Returns the maximum value in the dataset.

MEDIAN

MEDIAN(value 1, value 2)

Returns the median value in a data set.

MIN

MIN(value 1, value 2)

Returns the minimum value in the dataset collection.

MINA

MINA(value 1, value 2)

Returns the minimum value in the dataset.

NEGBINOMDIST

NEGBINOMDIST(number_f, number_s, probability_s)

Given a success probability and constant preference, calculate the probability of encountering a specified number of failures before a specified number of successes.

NORMDIST

NORMDIST(x, mean, standard_dev, cumulative)

Given the parameter value, mean, and standard deviation, returns the value of the normal distribution function (or normal cumulative distribution function).

NORMINV

NORMINV(x, mean, standard_deviation)

Given parameter values, mean and standard deviation, returns the inverse of the normal distribution function.

NORMSDIST

NORMSDIST(x)

Returns the standard normal cumulative distribution function value for a specified value.

NORMSINV

NORMSINV(x)

Returns the inverse of the standard normal cumulative distribution function for a specified value.

PEARSON

PEARSON(data_y, data_x)

Calculate the Pearson correlation coefficient r for the given dataset.

PERCENTILE

PERCENTILE(data, percentile)

Returns the value at a specified percentile of the dataset.

TRIMMEAN

TRIMMEAN(data, exclude_proportion)

Calculate the mean of the remaining data after excluding portions of data from both the high and low ends of the dataset.

PERMUT

PERMUT(n, k)

Given a total quantity, returns the number of permutations for selecting a specified number of objects from a set of objects, taking order into account.

POISSON

POISSON(x, mean, cumulative)

Given the parameter and mean, returns the value of the Poisson distribution function (or Poisson cumulative distribution function).

PROB

PROB(data, probability, lower_limit, upper_limit)

Given a set of values and corresponding probabilities, calculate the probability that the value of a random variable falls within a specified number of expected values.

QUARTILE

QUARTILE(data, quartile)

Returns the quartile value of a specified quartile for a data subset.

RANK

RANK(value, data, ascending)

Returns the rank of a specified value in a dataset.

RSQ

RSQ(data_y, data_x)

Calculate the square of r, where r is the Pearson correlation coefficient.

SKEW

SKEW(value 1, value 2)

Calculate the skewness of the dataset, which describes the symmetry of the dataset relative to the arithmetic mean.

SLOPE

SLOPE(data_y, data_x)

Calculation of the slope of the linear regression line through the dataset.

SMALL

SMALL(data, n)

Return the nth smallest element in the dataset, where n is specified by the user.

STANDARDIZE

STANDARDIZE(value, mean, standard_dev)

Given the values of the mean and standard deviation, calculate a value that represents the normalized value of a normal distribution.

STEYX

STEYX(data_y, data_x)

For each x in the regression analysis of the dataset, calculate the standard error of its corresponding y value.

VARPA

VARPA(value 1, value 2)

Calculates variance based on the entire sample population, treating text as 0.

TTEST

TTEST(data 1, data 2, tails, type)

Returns the probability associated with a t-test, used to determine whether two samples (individual or population) are likely to come from the same hypothetical population.

F.DIST

F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative)

Given input x, calculate the left-tail F probability distribution (cumulative probability) for two datasets, also known as the Fisher-Snedecor distribution or Snedecor F distribution.

T.INV.2T

T.INV.2T(probability, degrees_freedom)

A function that calculates the two-tailed inverse TDIST value.

T.INV

T.INV(probability, degrees_of_freedom)

Calculates the inverse of the one-tailed reverse TDIST value.

VAR

VAR(value 1, value 2)

Calculate the sample variance estimate based on the sample.

PERCENTRANK

PERCENTRANK(data, value, [significance])

Returns the percentile rank of a specified value in a dataset.

F.DIST.RT

F.DIST.RT(x, degrees_freedom1, degrees_freedom2)

Given input x, compute the right-tail F probability distribution (cumulative probability) for two datasets, also known as the Fisher-Snedecor distribution or Snedecor F distribution.

TINV

TINV(probability, degrees_of_freedom)

A function that calculates the two-tailed inverse TDIST value.

FDIST

FDIST(x, degrees_of_freedom_1, degrees_of_freedom_2)

Given input x, compute the right-tail F probability distribution (cumulative probability) for two datasets, also known as the Fisher-Snedecor distribution or Snedecor F distribution.

STDEV

STDEV(value 1, value 2)

Calculates an estimate of standard deviation based on a sample, treating text as 0.

STDEVA

STDEVA(value 1, value 2)

Calculates the standard deviation of a dataset based on a sample, treating text as 0.

STDEVP

STDEVP(value 1, value 2)

Calculates a standard deviation based on a sample.

STDEVPA

STDEVPA(value 1, value 2)

Calculates an estimate of standard deviation based on a sample population, treating text as 0.

VARP

VARP(value 1, value 2)

Calculates the variance based on the entire population.

ISBLANK

ISBLANK(value)

Check if the referenced cell is empty.

ERROR.TYPE

ERROR.TYPE(reference)

Returns the number corresponding to an error value in other cells.

NA

NA()

Returns a "no valid value" error, i.e., "#N/A".

ISERR

ISERR(value)

Check if a value is an error value other than #N/A.

ISERROR

ISERROR(value)

Check whether a value is an error value.

ISFORMULA

ISFORMULA(cell)

Check whether the referenced cell contains a formula.

ISLOGICAL

ISLOGICAL(value)

Check whether a value is TRUE or FALSE.

ISNA

ISNA(value)

Check whether a value is the error value #N/A.

ISNONTEXT

ISNONTEXT(value)

Check whether a value is non-text.

ISNUMBER

ISNUMBER(value)

Check whether a value is a number.

ISREF

ISREF(value)

Check whether a value is a valid cell reference.

ISTEXT

ISTEXT(value)

Check whether a value is text.

N

N(value)

Returns the argument provided in numeric form.

IMSUB

IMSUB(first number, second number)

Returns the difference between two complex numbers.

IMREAL

IMREAL(complex_number)

Returns the real coefficients of a complex number.

IMPRODUCT

IMPRODUCT(number 1, [number 2, ...])

Returns the result of multiplying a set of complex numbers.

IMDIV

IMDIV(dividend, divisor)

Returns the result of dividing one complex number by another.

HEX2OCT

HEX2OCT(signed hexadecimal number, significant digits)

Convert a signed hexadecimal number to signed octal format.

OCT2DEC

OCT2DEC(signed_octal_number)

Convert a signed octal number to decimal format.

IMCONJUGATE

IMCONJUGATE(number)

Returns the complex conjugate of a value.

COMPLEX

COMPLEX(real_num, i_num, [suffix])

Create a complex number from the given real and imaginary coefficients.

HEX2DEC

HEX2DEC(signed hexadecimal number)

Convert a signed hexadecimal number to decimal format.

DELTA

DELTA(number 1, [number 2])

Compares two numerical values and returns 1 if they are equal.

DEC2OCT

DEC2OCT(decimal_number, significant_digits)

Convert a decimal number to signed octal format.

DEC2BIN

DEC2BIN(decimal_number, significant_digits)

Convert a decimal number to signed binary format.

BIN2OCT

BIN2OCT(signed binary number, significant digits)

Convert a signed binary number to signed octal format.

BIN2HEX

BIN2HEX(signed binary number, significant digits)

Convert a signed binary number to signed hexadecimal format.

BIN2DEC

BIN2DEC(signed binary number)

Convert a signed binary number to decimal format.

IMABS

IMABS(number)

Returns the absolute value of a complex number.

IMSUM

IMSUM(value 1, [value 2, ...])

Returns the sum of a set of complex numbers.

OCT2BIN

OCT2BIN(signed octal number, significant digits)

Convert a signed octal number to signed binary format.

IMAGINARY

IMAGINARY(complex_number)

Returns the imaginary coefficient of a complex number.

HEX2BIN

HEX2BIN (signed hexadecimal number, [significant digits])

Convert signed hexadecimal numbers to signed binary format.

OCT2HEX

OCT2HEX(signed_octal_number, [significant_digits])

Convert a signed octal number to signed hexadecimal format.

DEC2HEX

DEC2HEX(decimal_number, [places])

Convert a decimal number to signed hexadecimal format.

SUMIFS

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Calculate the sum based on multiple condition filtering and matching.

COUNTIFS

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Calculate the number of cells that match based on multiple conditional sum criteria.

PRODUCT

PRODUCT(number 1, number 2)

Returns the result of multiplying a set of numbers together.

QUOTIENT

QUOTIENT(dividend, divisor)

Returns the result of dividing one number by another number.

COSH

COSH(number)

Returns the hyperbolic cosine of a given real number.

POWER

POWER(base, exponent)

Returns the specified power of a number.

PI

PI()

Returns the value of PI as a constant with 14 decimal places.

ODD

ODD(number)

Round the value up to the nearest odd integer.

MROUND

MROUND(number, multiple)

Round the numerical value to the nearest integer that is a multiple of one.

MOD

MOD(dividend, divisor)

Returns the result of the modulo operation, which is the remainder after division.

LOG10

LOG10(number)

Returns the base-10 logarithm of a number.

LOG

LOG(number, base)

Returns the logarithm of a number to a specified base.

LN

LN(number)

Returns the logarithm of a number to base e (approximately 2.718).

LCM

LCM(number 1, number 2)

Returns the least common multiple of one or more integers.

ISODD

ISODD(value)

Check whether the provided value is an odd number.

ISEVEN

ISEVEN(value)

Check whether the provided numerical value is an even number.

COUNTBLANK

COUNTBLANK(range)

Returns the number of empty cells in a given range.

ATAN2

ATAN2(x, y)

The angle between the x-axis and the line connecting the origin point (0,0) to the specified coordinate point (x,y), based on the position of the coordinate point.

ATANH

ATANH(number)

Returns the inverse hyperbolic tangent of a number.

MULTINOMIAL

MULTINOMIAL(number1, number2)

Returns the value of the factorial of the sum of the parameters divided by the product of the factorials of each parameter.

ASIN

ASIN(number)

Returns the arcsine of a numeric value, expressed in radians.

ACOSH

ACOSH(number)

Returns the inverse hyperbolic cosine of a number.

ACOS

ACOS(value)

Returns the arccosine of a numeric value, expressed in radians.

ABS

ABS(numeric_value)

Returns the absolute value of a number.

ATAN

ATAN(number)

Returns the arctangent of a numeric value in radians.

CEILING

CEILING(number, significance)

Rounds a number up to the nearest integer or to the nearest multiple of specified significance.

COMBIN

COMBIN(n, k)

Given the total number of objects in a set and the number of objects to be selected, return how many different selection methods there are.

TRUNC

TRUNC(number, digits)

Truncate the portion beyond the specified value and round the integer to the specified significant digits.

TANH

TANH(number)

Returns the hyperbolic tangent of a given real number.

TAN

TAN(angle)

Given an angle (expressed in radians), returns its tangent value.

SUMSQ

SUMSQ(value 1, value 2)

Returns the sum of the squares of a set of numbers and/or cells.

COS

COS(angle)

Returns the cosine of the given angle (angle expressed in radians).

SUMIF

SUMIF(range, criteria, sum_range)

Returns the sum of values within a specified range that meet the criteria.

SUM

SUM(number 1, number 2)

Returns the sum of a set of numbers and/or cells.

SUBTOTAL

SUBTOTAL(function_code, range 1, range 2)

Returns a subtotal of a series of numeric cells using the specified summary function.

SQRTPI

SQRTPI(number)

Returns the positive square root of the product of PI and the specified positive number.

SQRT

SQRT(number)

Returns the positive square root of a positive number.

SINH

SINH(number)

Returns the hyperbolic sine of a given real number.

SIN

SIN(angle)

Given an angle (expressed in radians), returns its sine value.

SIGN

SIGN(number)

Given a numerical value, if it is negative return -1; if it is positive return 1; if it is zero return 0.

SERIESSUM

SERIESSUM(x, n, m, a)

Given variables x, n, m, and a, return the sum of the power series a₁xⁿ + a₂x^(n+m) + ... + aᵢx^(n+(i-1)m), where i is the number of terms in the range a.

ROUNDUP

ROUNDUP(number, num_digits)

Round numerical values to the specified number of decimal places, always rounding up.

ROUNDDOWN

ROUNDDOWN(number, num_digits)

Round numerical values to the specified number of decimal places, always rounding down.

ROUND

ROUND(number, digits)

Round a number to a specified number of decimal places, truncating the portion after the specified decimal position.

RANDBETWEEN

RANDBETWEEN(lower_bound, upper_bound)

Returns a random number between two integers (including both integers).

INT

INT(value)

Rounds a number down to the nearest integer less than or equal to it.

GCD

GCD(number 1, number 2)

Returns the greatest common divisor of one or more integers.

GAMMALN

GAMMALN(number)

Returns the logarithm of the gamma function Γ (gamma number) for a specified numeric value.

FLOOR

FLOOR(number, significance)

Round a number down to the nearest integer that is a multiple of the specified factor.

FACTDOUBLE

FACTDOUBLE(number)

Returns the "double factorial" of a number.

FACT

FACT(number)

Returns the factorial of a number.

EXP

EXP(exponent)

Returns the specified power of the number e (≈2.718).

EVEN

EVEN(number)

Round the value up to the nearest even integer.

ERFC

ERFC(z)

Returns the complementary error function value.

DEGREES

DEGREES(angle)

Converts an angle value expressed in radians to degrees.

COUNTUNIQUE

COUNTUNIQUE(value 1, value 2)

Calculate the number of unique values in a column for specified values and ranges.

RAND

RAND()

Returns a random number between 0 and 1 (inclusive of 0 but exclusive of 1).

RADIANS

RADIANS(angle)

Converts an angle value expressed in degrees to radians.

COUNTIF

COUNTIF(range, criteria)

Returns the count of values within a range that meet the specified conditions.

ASINH

ASINH(value)

Returns the inverse hyperbolic sine of a number.

DAVERAGE

DAVERAGE(database, field, criteria)

Returns the average of a set of values selected from a database table-formatted array or range using an SQL-style query.

DCOUNTA

DCOUNTA(database, field, criteria)

Counts the number of numeric and text values selected from a database table-formatted array or range using an SQL-style query.

DGET

DGET(database, field, criteria)

Use SQL-style queries to return a single value from a database table format array or range.

DMAX

DMAX(database, field, criteria)

Returns the maximum value selected from a database table-formatted array or range using an SQL-style query.

DMIN

DMIN(database, field, criteria)

Returns the minimum value selected from a database table formatted array or range using an SQL-style query.

DCOUNT

DCOUNT(database, field, criteria)

Count the number of values that are selected from a database table-formatted array or range using SQL-style queries.

DSTDEV

DSTDEV(database, field, criteria)

Returns the standard deviation of a sample selected from a database table-formatted array or range using a SQL-style query.

DSTDEVP

DSTDEVP(database, field, criteria)

Returns the standard deviation of a sample population that is selected from a database table-formatted array or range using an SQL-style query.

DSUM

DSUM(database, field, criteria)

Returns the sum of a set of values selected from a database table-formatted array or range using an SQL-style query.

DVAR

DVAR(database, field, criteria)

Returns the variance of a sample that is selected from a database table-formatted array or range using a SQL-style query.

DVARP

DVARP(database, field, criteria)

Returns the variance of a sample population, which is selected from a database table-formatted array or range using a SQL-style query.

DPRODUCT

DPRODUCT(database, field, criteria)

Returns the product of a set of values selected from a database table-formatted array or range using a SQL-style query.

MDETERM

MDETERM(square_matrix)

Specifies an array or range and returns the determinant value of the corresponding square matrix.

LOGEST

LOGEST(known_data_y, known_data_x, b, verbose)

Given partial data from an exponential growth curve, calculate the parameters of the ideal exponential growth curve that best fits the data.

LINEST

LINEST(known_data_y, known_data_x, b, verbose)

Given partial data of a linear trend, use the least squares method to calculate the parameters of the ideal linear trend.

GROWTH

GROWTH(known_data_y, known_data_x, new_data_x, b)

Given partial data of an exponential growth trend, fit an ideal exponential growth trend and/or predict other data values.

FREQUENCY

FREQUENCY(data, bins)

Calculate the frequency distribution of a single-column array within specified categories.

TREND

TREND(known_data_y, known_data_x, new_data_x, b)

Given partial data of a linear trend, use the least squares method to fit an ideal linear trend and/or predict more result values.

TRANSPOSE

TRANSPOSE(array or range)

Transposes the rows and columns of an array or cell range.

SUMXMY2

SUMXMY2(array_x, array_y)

Calculate the sum of squares of the differences between corresponding values in two arrays.

SUMX2PY2

SUMX2PY2(array_x, array_y)

Calculate the sum of the squares of corresponding values in two arrays.

SUMX2MY2

SUMX2MY2(array_x, array_y)

Calculate the sum of squared differences between corresponding values in two arrays.

SUMPRODUCT

SUMPRODUCT(array 1, array 2)

Calculates the sum of the products of corresponding elements in two arrays or ranges of the same size.

MMULT

MMULT(matrix 1, matrix 2)

Calculates the matrix product of two matrices corresponding to the given array or range.

MINVERSE

MINVERSE(square_matrix)

Returns the inverse matrix of the square matrix corresponding to the given array or range.

CLEAN

CLEAN(text)

Returns the text after removing non-printable ASCII characters.

FINDB

FINDB(search_for, text_to_search, [starting_at])

Returns the position of the first occurrence of a string in the text (each Chinese character occupies two positions).

TEXT

TEXT(number, format)

Convert numbers to text according to the specified format.

ARABIC

ARABIC(roman_numeral)

Calculate the value of the given Roman numeral.

CHAR

CHAR(character_code)

According to the current Unicode encoding table, convert numbers to their corresponding characters.

CODE

CODE(string)

Returns the Unicode mapping value of the first character in the provided string.

CONCATENATE

CONCATENATE(string 1, string 2)

Append one string to another string.

DOLLAR

DOLLAR(number, decimals)

Set the number format to the currency format corresponding to the locale.

EXACT

EXACT(string 1, string 2)

Compare whether two strings are the same.

FIND

FIND(search_string, text_to_search, start_position)

Returns the position of the first occurrence of a string in the text.

FIXED

FIXED(number, decimals, no_separators)

Format numbers with a fixed number of decimal places.

JOIN

JOIN(delimiter, value or array 1, value or array 2)

Joins the elements of one or more one-dimensional arrays together using a specified delimiter.

LEFT

LEFT(string, number_of_characters)

Returns a substring extracted from the beginning of the specified string.

LEN

LEN(text)

Returns the length of the given string.

LOWER

LOWER(text)

Convert the letters in the specified string to lowercase.

MID

MID(string, start_position, extract_length)

Returns the substring from the specified string.

PROPER

PROPER(text_to_convert)

Convert the first letter of each word in the specified string to uppercase.

REGEXEXTRACT

REGEXEXTRACT(text, regular_expression)

Extract matching substrings according to the regular expression.

REGEXMATCH

REGEXMATCH(text, regular_expression)

Determine whether a text matches a regular expression.

REGEXREPLACE

REGEXREPLACE(text, regular_expression, replacement_content)

Use regular expressions to replace part of a text string with another text string.

REPLACE

REPLACE(text, position, length, new_text)

Replaces part of a text string with a different text string.

REPT

REPT(text, number_times)

Returns multiple repetitions of the specified text.

RIGHT

RIGHT(string, number_of_characters)

Returns a substring extracted from the end of the specified string.

ROMAN

ROMAN(number, rule_simplification)

Set the number format to Roman numeral form.

VALUE

VALUE(text)

Convert any recognizable date, time, or numeric format strings in the table on the right to numbers.

UPPER

UPPER(text)

Convert the letters in the specified string to uppercase.

TRIM

TRIM(text)

Remove whitespace before and after the specified string.

SEARCH

SEARCH(search_string, text_to_search, start_position)

Returns the position of the first occurrence of a string in the text.

T

T(value)

Returns a string parameter in text format.

SUBSTITUTE

SUBSTITUTE(text_to_search, search_text, replacement_text, occurrence_number)

Replace existing text with new text in the string.

SEARCHB

SEARCHB(search_string, text_to_search, [start_position])

Returns the position of the first occurrence of a string in the text (each Chinese character occupies two positions).

CHOOSE

CHOOSE(index, choice 1, choice 2)

Returns an element from a list of options based on an index.

ADDRESS

ADDRESS(row, column, absolute or relative mode, use A1 notation format)

Returns a cell reference as a string.

VLOOKUP

VLOOKUP(search_key, range, index, is_sorted)

Vertical lookup. Searches for a key value in the first column of a range, and returns the value of a specified cell in the row found.

ROW

ROW(cell reference)

Returns the row number of the specified cell

OFFSET

OFFSET(cell_reference, row_offset, column_offset, height, width)

Given a starting cell reference of a range and the number of rows and columns that the range covers, return the reference of that range.

MATCH

MATCH(search_key, range, search_type)

Returns the relative position of an item in a range that matches a specified value.

LOOKUP

LOOKUP(search_key, search_range, search_result_array[, result_range])

Finds a key value in a row or column, and returns the value in the corresponding cell to the same position in the result range as the searched row or column.

INDIRECT

INDIRECT(cell_reference_as_string, is_A1_notation)

Returns the cell reference specified by a string.

INDEX

INDEX(reference, row, column)

Returns the cell content based on the specified row and column offset.

HLOOKUP

HLOOKUP(search_key, range, index, is_sorted)

Horizontal lookup. Searches for a key value in the first row of a range and returns the value of a specified cell in the column found.

COLUMNS

COLUMNS(range)

Returns the number of columns in a specified array or range.

COLUMN

COLUMN(cell reference)

Return the column number of the specified cell according to the rule A=1.

ROWS

ROWS(range)

Returns the number of rows in the specified array or range.

PV

PV(rate, nper, pmt, fv, type)

Based on equal installment payments and a fixed interest rate, calculate the present value of an annuity investment.

COUPNUM

COUPNUM(settlement_date, maturity_date, frequency, basis)

Calculate the number of coupon payments between the settlement date and maturity date of an investment.

XNPV

XNPV(discount_rate, cash_flow_amounts, cash_flow_dates)

Calculate the net present value of an investment based on a specified series of potentially irregularly distributed cash flows and a discount rate.

XIRR

XIRR(cash flow amounts, cash flow dates, return estimate)

Calculate the internal rate of return for an investment based on a specified series of potentially irregularly distributed cash flows.

TBILLYIELD

TBILLYIELD(settlement, maturity, pr)

Calculate the yield of U.S. government short-term bonds based on price.

NOMINAL

NOMINAL(effective_rate, npery)

Calculate the nominal annual interest rate based on the actual interest rate and the number of compounding periods per year.

ACCRINTM

ACCRINTM(issue_date, maturity_date, rate, redemption_value, day_count_basis)

Calculate the accrued interest for interest payments due.

YIELDDISC

YIELDDISC(settlement, maturity, price, redemption, day_count_basis)

Calculates the annual yield for a discounted bond (non-interest bearing) based on price.

YIELD

YIELD(settlement_date, maturity_date, rate, price, redemption_value, frequency, day_count_method)

Calculate the annual yield of a regular coupon-paying bond (such as U.S. Treasury bonds) based on price.

COUPDAYBS

COUPDAYBS(settlement, maturity, frequency, basis)

Calculates the number of days from the beginning of a coupon period to the settlement date

TBILLPRICE

TBILLPRICE(settlement, maturity, discount)

Calculate the price of U.S. government short-term bonds based on the discount rate.

TBILLEQ

TBILLEQ(settlement, maturity, discount)

Calculate the equivalent annual yield of U.S. government short-term bonds based on the discount rate.

SYD

SYD(original_cost, salvage_value, useful_life, depreciation_period)

Calculate the asset depreciation amount within a specified period using the sum-of-years-digits method.

SLN

SLN(asset_cost, salvage_value, useful_life)

Calculate asset depreciation for each depreciation period using the straight-line method.

RATE

RATE(nper, pmt, pv, fv, type, guess)

Based on equal installment payments and a fixed interest rate, calculate the rate of return on an annuity investment.

DURATION

DURATION(settlement, maturity, rate, yield, frequency, [basis])

Calculate the number of compounding periods required to reach a target value based on a specified present value and investment amount against a given redemption value.

CUMPMT

CUMPMT(rate, nper, pv, start_period, end_period, type)

Based on equal installment payments and a fixed interest rate, calculate the cumulative interest on an investment over a series of payment periods.

CUMPRINC

CUMPRINC(rate, nper, pv, start_period, end_period, type)

Based on equal installment payments and a fixed interest rate, calculate the cumulative principal repayment amount of an investment over multiple payment periods.

DB

DB(cost, salvage, life, period, month)

Calculate the asset depreciation amount within a specified period using the declining balance method.

DDB

DDB(original_cost, salvage_value, useful_life, depreciation_period, factor)

Calculate the asset depreciation amount for a specified period using the double-declining balance method.

DOLLARDE

DOLLARDE(fractional_dollar, fraction)

Convert quotes expressed as decimal fractions to decimal numbers.

DOLLARFR

DOLLARFR(decimal_price, unit)

Convert quotes expressed as decimal numbers to decimal fractions.

EFFECT

EFFECT(nominal_rate, npery)

Calculate the effective annual interest rate based on the nominal interest rate and the number of compounding periods per year.

FV

FV(rate, nper, pmt, pv, type)

Based on equal installment payments and a fixed interest rate, calculate the future value of an annuity investment.

FVSCHEDULE

FVSCHEDULE(principal, schedule)

Calculate the future value of a principal amount based on a set of specified variable interest rates.

INTRATE

INTRATE(purchase_date, sale_date, purchase_price, sale_price, day_count_method)

If an investment is purchased at one price and sold at another price (where the investment itself does not generate interest or dividends), calculate the actual rate of return generated by that investment.

IPMT

IPMT(rate, period, total_periods, present_value, future_value, end_or_beginning)

Based on equal installment payments and a fixed interest rate, calculates the interest paid for an investment.

IRR

IRR(cash_flow_amount, rate_guess)

Calculate the internal rate of return for an investment based on a series of periodic cash flows.

MDURATION

MDURATION(settlement, maturity, rate, yield, frequency, basis)

Calculate the Macaulay modified duration of a fixed-income bond with periodic coupon payments (such as U.S. Treasury long-term bonds) based on the expected yield.

MIRR

MIRR(cash_flow_amounts, financing_rate, reinvestment_return_rate)

Based on a set of periodic cash flows and the rates of financing costs paid and reinvestment returns received, calculates the modified internal rate of return of an investment.

PRICEMAT

PRICEMAT(settlement date, maturity date, issue date, interest rate, yield, day count basis)

Calculate the price of a bond that pays interest at maturity based on the expected rate of return.

NPER

NPER(rate, payment, present_value, future_value, type)

Based on equal installment payments and a fixed interest rate, calculate the total number of periods for an investment.

NPV

NPV(discount_rate, cash_flow_1, cash_flow_2)

Based on a set of periodic cash flows and a discount rate, calculate the net present value of an investment.

PMT

PMT(rate, nper, pv, fv, type)

Based on equal installment payments and a fixed interest rate, calculate the amortization amount for an annuity investment.

PPMT

PPMT(rate, period, total_periods, present_value, future_value, end_or_beginning)

Based on equal installment payments and a fixed interest rate, calculate the principal repayment amount of the investment.

PRICE

PRICE (settlement, maturity, rate, yld, redemption, frequency, basis)

Calculate the price of a fixed-rate coupon bond (such as a U.S. Treasury long-term bond) based on the expected rate of return.

RECEIVED

RECEIVED (settlement, maturity, investment, discount, [basis])

Calculate the amount received at maturity for a fixed-income bond investment purchased at a set date and held until maturity.

DISC

DISC (settlement, maturity, pr, redemption, [basis])

Calculate bond discount rate based on price.

COUPDAYS

COUPDAYS (settlement, maturity, frequency, [basis])

Calculate the number of days included in the coupon period or interest payment period that contains the settlement date.

ACCRINT

ACCRINT(issue_date, first_interest_date, settlement_date, rate, redemption_amount, frequency, [day_count_basis])

Calculate the accrued interest for periodic interest payments.

PRICEDISC

PRICEDISC (settlement, maturity, discount, redemption, [basis])

Calculate the price of a discount bond (non-interest bearing) issued at a discount based on the discount yield.

COUPDAYSNC

COUPDAYSNC (settlement, maturity, frequency, [basis])

Calculate the number of days from the settlement date to the next coupon date or interest payment date.

COUPNCD

COUPNCD (settlement, maturity, frequency, [basis])

Calculate the next coupon or interest payment date after the settlement date.

COUPPCD

COUPPCD (settlement, maturity, frequency, [basis])

Calculate the last coupon or interest payment date before the settlement date.

IFERROR

IFERROR(value, [value_if_error])

If the first argument is not an error value, return the first argument; otherwise, return the second argument if it exists, or return a blank value if it does not exist.

AND

AND(logical expression 1, logical expression 2)

Returns true when all provided parameters are logically "true"; returns false if any one of the provided parameters is logically "false".

FALSE

FALSE()

Returns the logical value FALSE.

IF

IF(logical_expression, return_value_if_TRUE, return_value_if_FALSE)

Returns one value when a logical expression evaluates to TRUE, and returns another value when it evaluates to FALSE.

NOT

NOT(logical_expression)

Returns the opposite of a given logical value — "NOT(TRUE)" will return FALSE; "NOT(FALSE)" will return TRUE.

OR

OR(logical expression 1, logical expression 2)

Returns true if any of the provided parameters is logically true, and returns false if all provided parameters are logically false.

TRUE

TRUE()

Returns the logical value TRUE.