Skip to main content

Formulas and functions

Starting from v4.0, the package of DHTMLX Spreadsheet includes a set of predefined formulas that can be used for different types of calculations of strings and numbers. The formulas are compatible with Excel and Google Sheets.

note

Lowercase letters in formulas are automatically converted to upper case.

Functions

Here's a list of all the available functions with detailed descriptions.

Boolean operators

You can compare two values via using logical expressions that in any given case will only return either TRUE or FALSE.

OperatorExampleDescription
==A1=B1Returns TRUE if the value in cell A1 is equal to the value in cell B1; otherwise, FALSE.
<>=A1<>B1Returns TRUE if the value in cell A1 is not equal to the value in cell B1; otherwise, FALSE.
>=A1>B1Returns TRUE if the value in cell A1 is greater than the value in cell B1; otherwise, FALSE.
<=A1<B1Returns TRUE if the value in cell A1 is less than the value in cell B1; otherwise, FALSE.
>==A1>=B1Returns TRUE if the value in cell A1 is greater than or equal to the value in cell B1; otherwise, FALSE.
<==A1<=B1Returns TRUE if the value in cell A1 is less than or equal to the value in cell B1; otherwise, FALSE.

Check the example in our snippet tool.

Date functions

FunctionFormulaDescription
DATE=DATE(year,month,day)Combines three separate values (year, month, and day) and returns a date.
DATEDIF=DATEDIF(start_date,end_date,unit)Returns the number of days, months, or years between two dates.
The unit argument is used to define which type of information you want returned.
DATEVALUE=DATEVALUE(date_text)Converts a date that is stored as text to a serial number.
DAY=DAY(date)Returns the day of the month as a number between 1 to 31 from a specified date.
DAYS=DAYS(end_date, start_date)Returns the number of days between two dates.
DAYS360=DAYS360(start_date,end_date,[method]])Returns the number of days between 2 dates, based on a 360-day year (twelve 30-days months).
EDATE=EDATE(start_date, months)Returns the date on the same date of the month, n months in the past or future.
EOMONTH=EOMONTH(start_date, months)Returns the date for the last day of the month, n months before or after the specified start date.
ISOWEEKNUM=ISOWEEKNUM(date)Returns the number of the ISO week number of the year for the specified date.
MONTH=MONTH(date)Returns the month of the year of the specified date.
NETWORKDAYS=NETWORKDAYS(start_date, end_date, [holidays])Returns the number of whole working days between two dates. Working days exclude weekends and any dates specified in holidays.
NETWORKDAYSINTL=NETWORKDAYSINTL(start_date, end_date, [weekend], [holidays])Returns the number of whole working days between two dates.
The optional weekend parameter is used to specify which days of the week are considered weekends.
Weekend days and holidays are not considered as workdays.
NOW=NOW()Returns the current date.
TIMEVALUE
added in v4.3
=TIMEVALUE(time_text)Returns the decimal number of the time represented by a text string
WEEKDAY=WEEKDAY(date,[return_type])Returns the day of the week for the specified date.
The return_type argument is used to define which day of the week is considered the first day.
WEEKNUM=WEEKNUM(date,[return_type])Returns the week number for the specified date.
The return_type argument is used to define which day of the week is considered the first day.
WORKDAY=WORKDAY(start_date, days, [holidays])Returns the date of the nearest working day n days in the future or past.
Working days exclude weekends and any dates specified in holidays.
WORKDAYINTL=WORKDAYINTL(start_date, days, [weekend], [holidays])Returns the date of the nearest working day n days in the future or past.
The optional weekend parameter is used to specify which days of the week are considered weekends.
Weekend days and holidays are not considered as workdays.
YEAR=YEAR(date)Returns the year of the specified date.
YEARFRAC=YEARFRAC(start_date, end_date, [basis])Returns the year of the specified date.
The optional basis argument is used to define the type of day count basis.

Check the example in our snippet tool.

Financial functions

FunctionFormulaDescription
ACCRINT =ACCRINT(issue, id, sd, rate, par, frequency, [basis], [calc_method]),

where:
  • issue - the issue date of the security;
  • id - the security's first interest date;
  • sd - the security's settlement date;
  • rate - the security's annual coupon rate;
  • par - the security's par value, $1,000 by default;
  • frequency - the number of coupon payments per year (1 for annual payments);
  • basis - optional, the type of day count basis to use;
  • calc_method - optional, the way to calculate the total accrued interest when the date of settlement is later than the date of first interest (0 or 1(default)).
Returns the accrued interest for a security that pays periodic interest.
BINOM.DIST
added in v4.3
=BINOM.DIST(number_s, trials, probability_s, cumulative),

where:
  • number_s - the number of successes in trials;
  • trials - the number of independent trials;
  • probability_s - the probability of success on each trial;
  • cumulative - if TRUE, then BINOM.DIST returns the cumulative distribution function; if FALSE (use 0), it returns the probability mass function.
Returns the individual term binomial distribution probability.
BINOM.DIST.RANGE
added in v4.3
=BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]),

where:
  • trials - the number of independent trials (must be ≥ 0);
  • probability_s - the probability of success in each trial (must be ≥ 0 and ≤ 1);
  • number_s - the number of successes in trials (must be ≥ 0 and ≤ trials);
  • number_s2 - optional. If provided, returns the probability that the number of successful trials will fall between number_s and number_s2
    ([number_s2] must be ≥ number_s and ≤ trials).
Returns the probability of a trial result using a binomial distribution.
BINOM.INV
added in v4.3
=BINOM.INV(trials, probability_s, alpha),

where:
  • trials - the number of Bernoulli trials;
  • probability_s - the probability of success in each trial (must be ≥ 0 and ≤ 1);
  • alpha - the criterion value (must be ≥ 0 and ≤ 1);
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
BITLSHIFT
added in v4.3
=BITLSHIFT(number, shift_amount),

where:
  • number - the number to be shifted (must be an integer greater than or equal to 0
  • shift_amount - the amount of bits to shift, if negative, shifts bits to the right instead
Returns a number shifted left by the specified number of bits.
BITOR
added in v4.3
=BITOR(number1, number2),

where:
  • number1 - a decimal number (must be greater than or equal to 0 and no larger than 2^48 - 1);
  • number2 - a decimal number (must be greater than or equal to 0 and no larger than 2^48 - 1);
Returns a decimal number representing the bitwise OR of two numbers.
BITRSHIFT
added in v4.3
=BITRSHIFT(number, shift_amount),

where:
  • number - the number to be shifted (must be an integer greater than or equal to 0);
  • shift_amount - the amount of bits to shift, if negative shifts bits to the left instead;
Returns a number shifted right by the specified number of bits.
BITXOR
added in v4.3
=BITXOR(number1, number2),

where:
  • number1 - a decimal number (must be greater than or equal to 0 and no larger than 2^48 - 1);
  • number2 - a decimal number (must be greater than or equal to 0 and no larger than 2^48 - 1);
Returns a decimal number representing the bitwise XOR of two numbers.
COMPLEX
added in v4.3
=COMPLEX(real_num, i_num, [suffix]),

where:
  • real_num - the real coefficient of the complex number;
  • i_num - the imaginary coefficient of the complex number;
  • suffix - optional ("i" by default) - the suffix for the imaginary component of the complex number;
    (must be lowercase "i" or "j") .
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
CORREL
added in v4.3
=CORREL(array1, array2),

where:
  • array1 - a range of cell values;
  • array2 - a second range of cell values;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included. The arrays must have equal number of data points.
Returns the correlation coefficient of two cell ranges.
COVAR
added in v4.3
=COVAR(array1, array2),

where:
  • array1 - The first cell range of integers;
  • array2 - The second cell range of integers;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included. The arrays must have equal number of data points.
Returns covariance, the average of the products of deviations for each data point pair in two data sets.
COVARIANCE.P
added in v4.3
=COVARIANCE.P(array1, array2),

where:
  • array1 - The first cell range of integers;
  • array2 - The second cell range of integers;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included. The arrays must have equal number of data points.
Returns population covariance, the average of the products of deviations for each data point pair in two data sets.
COVARIANCE.S
added in v4.3
=COVARIANCE.S(array1, array2),

where:
  • array1 - The first cell range of integers;
  • array2 - The second cell range of integers;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included. The arrays must have equal number of data points.
Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.
DB=DB(cost, salvage, life, period, [month]),

where:
  • cost - the initial cost of the asset;
  • salvage - the value of the asset at the end of the depreciation;
  • life - the number of periods over which the asset is being depreciated;
  • period - the period to calculate depreciation for;
  • month - optional, the number of months in the first year, 12 by default.
Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
DDB=DDB(cost, salvage, life, period, [factor]),

where:
  • cost - the initial cost of the asset;
  • salvage - the value of the asset at the end of the depreciation;
  • life - the number of periods over which the asset is being depreciated;
  • period - the period to calculate depreciation for;
  • factor - optional, the rate at which the balance declines, 2 (the double-declining balance method) by default
Calculates the depreciation of an asset for a specified period using the double-declining balance method or another method you specify.
DEC2BIN
added in v4.3
=DEC2BIN(number),

where:
  • number - the decimal integer you want to convert (must be greater than -512 but less than 511);
Converts a decimal number to binary.
DEC2HEX
added in v4.3
=DEC2HEX(number),

where:
  • number - the decimal integer you want to convert (must be greater than -549755813888 but less than 549755813887);
Converts a decimal number to hexadecimal.
DEC2OCT
added in v4.3
=DEC2OCT(number),

where:
  • number - the decimal integer you want to convert (must be greater than -536870912 but less than 536870911);
Converts a decimal number to octal.
DELTA
added in v4.3
=DELTA(number1, [number2]),

where:
  • number1 - the first number;
  • number2 - optional, the second number. If omitted, number2 is assumed to be zero.
Tests two numbers for equality. Returns 1 if number1 = number2; returns 0 otherwise.
DEVSQ
added in v4.3
=DEVSQ(number1, [number2], ...),

where:
  • number1, number2,... - from 1 to 255 arguments for which you want to calculate the sum of squared deviations;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included.
Returns the sum of squares of deviations of data points from their sample mean.
DOLLARDE=DOLLARDE(fractional_dollar, fraction)Converts a dollar price specified as an integer part and a fraction part into a dollar price displayed as a decimal number.
DOLLARFR=DOLLARFR(decimal_dollar, fraction)Converts a decimal number into fractional dollar number.
EFFECT=EFFECT(nominal_rate, npery)

nominal_rate must be >= 0, npery must be > 1.
Returns the effective annual interest rate on the base of the nominal annual interest rate and the number of compounding periods per year you specify.
Works with numeric values.
ERF
added in v4.3
=ERF(lower_limit, [upper_limit]),

where:
  • lower_limit - the lower bound for integrating ERF;
  • upper_limit - the upper bound for integrating ERF. If omitted, ERF integrates between 0 and lower_limit.
Returns the error function integrated between lower_limit and upper_limit.
ERFC
added in v4.3
=ERFC(x),

where:
  • x - the lower bound for integrating ERFC
Returns the complementary ERF function integrated between x and infinity.
EXP
added in v4.3
=EXP(number),

where:
  • number - the power that e is raised to
Returns the result of the constant e (which equals 2.71828182845904) raised to the power of a number.
FISHER
added in v4.3
=FISHER(x),

where:
  • x - the value for which you want to calculate the transformation
Calculates the Fisher transformation for a supplied value.
FISHERINV
added in v4.3
=FISHERINV(y),

where:
  • y - the value for which you want to perform the inverse of the transformation
Calculates the inverse of the Fisher transformation and returns a value between -1 and +1.
FV=FV(rate, nper, pmt, [pv], [type]),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • nper - the total number of payment periods in an annuity. For monthly payments, nper=nper*12;
  • pmt - the payment made each period;
  • pv - optional, the present value, or the lump-sum amount that a series of future payments is worth right now, 0 by default;
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Calculates the future value of an investment.
FVSCHEDULE=FVSCHEDULE(principal, schedule),

where:
  • principal - the present value;
  • schedule - an array of interest rates to apply. The values in the array can be numbers or blank cells; any other value produces the error value. Blank cells are taken as zeros.
Returns the future value of an initial principal (=present value) after applying a series of compound interest rates.
GAMMA
added in v4.3
=GAMMA(number)

If Number is a negative integer or 0, GAMMA returns the #Error value.
Returns the gamma function value.
GEOMEAN
added in v4.3
=GEOMEAN(number1, [number2], ...)

where:
  • number1, number2,... - from 1 to 255 arguments for which you want to calculate the mean;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included.
Returns the geometric mean of an array or range of positive data.
GESTEP
added in v4.3
=GESTEP(number, [step])

where:
  • number - the value to test against step;
  • step - optional, the threshold value. If you omit a value for step, GESTEP uses zero;
Returns 1 if number ≥ step; returns 0 (zero) otherwise.
HARMEAN
added in v4.3
=HARMEAN(number1, [number2], ...)

where:
  • number1, number2,... - from 1 to 255 arguments for which you want to calculate the mean;

  • Text, logical values, or empty cells are ignored. Cells with zero values are included.
Returns the harmonic mean of a data set.
HEX2BIN
added in v4.3
=HEX2BIN(number)

where:
  • number - the hexadecimal number you want to convert. Number can't contain more than 10 characters;
Converts a hexadecimal number to binary.
HEX2DEC
added in v4.3
=HEX2DEC(number)

where:
  • number - the hexadecimal number you want to convert. Number can't contain more than 10 characters;
Converts a hexadecimal number to decimal.
HEX2OCT
added in v4.3
=HEX2OCT(number)

where:
  • number - the hexadecimal number you want to convert. Number can't contain more than 10 characters;
Converts a hexadecimal number to octal.
IMABS
added in v4.3
=IMABS(inumber)

where:
  • inumber - a complex number
Returns the absolute value of a complex number in the format x + yi or x + yj.
IMAGINARY
added in v4.3
=IMAGINARY(inumber)

where:
  • inumber - a complex number
Returns the imaginary coefficient of a complex number given in the format x + yi or x + yj.
IMCONJUGATE
added in v4.3
=IMCONJUGATE(inumber)

where:
  • inumber - a complex number
Returns the complex conjugate of a complex number given in the format x + yi or x + yj.
IMCOS
added in v4.3
=IMCOS(inumber)

where:
  • inumber - a complex number
Returns the cosine of a complex number given in the format x + yi or x + yj.
IMCOSH
added in v4.3
=IMCOSH(inumber)

where:
  • inumber - a complex number
Returns the hyperbolic cosine of a complex number given in the format x + yi or x + yj.
IMCOT
added in v4.3
=IMCOT(inumber)

where:
  • inumber - a complex number
Returns the cotangent of a complex number given in the format x + yi or x + yj.
IMCSC
added in v4.3
=IMCSC(inumber)

where:
  • inumber - a complex number
Returns the cosecant of a complex number given in the format x + yi or x + yj.
IMCSCH
added in v4.3
=IMCSCH(inumber)

where:
  • inumber - a complex number
Returns the hyperbolic cosecant of a complex number given in the format x + yi or x + yj.
IMDIV
added in v4.3
=IMDIV(inumber1, inumber2)

where:
  • inumber1 - the complex numerator or dividend
  • inumber2 - the complex denominator or divisor
Returns the quotient of two complex numbers given in the format x + yi or x + yj.
IMEXP
added in v4.3
=IMEXP(inumber)

where:
  • inumber - a complex number
Returns the exponential of a complex number given in the format x + yi or x + yj.
IMLN
added in v4.3
=IMLN(inumber)

where:
  • inumber - a complex number
Returns the natural logarithm of a complex number given in the format x + yi or x + yj.
IMPOWER
added in v4.3
=IMPOWER(inumber, number)

where:
  • inumber - a complex number
  • number - the power to which you want to raise the complex number
Returns a complex number in x + yi or x + yj text format raised to a power.
IMPRODUCT
added in v4.3
=IMPRODUCT(inumber1, [inumber2], ...)

where:
  • inumber1, inumber2,... - from 1 to 255 complex numbers to multiply
Returns the product of 1 to 255 complex numbers given in the format x + yi or x + yj.
IMREAL
added in v4.3
=IMREAL(inumber)

where:
  • inumber - a complex number
Returns the real coefficient of a complex number given in the format x + yi or x + yj.
IMSEC
added in v4.3
=IMSEC(inumber)

where:
  • inumber - a complex number
Returns the secant of a complex number given in the format x + yi or x + yj.
IMSECH
added in v4.3
=IMSECH(inumber)

where:
  • inumber - a complex number
Returns the hyperbolic secant of a complex number given in the format x + yi or x + yj.
IMSIN
added in v4.3
=IMSIN(inumber)

where:
  • inumber - a complex number
Returns the sine of a complex number given in the format x + yi or x + yj.
IMSINH
added in v4.3
=IMSINH(inumber)

where:
  • inumber - a complex number
Returns the hyperbolic sine of a complex number given in the format x + yi or x + yj.
IMSQRT
added in v4.3
=IMSQRT(inumber)

where:
  • inumber - a complex number
Returns the square root of a complex number given in the format x + yi or x + yj.
IMSUB
added in v4.3
=IMSUB(inumber1, inumber2)

where:
  • inumber1 - a complex number from which to subtract inumber2;
  • inumber2 - the complex number to subtract from inumber1
Returns the difference of two complex numbers given in the format x + yi or x + yj.
IMSUM
added in v4.3
=IMSUB(inumber1, [inumber2], ...)

where:
  • inumber1, inumber2,... - from 1 to 255 complex numbers to add
Returns the sum of two or more complex numbers given in the format x + yi or x + yj.
IMTAN
added in v4.3
=IMTAN(inumber)

where:
  • inumber - a complex number
Returns the tangent of a complex number given in the format x + yi or x + yj.
IPMT=IPMT(rate, per, nper, pv, [fv], [type]),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • per - the period for which you want to find the interest and must be in the range between 1 and nper;
  • nper - the total number of payment periods in an annuity. For monthly payments, nper=nper*12;
  • pv - the present value, or the lump-sum amount that a series of future payments is worth right now;
  • fv - optional, the future value, 0 by default;
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
IRR=IRR(values, [guess]),

where:
  • values - an array or reference to cells that contain values. The array must contain at least one positive value and one negative value;
  • guess - optional, an estimate for expected IRR, .1 (=10%) by default.
Returns the internal rate of return (IRR) for a series of cash flows that occur at regular intervals.
ISPMT=ISPMT(rate, per, nper, pv),

where:
  • rate - the interest rate for the investment. For monthly payments, rate = rate/12;
  • per - the period for which you want to find the interest and must be in the range between 1 and nper;
  • nper - the total number of payment periods for the investment. For monthly payments, nper=nper*12;
  • pv - the present value of the investment. For a loan, pv is the loan amount.
Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments.
LARGE
added in v4.3
=LARGE(array, k),

where:
  • array - the array or range of data for which you want to determine the k-th largest value;
  • k - the position (from the largest) in the array or cell range of data to return.
Returns the k-th largest value in an array.
MEDIAN
added in v4.3
=MEDIAN(number1, [number2], ...),

where:
  • number1, number2,... - from 1 to 255 numbers for which you want to calculate the median;
Returns the median of the given numbers.
NOMINAL=NOMINAL(effect_rate, npery),

effect_rate must be >= 0, npery must be > 1.
Returns the nominal annual interest rate on the base of the effective rate and the number of compounding periods per year you specify.
NPER=NPER(rate,pmt,pv,[fv],[type]),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • pmt - the payment made each period;
  • pv - the present value, or the lump-sum amount that a series of future payments is worth right now;
  • fv - optional, the future value, 0 by default;
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
NPV=NPV(rate,value1,[value2],...),

where:
  • rate - the rate of discount over one year;
  • value1, value2,... - from 1 to 254 values representing cash flows (future payments and income).
    Empty cells, logical values, text, or error values are ignored.
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
OCT2BIN
added in v4.3
=OCT2BIN(number),

where:
  • number - the octal number you want to convert. It can't contain more than 10 characters;
Converts an octal number to binary.
OCT2DEC
added in v4.3
=OCT2DEC(number),

where:
  • number - the octal number you want to convert. Number may not contain more than 10 octal characters (30 bits)
Converts an octal number to decimal.
OCT2HEX
added in v4.3
=OCT2HEX(number),

where:
  • number - the octal number you want to convert. Number may not contain more than 10 octal characters (30 bits)
Converts an octal number to hexadecimal.
PDURATION=PDURATION(rate, pv, fv),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • pv - the present value of the investment;
  • fv - the desired future value of the investment.

All arguments must be positive values.
Returns the number of periods required by an investment to reach a specified value.
PERCENTILE
added in v4.3
=PERCENTILE(array, k),

where:
  • array - an array of data values;
  • k - the percentile value between 0 and 1, inclusive.
Returns the k-th percentile of values in a range.
PERCENTILE.EXC
added in v4.3
=PERCENTILE.EXC(array, k),

where:
  • array - an array of data values;
  • k - the percentile value between 0 and 1, exclusive.
Returns the k-th percentile of values in a range.
PERCENTILE.INC
added in v4.3
=PERCENTILE.INC(array, k),

where:
  • array - an array of data values;
  • k - the percentile value between 0 and 1, inclusive.
Returns the k-th percentile of values in a range.
PERMUT
added in v4.3
=PERMUT(number, number_chosen),

where:
  • number - the total number of items;
  • number_chosen - the number of items in each combination.
Returns the number of permutations for a given number of items.
PMT=PMT(rate, nper, pv, [fv], [type]),

where:
  • rate - the interest rate for the loan. For monthly payments, rate = rate/12;
  • nper - the total number of months of payments for the loan. For monthly payments, nper=nper*12;
  • pv - the present value (or the current total amount of loan);
  • fv - optional, the future value, 0 by default;
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Calculates the monthly payment for a loan based on constant payments and a constant interest rate.
PPMT=PPMT(rate, per, nper, pv, [fv], [type]),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • per - the period for which you want to find the interest and must be in the range between 1 and nper;
  • nper - the total number of payment years in an annuity. For monthly payments, nper=nper*12;
  • pv - the present value - the total amount that a series of future payments is worth now;
  • fv - the desired future value or a cash balance.
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Returns the payment on the principal for a specified period for an investment based on periodic, constant payments and a constant interest rate.
PV=PV(rate, nper, pmt, [fv], [type]),

where:
  • rate - the interest rate per period. For monthly payments, rate = rate/12;
  • nper - the total number of payment years in an annuity. For monthly payments, nper=nper*12;
  • pmt - the payment made each period. If pmt is omitted, you must include the fv argument;
  • fv - optional, the desired future value or a cash balance.
  • type - optional, 0(default) - the payments are due at the end of the period, 1 - at the beginning of the period.
Returns the present value of a loan or an investment, based on a constant interest rate.
QUARTILE
added in v4.3
=QUARTILE(array, quart),

where:
  • array - the array or cell range of numeric values;
  • quart - indicates which value to return (0-4).
Returns the quartile of a data set.
QUARTILE.EXC
added in v4.3
=QUARTILE(array, quart),

where:
  • array - the array or cell range of numeric values;
  • quart - indicates which value to return (1-3).
Returns the quartile of the data set, based on percentile values from 0..1, exclusive.
QUARTILE.INC
added in v4.3
=QUARTILE.INC(array, quart),

where:
  • array - the array or cell range of numeric values;
  • quart - indicates which value to return (0-4).
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
SIGN
added in v4.3
=SIGN(number),

where:
  • number - any real number
Defines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.
SMALL
added in v4.3
=SMALL(array, k),

where:
  • array - an array or range of numeric values;
  • k - the position (from 1 - the smallest value) in the data set.
Returns the k-th smallest value based on its position in the data set.
STEYX
added in v4.3
=STEYX(known_y's, known_x's),

where:
  • known_y's - an array or range of dependent data points;
  • known_x's - an array or range of independent data points.

Text, logical values, or empty cells are ignored. Zero values are included.
Returns the standard error of the predicted y-value for each x in the regression.
SYD
added in v4.3
=SYD(cost, salvage, life, per),

where:
  • cost - the initial cost of the asset;
  • salvage - the asset value at the end of the depreciation;
  • life - the number of periods over which the asset is depreciated;
  • per - the period and must use the same units as life.
Returns the sum-of-years' digits depreciation of an asset for a specified period.
TBILLPRICE
added in v4.3
=TBILLPRICE(settlement, maturity, discount),

where:
  • settlement - the settlement date of the Treasury bill;
  • maturity - the maturity date of the Treasury bill;
  • discount - the Treasury bill's percentage discount rate.
Returns the price per $100 face value for a Treasury bill.
TBILLYIELD
added in v4.3
=TBILLYIELD(settlement, maturity, pr),

where:
  • settlement - the settlement date of the Treasury bill;
  • maturity - the maturity date of the Treasury bill;
  • pr - the Treasury bill's price per $100 face value.
Returns the yield for a Treasury bill.
WEIBULL
added in v4.3
=WEIBULL(x, alpha, beta, cumulative),

where:
  • x - the value at which the function must be calculated (must be ≥ 0);
  • alpha - the alpha parameter to the distribution (must be > 0);
  • beta - the beta parameter to the distribution (must be > 0);
  • cumulative - the logical (true/false) argument which defines the type of distribution to be used. If True - Weibull cumulative distribution function, if False - Weibull probability density function
Returns the Weibull distribution.
WEIBULL.DIST
added in v4.3
=WEIBULL(x, alpha, beta, cumulative),

where:
  • x - the value at which the function must be calculated (must be ≥ 0);
  • alpha - the alpha parameter to the distribution (must be > 0);
  • beta - the beta parameter to the distribution (must be > 0);
  • cumulative - the logical (true/false) argument which defines the type of distribution to be used. If True - Weibull cumulative distribution function, if False - Weibull probability density function
Returns the Weibull distribution.

Check the example in our snippet tool.

Information functions

FunctionFormulaDescription
ISBINARY=ISBINARY(value)Returns TRUE if the value is binary; otherwise, returns FALSE.
ISBLANK=ISBLANK(A1)Returns TRUE if a cell is empty; otherwise, returns FALSE.
ISEVEN=ISEVEN(number)Returns TRUE if a number is even, or FALSE if number is odd.
Works with integer numbers.
ISNONTEXT=ISNONTEXT(value)Returns TRUE if a cell contains any value except text; otherwise, returns FALSE.
ISNUMBER=ISNUMBER(value)Returns TRUE if a cell contains a number; otherwise, returns FALSE.
ISODD=ISODD(number)Returns TRUE if a number is odd, or FALSE if number is even.
Works with integer numbers.
ISTEXT=ISTEXT(value)Returns TRUE if a value is text; otherwise, returns FALSE.
N=N(value)Returns a value converted to a number.

Check the example in our snippet tool.

Lookup functions

FunctionFormulaDescription
HLOOKUP
added in v4.3
=HLOOKUP(lookup_value, table_array, row_index, [range_lookup]),

where:
  • lookup_value - the value to search for;
  • table_array - the table from which to retrieve a value;
  • column_index_num - the row number in the table from which to retrieve a value;
  • range_lookup - optional (1 by default):
    0 - exact match, 1 - approximate match
Looks up a value in a horizontal table
INDEX
added in v4.3
=INDEX(array, row_num, [column_num]),

where:
  • array - a range of cells or an array constant;
  • row_num - the row position in the reference or array;
  • column_num - optional, the column position in the reference or array.
Returns the value at a given location in a range or array.
LOOKUP
added in v4.3
=LOOKUP(lookup_value, lookup_vector, [result_vector]),

where:
  • lookup_value - the value to search for;
  • lookup_vector - the one-row, or one-column range to search;
  • result_vector - optional, the one-row, or one-column range of results.
Looks up a value in a one-column or one-row range, and retrieves a value from the same position in another one-column or one-row range.
MATCH
added in v4.3
=MATCH(lookup_value, lookup_array, [match_type]),

where:
  • lookup_value - the value that you want to match in lookup_array;
  • lookup_array - the range of cells;
  • match_type - optional (1 by default):
    1- finds the largest value that is less than or equal to lookup_value
    0 - finds the value that is exactly equal to lookup_value
    -1 - finds the smallest value that is greater than or equal to lookup_value
Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
VLOOKUP
added in v4.3
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup]),

where:
  • lookup_value - the value to search for in the first column of a table;
  • table_array - the table from which to retrieve a value;
  • column_index_num - the column number in the table from which to retrieve a value;
  • range_lookup - optional (1 by default):
    0 - exact match, 1 - approximate match
Looks up a value in a vertical table by matching on the first column
XLOOKUP
added in v4.3
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode]),

where:
  • lookup_value - the value to search for;
  • lookup_array - the array or range to search;
  • return_array - the array or range to return;
  • if_not_found - optional, if a valid match is not found, returns the [if_not_found] text you supply;
  • match_mode - optional (0 by default):
    0 - Exact match
    -1 - Exact match. If not found, returns the next smaller item
    1 - Exact match. If not found, returns the next larger item
Searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
XMATCH
added in v4.3
=XMATCH(lookup_value, lookup_array, [match_mode]),

where:
  • lookup_value - the value that you want to match in lookup_array;
  • lookup_array - the range of cells;
  • match_mode - optional, 0 - exact match (default), -1 - exact match or next smallest, 1 - exact match or next larger
Performs a lookup and returns a position in vertical or horizontal ranges.

Math functions

ABSReturns the absolute value of a number. The absolute value of a number is always positive.
ACOSReturns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The number must be from -1 to 1, inclusive. The returned angle is given in radians in the range 0 (zero) to pi.
ACOSHReturns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1.
ACOTReturns the principal value of the arc-cotangent, or inverse cotangent, of a number. The returned angle is given in radians in the range 0 (zero) to pi.
ACOTHReturns the inverse hyperbolic cotangent of a number. The absolute value of the number must be greater than 1.
ADDReturns the sum of two values.
Empty cells, logical values like TRUE, or text are ignored.
ARABICConverts a Roman numeral to an Arabic numeral.
ASINReturns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The number must be from -1 to 1, inclusive. The returned angle is given in radians in the range -pi/2 to pi/2.
ASINHReturns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number. Works with real numbers.
ATANReturns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. Works with the tangent of the angle you want.
ATAN2Returns the arctangent of (x,y) coordinates. The arctangent is returned in radians between -pi and pi, excluding -pi.
ATANHReturns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). Works with real numbers.
AVEDEV
added in v4.3
Returns the average of the absolute deviations of data points from their mean.
Empty cells, logical values, text, or error values in the array or reference are ignored. Cells with the value 0 are included.
AVERAGECalculates the average (arithmetic mean) of a group of numbers.
Logical values, empty cells and cells that contain text in the array or reference are ignored
However, cells with the value zero are included.
AVERAGEA
added in v4.3
Calculates the average (arithmetic mean) of the values in the list of arguments.
Arguments can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
Empty cells and text values in the array or reference are ignored.
BASEConverts a number into the supplied base (radix).
The number should be an integer and greater than or equal to 0 and less than 2^53.
The base radix is what we want to convert the number into. It must be an integer from 2 to 36, inclusive.
BITAND
added in v4.3
Returns a bitwise 'AND' of two numbers.
The number must be an integer and greater than or equal to 0 and less than (2^48)-1.
CEILINGReturns a number rounded up to the nearest integer or to the nearest multiple of the specified significance.
COMBINReturns the number of combinations for two given integer numbers: the number of items (number) and the number of items in each combination (number_chosen):
  • number should be greater than or equal to zero; also, it should be greater than or equal to the number_chosen;
  • number_chosen must be greater than or equal to zero.
COMBINAReturns the number of combinations for two given integer numbers and includes repetitions. The numbers are: the number of items (number) and the number of items in each combination (number_chosen):
  • number should be greater than or equal to zero; also, it should be greater than or equal to the number_chosen;
  • number_chosen must be greater than or equal to zero.
COSReturns the cosine of an angle specified in radians.
COSHReturns the hyperbolic cosine of a real number.
CSCReturns the cosecant of an angle specified in radians.
CSCHReturn the hyperbolic cosecant of an angle specified in radians.
COTReturns the cotangent of the an angle specified in radians.
COTHReturns the hyperbolic cotangent of a hyperbolic angle.
COUNTCounts the number of cells that contain numbers, and counts numbers within the list of arguments.
Empty cells, logical values, text, or error values in the array or reference are not counted.
COUNTACounts the number of cells that contain numbers, text, logical values, error values, and empty text (""); cells with zero values are excluded.
The function does not count empty cells.
COUNTBLANKReturns the number of empty cells from a specified range.
Cells with zero values are not counted.
DECIMALConverts a text representation of a number in a given base (radix) into a decimal number.
The base radix must be an integer from 2 to 36, inclusive.
DEGREESConverts radians into degrees.
DIVIDEReturns the result of dividing one number by another.
EQReturns TRUE if the first argument is equal to the second; otherwise FALSE.
EVENReturns a number rounded up to the nearest even integer.
FACTReturns the factorial of a number. The number must be from 1 to n. If number is not an integer, it is truncated.
FACTDOUBLEReturns the double factorial of a number. The number must be from 1 to n. If number is not an integer, it is truncated.
FLOORRounds number down, toward zero, to the nearest multiple of the specified significance. The significant must be from 1 to n.
If the sign of number is positive, a value is rounded down and adjusted toward zero. If the sign of number is negative, a value is rounded down and adjusted away from zero.
GCDReturns the greatest common divisor of two or more integers. The function takes from 1 to 255 numeric values which are expected to be integers. If any value is not an integer, it is truncated.
GTReturns TRUE if the first argument is greater than the second; otherwise FALSE.
GTEReturns TRUE if the first argument is greater than or equal to the second; otherwise FALSE.
INTReturns a number rounded down to the nearest integer.
LNReturns the natural logarithm of a positive real number.
LOGReturns the logarithm of a positive real number to the base you specify. If base is omitted, it is assumed to be 10.
LOG10Returns the base-10 logarithm of a positive real number.
LTReturns TRUE if the first argument is less than the second; otherwise FALSE.
LTEReturns TRUE if the first argument is less than or equal to the second; otherwise FALSE.
MAXReturns the largest value in a set of values.
The function ignores empty cells, the logical values TRUE and FALSE, and text values. If the arguments contain no numbers, MAX returns 0 (zero).
MINReturns the smallest number in a set of values.
Empty cells, logical values, or text in the array or reference are ignored. If the arguments contain no numbers, MIN returns 0 (zero).
MINUSReturns the difference of two numbers.
MODReturns the remainder after number is divided by divisor. The result has the same sign as divisor.
MROUNDReturns a number rounded to the nearest multiple of the specified significance. The values of number and multiple must have the same sign.
MULTINOMIALReturns the ratio of the factorial of a sum of values to the product of factorials. The function takes from 1 to 255 numeric values which must be greater than or equal to 0.
MULTIPLYReturns the result of multiplying two numbers.
NEReturns TRUE if the first argument is not equal to the second; otherwise FALSE.
ODDReturns a number rounded up to the nearest odd integer.
PIReturns the number 3.14159265358979 (the mathematical constant pi, accurate to 15 digits).
POWReturns the result of a number raised to a given power.
Works with real numbers.
POWERReturns the result of a number raised to a given power.
Works with real numbers.
PRODUCTMultiplies all the numbers given as arguments and returns the product.
Only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.
QUOTIENTReturns the result of integer division without the remainder.
Works with real numbers.
RADIANSConverts degrees to radians.
RANDReturns a random number that is greater than or equal to 0 and less than 1.
Returns a new random number each time your spreadsheet recalculates.
RANDBETWEENReturns a random number between two specified numbers.
Returns a new random number each time your spreadsheet recalculates.
ROMANConverts an arabic numeral to roman.
ROUNDReturns a number rounded to a specified number of digits.
ROUNDDOWNReturns a number rounded down to a specified number of digits.
ROUNDUPReturns a number rounded up to a specified number of digits.
SECReturns the secant of an angle specified in radians.
Works with numeric values.
SECHReturns the hyperbolic secant of an angle specified in radians.
Works with numeric values.
SINReturns the sine of an angle specified in radians.
SINHReturns the hyperbolic sine of a real number.
SQRTReturns a positive square root of a number.
SQRTPIReturns the square root of a number multiplied by pi. The number must be greater than or equal to 0.
STDEVCalculates standard deviation based on data that represents a sample of population.
The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Empty cells, logical values, text, or error values in the array or reference are ignored.
STDEVACalculates standard deviation based on a sample.
The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Empty cells and text values in the array or reference are ignored.
STDEVPCalculates standard deviation based on the entire population of numbers.
The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
Empty cells, logical values, text, or error values in the array or reference are ignored.
STDEVPACalculates standard deviation based on the entire population given as arguments, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE).
The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
Error values cause errors.
STDEV.S
added in v4.3
Estimates standard deviation based on a sample (ignores logical values and text in the sample).
The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
If an argument is an array or reference, only values in that array or reference are used. Empty cells, logical values, text, or error values in the array or reference are ignored.
Error values cause errors.
SUBTOTALReturns a subtotal in a list or database.
SUMReturns the sum of supplied values.
Empty cells, logical values like TRUE, or text are ignored.
SUMPRODUCTMultiplies range of cells or arrays and returns the sum of products.
For valid products only numbers are multiplied.
Empty cells, logical values, and text are ignored.
Treats array entries that are not numeric as if they were zeros.
SUMSQReturns the sum of the squares of the arguments.
Empty cells, logical values, text, or error values in the array or reference are ignored.
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays.
The arguments should be either numbers or names, arrays, or references that contain numbers.
Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included.
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays.
The arguments should be either numbers or names, arrays, or references that contain numbers.
Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included.
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays.
The arguments should be either numbers or names, arrays, or references that contain numbers.
Empty cells, logical values, text, or error values in the array or reference are ignored. Zero values are included.
TANReturns the tangent of an angle specified in radians.
TANHReturns the hyperbolic tangent of a real number.
TRUNCTruncates a number to an integer by removing the fractional part of the number.
VARReturns the variance based on a sample.
Empty cells, logical values, text, or error values in the array or reference are ignored.
VARAReturns the variance based on a sample of the population, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE).
Empty cells and text values in the array or reference are ignored.
VARPReturns the variance of a population based on an entire population of numbers.
Empty cells, logical values, text, or error values in the array or reference are ignored.
VARPAReturns the variance of a population based on an entire population, including text (evaluate as 0) and logical values (evaluate as 1 for TRUE, and as 0 for FALSE).
Empty cells and text values in the array or reference are ignored.
VAR.S
added in v4.3
Returns the variance based on a sample (ignores logical values and text in the sample).
Empty cells, logical values, text, or error values in the array or reference are ignored.

Check the example in our snippet tool.

Regex functions

FunctionFormulaDescription
REGEXREPLACE=REGEXREPLACE(text, regular_expression, replacement)Replaces a part of a text string with a different text string using regular expressions.
REGEXMATCH=REGEXMATCH(text, regular_expression)Returns TRUE if a text string matches the pattern in the regular expression and FALSE if it doesn't.
REGEXEXTRACT=REGEXEXTRACT(text, regular_expression)Returns the part of the string that matches the pattern in the regular expression.

Check the example in our snippet tool.

String functions

FunctionFormulaDescription
ARRAYTOTEXT
added in v4.3
=ARRAYTOTEXT(array, [format])Returns an array of text values from any specified range, based on the format you specify (0 - concise (default) or 1 - strict format)
CHAR=CHAR(number)Returns the character (from the character set used by your computer) specified by a number. Number must be between 1 and 255.
CLEAN=CLEAN(text)Removes characters, which are not printed when you use the print option, from the text.
CODE=CODE(text)Returns a numeric code for the first character in a text string.
CONCATENATE=CONCATENATE(A1,"",B2:C3)Joins two or more text strings into one string.
DOLLAR=DOLLAR(number, decimals)Converts a number to text using the currency format, based on the number of digits to the right/left of the decimal point you specify (by default, 2).
EXACT=EXACT(text1, text2)Compares two strings and returns TRUE if they are exactly the same; otherwise, returns FALSE.
FIND=FIND(find_text, within_text, [start_num])Returns the position (as a number) of one text string inside another, starting at the position you specify (by default, 1).
FIXED=FIXED(number, [decimals], [no_commas])Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and converts the result to text. If no_commas is set to 1, the returned text won't include commas.
JOIN=JOIN(separator, value1, value2,...)Concatenates values using a specified separator.
LEFT=LEFT(text, count)Returns the first character or characters in a text string,
based on the number of characters you specify.
LEN=LEN(text)Returns the length of the specified string.
LOWER=LOWER(text)Converts all letters in the specified string to lowercase.
MID=MID(text, start, count)Returns a specific number of characters from a text string,
starting at the position you specify, based on the number
of characters you specify.
NUMBERVALUE=NUMBERVALUE (text, [decimal_separator], [group_separator])Converts a number in text format to numeric value, using specified decimal and group separators.
PROPER=PROPER(text)Sets the first character in each word to uppercase
and converts all other characters to lowercase.
REPLACE
added in v4.3
=REPLACE(old_text, start_num, num_chars, new_text),

where:
  • old_text - the text in which you want to replace some characters;
  • start_num - the position of the character in old_text that you want to replace with new_text;
  • num_chars - the number of characters to be replaced in old_text;
  • new_text - the text that will replace characters in old_text.
Replaces part of a text string, based on the number of characters you specify, with a different text string.
REPT=REPT(text, number_times)Repeats text a specified number of times.
RIGHT=RIGHT(text, count)Returns the last character or characters (rightmost) in a text string,
based on the number of characters you specify.
SEARCH=SEARCH(find_text, within_text, [start_num])Returns the position (as a number) of the first character of find_text inside within_text, starting at the position you specify (by default, 1).
SUBSTITUTE=SUBSTITUTE(text, old_text, new_text, [instance_num])Replaces old text with new text in a text string. If you specify instance_num, only that instance of old_text is replaced; otherwise, all instances are replaced.
T=T(value) returns text when given a text value and an empty string ("") for numbers, dates, and the logical TRUE/FALSE values.
TRIM=TRIM(text)Removes all spaces from text except for single spaces between words.
UPPER=UPPER(text)Converts text to uppercase.

Check the example in our snippet tool.

Other functions

FunctionExampleDescription
AND=AND(logical1, [logical2], ...)Returns either TRUE or FALSE depending on
whether multiple conditions are met or not.
CHOOSE=CHOOSE(index_num, value1, [value2], ...)Returns a value from the list of value arguments based on a position or index you specify.
FALSE=FALSE()Returns the logical value FALSE.
IF=IF(condition, [value_if_true], [value_if_false])Returns one value if a condition is TRUE
and another value if it's FALSE.
NOT=NOT(logical)Returns the opposite of a given logical or Boolean value.
OR=OR(logical1, [logical2], ...)Returns TRUE if at least one of the logical expressions is TRUE; otherwise, FALSE.
TRUE=TRUE()Returns the logical value TRUE.

Check the example in our snippet tool.

Getting cell formula

Starting with v4.1, you can get the formula applied to a cell via the getFormula() method. The method takes the id of the cell as a parameter:

var formula = spreadsheet.getFormula("B2");
// -> "ABS(C2)"