Excel Formulas
Overview
Unqork logic components let you use formulas in your configuration. Components that support formulas are the Calculator, Initializer, and Data Workflow components. You can enter formulas into the Output table of the Calculator and Initializer components. Using formulas in the Data Workflow operator depends on the operators you configure. For example, the Formula operator supports formula configuration.
NOTE If the formula used produces an error in Microsoft Excel, it will also return an error in Unqork. For more information on valid Microsoft Excel formulas, visit the Are you seeing a pound (#) error? section in Microsoft's How to avoid broken formulas 
support article.
Below is an extensive list of all supported Excel formulas in Unqork.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
A
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
ABS |
Returns the absolute value of a number. |
ABS(number) |
|
|
ACCRINT |
Calculates accrued interest for a security that pays periodic interest. |
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]) |
|
|
ACOS |
Returns the arccosine of a number or angle of a cosign. |
ACOS(number) |
|
|
ACOSH |
Returns the inverse hyperbolic cosine of a number. |
ACOSH(number) |
|
|
ACOT |
Returns the arccotangent or arc cotangent of a number. This is the angle, measured in radians, that has a cotangent equal to the given number. |
ACOT(number) |
|
|
ACOTH |
Returns the inverse hyperbolic arccotangent of a number. |
ACOTH(number) |
|
|
AGGREGATE |
Groups multiple values in a list or database into a single value. |
AGGREGATE(function_num, options, ref1, [ref2], …) |
|
|
AND |
Used to determine whether all conditions are true. |
AND(logical1, [logical2], ...) |
|
|
ARABIC |
Converts Roman numbers to Arabic numbers (0, 1, 2, 3, 4, 5, 6, 7, 8, and 9). |
ARABIC(text) |
|
|
ASIN |
Returns the arcsine of a number. |
ASIN(number) |
|
|
ASINH |
Returns the inverse hyperbolic sine of a number. |
ASINH(number) |
|
|
ATAN |
Returns the arctangent of a number. |
ATAN(number) |
|
|
ATAN2 |
Returns the arctangent from x and y-coordinates. |
ATAN2(x_num, y_num) |
|
|
ATANH |
Calculates the inverse hyperbolic tangent of a number. |
ATANH(number) |
|
|
AVEDEV |
Returns the average deviation of a set of values. |
AVEDEV(number1, [number2], ...) |
|
|
AVERAGE |
Returns the arithmetic mean of the numbers provided. |
AVERAGE(number1, [number2], ...) |
|
|
AVERAGEA |
Returns the arithmetic mean of the numbers, text, and logical values provided. |
AVERAGEA(value1, [value2], ...) |
|
|
AVERAGEIF |
Returns the arithmetic mean of the cells in a range that meet a given criteria. |
AVERAGEIF(range, criteria, [average_range]) |
|
|
AVERAGEIFS |
Returns the arithmetic mean of the cells in a range that meet a given criteria. |
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) |
B
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
BASE |
Returns a text representation by converting a number into a supplied base (radix). |
BASE(number, radix, [min_length]) |
|
|
BESSELI |
Returns the modified Bessel function In(x). |
BESSELI(x, n) |
|
|
BESSELJ |
Returns the Bessel function Jn(x). |
BESSELJ(x, n) |
|
|
BESSELK |
Returns the modified Bessel function Kn(x). |
BESSELK(x, n) |
|
|
BESSELY |
Returns the Bessel function Yn(x). |
BESSELY(x, n) |
|
|
BETA.DIST |
Returns the beta cumulative distribution function. Used when studying variation in the percentage of a value across samples. |
BETA.DIST(x, alpha, beta, cumulative, [A], [B]) |
|
|
BETA.INV |
Returns the inverse of the beta cumulative distribution function. Used when studying variation in the percentage of a value across samples. |
BETA.INV(probability, alpha, beta, [A], [B]) |
|
|
BETADIST |
Returns the beta cumulative distribution function. Used when studying variation in the percentage of a value across samples. |
BETADIST(x, alpha, beta, cumulative, [A], [B]) |
|
|
BETAINV |
Returns the inverse of the beta cumulative distribution function. Used when studying variation in the percentage of a value across samples. |
BETAINV(probability, alpha, beta, [A], [B]) |
|
|
BIN2DEC |
Converts a binary number to decimal. |
BIN2DEC(number) |
|
|
BIN2HEX |
Converts a binary number to hexadecimal. |
BIN2HEX(number, [places]) |
|
|
BIN2OCT |
Converts a binary number to octal. |
BIN2OCT(number, [places]) |
|
|
BINOM.DIST |
Returns the individual term binomial distribution probability. Use this function when the outcomes of a test are success or failure. |
BINOM.DIST(number_s, trials, probability_s, cumulative) |
|
|
BINOM.DIST.RANGE |
Returns the individual term binomial distribution probability. Use thisfunction when the outcomes of a test are success or failure. |
BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) |
|
|
BINOM.INV |
Returns the smallest number of successes where the binomal distribution is less than or equal to a particular value. The result shows the probability of a specific number of successes from a specific number of tests. |
BINOM.INV(trials, probability_s, alpha) |
|
|
BINOMDIST |
Calculates the probability of getting less than or equal to a particular value in a binomial distribution. |
BINOMDIST(number_s, trials, probability_s, cumulative) |
|
|
BITAND |
Returns a decimal number representing the Bitwise And of two numbers. |
BITAND(number1, number2) |
|
|
BITLSHIFT |
Returns a value number shifted left by the number of bits specified. |
BITLSHIFT(number, shift_amount) |
|
|
BITOR |
Returns a decimal number representing the Bitwise Or of two numbers. |
BITOR(number1, number2) |
|
|
BITRSHIFT |
Returns a value number shifted right by the number of bits specified. |
BITRSHIFT(number, shift_amount) |
|
|
BITXOR |
Returns a decimal number based on the comparison of two numbers. This number represents the Exclusive Or of two numbers. If both digits at each position are not equal, it returns a 1 for that position. If both digits at each position are equal, it returns a 0 for that position. |
BITXOR(number1, number2) |
C
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
CEILING |
Rounds real a number to the nearest integer greater than or equal to the real number. |
CEILING(number, [significance], [mode]) |
|
|
CEILINGMATH |
Rounds real a number to the nearest integer greater than or equal to the real number. |
CEILINGMATH(number, [significance], [mode]) |
|
|
CEILING.PRECISE |
Rounds real a number to the nearest integer greater than or equal to the real number. Rounding happens regardless of the sign of the number. |
CEILING.PRECISE(number, [significance]) |
|
|
CHAR |
Returns a character specified by the code number. For example, CHAR(10) returns a line break on Windows and CHAR(13) returns a line break on the Mac. |
CHAR(number) |
|
|
CHISQ.DIST |
Calculates the chi-squared distribution, showing the variation in the percentage across samples. |
CHISQ.DIST(x, deg_freedom, cumulative) |
|
|
CHISQ.DIST.RT |
Calculates the right-tailed or upper probability of the chi-squared distribution. Used when comingare and observing expected values. |
CHISQ.DIST.RT(x, deg_freedom) |
|
|
CHISQ.INV |
Returns the inverse of the one-tailed probability of the chi-squared distribution. |
CHISQ.INV(probability, deg_freedom) |
|
|
CHISQ.INV.RT |
Returns the inverse of the right-tailed probability of the chi-squared distribution. |
CHISQ.INV.RT(probability, deg_freedom) |
|
|
CHOOSE |
Returns a value from a list of values. |
CHOOSE(index_num, value1, [value2], ...) |
|
|
CLEAN |
Returns text with all line breaks and non-printable characters removed. |
CLEAN(text) |
|
|
CODE |
Returns a numeric code for a specified character in a text string. |
CODE(text) |
|
|
COLUMN |
Returns the column number of a reference. |
COLUMN(array, index) |
|
|
COLUMNS |
Returns the number of columns in a reference. |
COLUMNS(array) |
|
|
COMBIN |
Returns the number of combinations for a set of objects. |
COMBIN(number, number_chosen) |
|
|
COMBINA |
Returns the number of combinations for a set of objects, including repetitions. |
COMBINA(number, number_chosen) |
|
|
COMPLEX |
Converts coefficients (real and imaginary) into a complex number. |
COMPLEX(real_num, i_num, [suffix]) |
|
|
CONCATENATE |
Combines several text strings into one string. |
CONCATENATE(text1, [text2], ...) |
|
|
CONFIDENCE |
Returns the confidence interval for a probability and sample size. |
CONFIDENCE(alpha, standard_dev, size) |
|
|
CONFIDENCE.NORM |
Uses the normal distribution to return the confidence interval for a population mean. |
CONFIDENCE.NORM(alpha, standard_dev, size) |
|
|
CONFIDENCE.T |
Uses a student's t-distribution to return the confidence interval for a population mean. |
CONFIDENCE.T(alpha, standard_dev, size) |
|
|
CONVERT |
Converts a number from one data type to another. |
CONVERT(number, from_unit, to_unit) |
|
|
CORREL |
Returns the correlation coefficient or relationship between two data sets. |
CORREL(array1, array2) |
|
|
COS |
Returns the cosine of a number. |
COS(number) |
|
|
COSH |
Returns the hyperbolic cosine of a number. |
COSH(number) |
|
|
COT |
Returns the cotangent of an angle specified in radians.. |
COT(number) |
|
|
COTH |
Returns the hyperbolic cotangent of a hyperbolic angle. |
COTH(number) |
|
|
COUNT |
Counts how many numbers are in a list of arguments. |
COUNT(value1, [value2], ...) |
|
|
COUNTA |
Counts how many values are in a list of arguments. |
COUNTA(value1, [value2], ...) |
|
|
COUNTBLANK |
Returns the number of empty cells. |
COUNTBLANK(range) |
|
|
COUNTIF |
Counts the number of cells for the specified criteria. |
COUNTIF(range, criteria) |
|
|
COUNTIFS |
Counts the number of cells meeting multiple specified criteria. |
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …) |
|
|
COVARIANCE.P |
Returns covariance, the average of the products of paired deviations. |
COVARIANCE.P(array1, array2) |
|
|
COVARIANCE.S |
Returns the sample covariance, the average of the products deviations for each data point pair in two data sets. |
COVARIANCE.S(array1, array2) |
|
|
CSC |
Returns the cosecant of an angle. |
CSC(number) |
|
|
CSCH |
Returns the hyperbolic cosecant of an angle. |
CSCH(number) |
|
|
CUMIPMT |
Returns the cumulative interest paid between a start and end period. |
CUMIPMT(rate, nper, pv, start_period, end_period, type) |
|
|
CUMPRINC |
Returns the cumulative principal paid between a start and end period. |
CUMPRINC(rate, nper, pv, start_period, end_period, type) |
D
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
DATE |
Calculates the serial number of a particular date. The date is represented as the number of days since January 1, 1900. |
DATE(year,month,day) |
|
|
DATEVALUE |
Parses a date string and returns it as the number of full days since nullDate. |
DATEVALUE(date_text) |
|
|
DAY |
Converts a serial number to a day of the month (1-31). |
DAY(serial_number) |
|
|
DAYS |
Calculates the number of days between two dates. |
DAYS(end_date, start_date) |
|
|
DAYS360 |
Calculates the number of days between two dates based on a 360-day year. |
DAYS360(start_date,end_date,[method]) |
|
|
DB |
Returns the depreciation of an asset for a specified period using the fixed-declining balance method. |
DB(cost, salvage, life, period, [month]) |
|
|
DDB |
Returns the depreciation of an asset for a specified period using the double-declining balance method. |
DDB(cost, salvage, life, period, [factor]) |
|
|
DEC2BIN |
Converts a decimal number to binary. |
DEC2BIN(number, [places]) |
|
|
DEC2HEX |
Converts a decimal number to hexadecimal. |
DEC2HEX(number, [places]) |
|
|
DEC2OCT |
Converts a decimal number to octal. |
DEC2OCT(number, [places]) |
|
|
DECIMAL |
Converts a text number into a decimal number. |
DECIMAL(text, radix) |
|
|
DEGREES |
Converts radians to degrees. |
DEGREES(angle) |
|
|
DELTA |
Compares the difference between two values. |
DELTA(number1, [number2]) |
|
|
DEVSQ |
Returns the sum of squares of deviations for a dataset. |
DEVSQ(number1, [number2], ...) |
|
|
DOLLAR |
Converts a number to text using the currency format ($#,##0.00). |
DOLLAR(number, [decimals]) |
|
|
DOLLARDE |
Converts a dollar price fraction to a decimal. |
DOLLARDE(fractional_dollar, fraction) |
|
|
DOLLARFR |
Converts a dollar price fraction to a decimal. |
DOLLARFR(decimal_dollar, fraction) |
E
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
EDATE |
Returns a date a set number of months in the past or future from the specified date. |
EDATE(start_date, months) |
|
|
EFFECT |
Returns the effective annual interest rate. |
EFFECT(nominal_rate, npery) |
|
|
EOMONTH |
Returns the serial number of the last day of the month based on the specified number of months before or after the start date. |
EOMONTH(start_date, months) |
|
|
ERF |
Returns the error function. |
ERF(lower_limit,[upper_limit]) |
|
|
ERFC |
Returns the complementary error function. |
ERFC(x) |
|
|
EVEN |
Rounds a number to the nearest even integer. |
EVEN(number) |
|
|
EXACT |
Determines whether two text values are identical. |
EXACT(text1, text2) |
|
|
EXP |
Returns Euler's number (e), raised to the power of a given number. Constant e relates to exponential growth and decay. Its value is approximately 2.71828. |
EXP(number) |
|
|
EXPON.DIST |
Returns the exponential distribution. |
EXPON.DIST(x,lambda,cumulative) |
|
|
EXPONDIST |
Returns the exponential distribution. |
EXPONDIST(x,lambda,cumulative) |
F
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
F.DIST |
Returns the F probability distribution. Used when determining whether two datasets have different degrees of diversity . |
F.DIST(x,deg_freedom1,deg_freedom2,cumulative) |
|
|
F.DIST.RT |
Returns the F probability distribution. Used when determining whether two datasets have different degrees of diversity. |
F.DIST.RT(x,deg_freedom1,deg_freedom2) |
|
|
F.INV |
Returns the inverse of the F probability distribution. |
F.INV(probability,deg_freedom1,deg_freedom2) |
|
|
F.INV.RT |
Returns the inverse of the F probability distribution. |
F.INV.RT(probability,deg_freedom1,deg_freedom2) |
|
|
FACT |
Counts the number of possible arrangements for a group of items. |
FACT(number) |
|
|
FACTDOUBLE |
Returns the double factorial of a number. |
FACTDOUBLE(number) |
|
|
FALSE |
Returns the value of false. |
FALSE() |
|
|
FDIST |
Calculates the right-tailed F probability distribution, which measures the degree of diversity between two datasets. |
FDIST(x,deg_freedom1,deg_freedom2) |
|
|
FDISTRT |
Calculates the right-tailed F probability distribution, which measures the degree of diversity between two data sets. |
FDISTRT(x,deg_freedom1,deg_freedom2) |
|
|
FIND |
Returns the location of one text string inside another. |
FIND(find_text, within_text, [start_num]) |
|
|
FINV |
Returns the inverse of the F probability distribution. |
FINV(probability,deg_freedom1,deg_freedom2) |
|
|
FINVRT |
Returns the inverse of the right-tailed F probability distribution. |
FINVRT(probability,deg_freedom1,deg_freedom2) |
|
|
FISHER |
Returns the Fisher transformation for the supplied value. Used when testing hypotheses on the correlation coefficient. |
FISHER(x) |
|
|
FISHERINV |
Returns the inverse of the Fisher transformation. |
FISHERINV(y) |
|
|
FIXED |
Converts a number to text with a fixed number of decimals. |
FIXED(number, [decimals], [no_commas]) |
|
|
FLOOR |
Rounds real a number to the nearest integer less than or equal to the real number. |
FLOOR(number, significance) |
|
|
FORECAST |
Returns a predicted future value based on the existing values. |
FORECAST(x, known_y's, known_x's) |
|
|
FREQUENCY |
Calculates how often values occur in the specified ranges. |
FREQUENCY(data_array, bins_array) |
|
|
FV |
Returns the future value of an investment. |
FV(rate,nper,pmt,[pv],[type]]) |
|
|
FVSCHEDULE |
Returns the future value of an investment after applying a series of specified interest rates. |
FVSCHEDULE(principal, schedule) |
G
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
GAMMA |
Returns the gamma function value. |
GAMMA(number) |
|
|
GAMMA.DIST |
Returns the gamma distribution. |
GAMMA.DIST(x,alpha,beta,cumulative) |
|
|
GAMMA.INV |
Returns the inverse of the gamma cumulative distribution. |
GAMMA.INV(probability,alpha,beta) |
|
|
GAMMADIST |
Returns the gamma distribution. |
GAMMADIST(x,alpha,beta,cumulative) |
|
|
GAMMAINV |
Returns the inverse of the gamma cumulative distribution. |
GAMMAINV(probability,alpha,beta) |
|
|
GAMMALN |
Calculates the natural logarithm of the gamma function, Γ(x). |
GAMMALN(x) |
|
|
GAMMALN.PRECISE |
Calculates the natural logarithm of the gamma function, Γ(x). |
GAMMALN.PRECISE(x) |
|
|
GAUSS |
Returns the number of standard deviations from the mean. |
GAUSS(z) |
|
|
GCD |
Returns the greatest common divisor of two or more integers. |
GCD(number1, [number2], ...) |
|
|
GEOMEAN |
Calculates the geometric mean for a set of numbers. |
GEOMEAN(number1, [number2], ...) |
|
|
GESTEP |
Tests whether a specified number is greater than a specified step size. |
GESTEP(number, [step]) |
|
|
GROWTH |
Calculates predicted exponential growth using the supplied data. |
GROWTH(known_y's, [known_x's], [new_x's], [const]) |
H
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
HARMEAN |
Calculates the harmonic mean for a set of values. |
HARMEAN(number1, [number2], ...) |
|
|
HEX2BIN |
Converts a hexadecimal (Base16) number to a binary (Base 2) number. |
HEX2BIN(number, [places]) |
|
|
HEX2DEC |
Converts a hexadecimal (Base 16) number to decimal number. |
HEX2DEC(number) |
|
|
HEX2OCT |
Converts a hexadecimal (Base 16) number to octal (Base 8) number. |
HEX2OCT(number, [places]) |
|
|
HOUR |
Converts a serial number to an hour format (9:00 PM). |
HOUR(serial_number) |
|
|
HYPGEOM.DIST |
Returns the hypergeometric distribution or probability of a given number of sample successes. |
HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative) |
|
|
HYPGEOMDIST |
Returns the hypergeometric distribution or probability of a given number of sample successes. |
HYPGEOMDIST(sample_s,number_sample,population_s,number_pop,cumulative) |
I
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
IF |
Compares a value and expectation based on a condition. |
IF(logical_test, value_if_true, value_if_false) |
|
|
IMABS |
Gets the absolute value of a complex number. |
IMABS(inumber) |
|
|
IMAGINARY |
Gets the imaginary coefficient of a complex number. |
IMAGINARY(inumber) |
|
|
IMARGUMENT |
Returns an argument of a complex number. |
IMARGUMENT(inumber) |
|
|
IMCONJUGATE |
Gets the complex conjugate of a complex number. |
IMCONJUGATE(inumber) |
|
|
IMCOS |
Returns the cosine of a complex number. |
IMCOS(inumber) |
|
|
IMCOSH |
Calculates the hyperbolic cosine of a complex number. |
IMCOSH(inumber) |
|
|
IMCOT |
Returns the cotangent of a complex number. |
IMCOT(inumber) |
|
|
IMCSC |
Gets the cosecant of a complex number. |
IMCSC(inumber) |
|
|
IMCSCH |
Returns the hyperbolic cosecant of a complex number. |
IMCSCH(inumber) |
|
|
IMDIV |
Returns the quotient of two complex numbers. |
IMDIV(inumber1, inumber2) |
|
|
IMEXP |
Returns the complex exponential of a complex number. |
IMEXP(inumber) |
|
|
IMLN |
Returns the natural logarithm of a complex number. |
IMLN(inumber) |
|
|
IMLOG10 |
Returns the common (Base-10) logarithm of a complex number. |
IMLOG10(inumber) |
|
|
IMLOG2 |
Returns the Base-2 logarithm of a complex number. |
IMLOG2(inumber) |
|
|
IMPOWER |
Returns a complex number raised to a specified power. |
IMPOWER(inumber, number) |
|
|
IMPRODUCT |
Gets the product of one or more complex numbers. |
IMPRODUCT(inumber1, [inumber2], ...) |
|
|
IMREAL |
Gets the real coefficient of a complex number. |
IMREAL(inumber) |
|
|
IMSEC |
Returns the secant of a complex number. |
IMSEC(inumber) |
|
|
IMSECH |
Gets the hyperbolic secant of a complex number. |
IMSECH(inumber) |
|
|
IMSIN |
Returns the sine of a complex number. |
IMSIN(inumber) |
|
|
IMSINH |
Returns the hyperbolic sine of a complex number. |
IMSINH(inumber) |
|
|
IMSQRT |
Gets the square root of a complex number. |
IMSQRT(inumber) |
|
|
IMSUB |
Returns the difference between two complex numbers. |
IMSUB(inumber1, inumber2) |
|
|
IMSUM |
Returns the sum of two or more complex numbers. |
IMSUM(inumber1, [inumber2], ...) |
|
|
IMTAN |
Returns the tangent of a complex number. |
IMTAN(inumber) |
|
|
INT |
Rounds a number down to the nearest integer. |
INT(number) |
|
|
INTERCEPT |
Gets the intercept of the linear regression line. |
INTERCEPT(known_y's, known_x's) |
|
|
IPMT |
Calculates the interest portion of a loan or investment based on the principle payment and payment period. |
IPMT(rate, per, nper, pv, [fv], [type]]) |
|
|
IRR |
Calculates the internal rate of return and the expected compound annual rate of return to be earned on an investment. |
IRR(values, [guess]) |
|
|
ISBLANK |
Returns true if the value is blank. |
ISBLANK(value) |
|
|
ISEVEN |
Returns true if the number is even. |
ISEVEN(Value) |
|
|
ISLOGICAL |
Returns true if the value is a logical value. |
ISLOGICAL(value) |
|
|
ISNONTEXT |
Returns true if the value is not text. The same result occurs if the value's cell is empty. |
ISNONTEXT(value) |
|
|
ISNUMBER |
Returns true if the value is a number. |
ISNUMBER(value) |
|
|
ISODD |
Returns true if the number is odd. |
ISODD(number) |
|
|
ISOWEEKNUM |
Returns an ISO week number of the year for the specified date. |
ISOWEEKNUM(date) |
|
|
ISPMT |
Calculates interest paid during a specific period of a loan or investment. |
ISPMT(rate, per, nper, pv) |
|
|
ISTEXT |
Returns true if the value is text. |
ISTEXT(value) |
K-L
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
KURT |
Calculates the kurtosis (peakedness or flatness of a distribution) of a dataset. |
KURT(number1, [number2], ...) |
|
|
LARGE |
Returns the k-th largest value in a dataset. |
LARGE(array, k) |
|
|
LCM |
Returns the least common multiple of the specified numbers. |
LCM(number1, [number2], ...) |
|
|
LEFT |
Returns the length of a specified text. |
LEFT(text, [num_chars]) |
|
|
LEN |
Returns the number of characters in a text string. |
LEN(text) |
|
|
LINEST |
Returns an array describing a linear trend. |
LINEST(known_y's, known_x's) |
|
|
LN |
Returns the natural logarithm of a number. |
LN(number) |
|
|
LOG |
Returns the logarithm of a number to a specified base. |
LOG(number, base) |
|
|
LOG10 |
Returns the Base-10 logarithm of a number. |
LOG10(number) |
|
|
LOGEST |
Returns an array of the exponential curve based on the specified data. |
LOGEST(known_y's, known_x's) |
|
|
LOGNORM.DIST |
Returns the cumulative lognormal distribution. Use when analyzing data transformed by a logarithm. |
LOGNORM.DIST(x,mean,standard_dev,cumulative) |
|
|
LOGNORM.INV |
Returns the inverse of the lognormal cumulative distribution. |
LOGNORM.INV(probability, mean, standard_dev) |
|
|
LOGNORMDIST |
Returns the cumulative lognormal distribution. Use when analyzing data transformed by a logarithm. |
LOGNORMDIST(x,mean,standard_dev) |
|
|
LOGNORMINV |
Returns the inverse of the lognormal cumulative distribution. |
LOGNORMINV(probability, mean, standard_dev) |
|
|
LOWER |
Returns text converted to lowercase. |
LOWER(text) |
M
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
MATCH |
Returns the relative position of an item in an array matching a specified value. |
MATCH(lookup_value, lookup_array, [match_type]) |
|
|
MAX |
Returns the largest number of the specified values. |
MAX(number1, [number2], ...) |
|
|
MAXA |
Returns the largest number of the specified values, including numbers, text, and logical values. |
MAXA(value1,[value2],...) |
|
|
MEDIAN |
Returns the median of the specified numbers. |
MEDIAN(number1, [number2], ...) |
|
|
MID |
Returns the number of characters in a text string starting, based on the position you specify. |
MID(text, start_num, num_chars) |
|
|
MIN |
Returns the minimum value in a list of arguments. |
MIN(number1, [number2], ...) |
|
|
MINA |
Returns the smallest of the specified values, including numbers, text, and logical values. |
MINA(value1, [value2], ...) |
|
|
MINUTE |
Converts a serial number to a minute. |
MINUTE(serial_number) |
|
|
MIRR |
Considers the cost of an investment and the interest received on reinvestment to determine the modified internal rate of return for periodic cash flows. |
MIRR(values, finance_rate, reinvest_rate) |
|
|
MOD |
Returns the remainder after the number is divided by a specified divisor. |
MOD(number, divisor) |
|
|
MODE.MULT |
Returns a vertical array of the most repetitive values for a data range. |
MODE.MULT(number1,[number2],...) |
|
|
MODE.SNGL |
Returns the value that occurs most frequently in a dataset. |
MODE.SNGL(number1,[number2],...) |
|
|
MODEMULT |
Returns a vertical array of the most repetitive values for a data range. |
MODEMULT(number1,[number2],...) |
|
|
MODESNGL |
Returns the value that occurs most frequently in a data set. |
MODESNGL(number1,[number2],...) |
|
|
MONTH |
Converts a serial number to a month or number (1-12). |
MONTH(serial_number) |
|
|
MROUND |
Returns a number rounded up from zero. |
MROUND(number, multiple) |
|
|
MULTINOMIAL |
Returns the multinomial probability distribution of a set of numbers. |
MULTINOMIAL(number1, [number2], ...) |
N-O
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
NEGBINOM.DIST |
Returns the probability of receiving less than or equal to a particular value in a negative binomial distribution. |
NEGBINOM.DIST(number_f,number_s,probability_s,cumulative) |
|
|
NEGBINOMDIST |
Returns the probability of receiving less than or equal to a particular value in a negative binomial distribution. |
NEGBINOMDIST(number_f,number_s,probability_s) |
|
|
NETWORKDAYS |
Returns the number of whole workdays between two dates. |
NETWORKDAYS(start_date, end_date, [holidays]) |
|
|
NOMINAL |
Returns the annual nominal interest rate. |
NOMINAL(effect_rate, npery) |
|
|
NORM.DIST |
Returns the normal distribution for the specified mean and standard deviation. |
NORM.DIST(x,mean,standard_dev,cumulative) |
|
|
NORM.INV |
Returns the inverse of the standard normal cumulative distribution for a number. |
NORM.INV(probability,mean,standard_dev) |
|
|
NORM.S.DIST |
Returns the normal distribution with a mean of 0 and a standard deviation of 1. |
NORM.S.DIST(z,cumulative) |
|
|
NORM.S.INV |
Returns the inverse of the standard normal cumulative distribution with a mean of 0 and a standard deviation of 1. |
NORM.S.INV(probability) |
|
|
NORMDIST |
Returns the normal distribution for the specified mean and standard deviation. |
NORMDIST(x,mean,standard_dev,cumulative) |
|
|
NORMINV |
Retruns the inverse of the standard normal cumulative distribution for a number. |
NORMINV(probability,mean,standard_dev) |
|
|
NORMSDIST |
Returns the normal distribution for the specified mean and standard deviation. |
NORMSDIST(z) |
|
|
NORMSINV |
Returns the inverse of the cumulative standardized normal distribution. |
NORMSINV(probability) |
|
|
NOT |
Determines whether a condition is false. |
NOT(logical) |
|
|
NOW |
Returns the serial number of the current date and time. |
NOW() |
|
|
NPER |
Returns the number of periods for a loan or investment. |
NPER(rate,pmt,pv,[fv],[type]) |
|
|
NPV |
Returns the net present value of an investment using a discount rate and a series of periodic cash flows. |
NPV(rate,value1,[value2],...) |
|
|
OCT2BIN |
Converts an octal number to binary. |
OCT2BIN(number, [places]) |
|
|
OCT2DEC |
Converts an octal number to decimal. |
OCT2DEC(number) |
|
|
OCT2HEX |
Converts an octal number to hexadecimal. |
OCT2HEX(number, [places]) |
|
|
ODD |
Rounds a number to the nearest odd integer. |
ODD(number) |
|
|
OR |
Determines whether only one condition is true. |
OR(logical1, [logical2], ...) |
P
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
PDURATION |
Returns the number of periods needed for an investment to reach a specified amount. |
PDURATION(rate, pv, fv) |
|
|
PEARSON |
Returns the Pearson product moment correlation coefficient, showing how strongly the two variables correlate. |
PEARSON(array1, array2) |
|
|
PERCENTILEEXC |
Calculates the k-th percentile of values in a range, where k is 0 to 1. |
PERCENTILEEXC(array,k) |
|
|
PERCENTILEINC |
Calculates the k-th percentile of values in a range, where k is 0 to 1. |
PERCENTILEINC(array,k) |
|
|
PERCENTRANKEXC |
Returns the rank of a value in a dataset as a percentage. It also indicates how many values are less than or equal to the value. |
PERCENTRAKEXC(array,x,[significance]) |
|
|
PERCENTRANKINC |
Returns the rank of a value in a data set as a percentage. It also indicates how many values are less than or equal to the value. |
PERCENTRANKINC(array,x,[significance]) |
|
|
PERMUT |
Returns the number of permutations of a specified number of objects. |
PERMUT(number, number_chosen) |
|
|
PERMUTATIONA |
Returns the number of permutations of a specified number of objects with repetitions. |
PERMUTATIONA(number, number-chosen) |
|
|
PHI |
Returns the value of the density function for a standard normal distribution. |
PHI(x) |
|
|
PI |
Returns the value of pi. |
PI() |
|
|
PMT |
Returns the periodic payment for a loan or annuity. |
PMT(rate, nper, pv, [fv], [type]) |
|
|
POISSON.DIST |
Returns the Poisson distribution: the number of events occurring in a given time interval. |
POISSON.DIST(x,mean,cumulative) |
|
|
POISSONDIST |
Returns the Poisson distribution: the number of events occurring in a given time interval. |
POISSONDIST(x,mean,cumulative) |
|
|
POWER |
Returns the result of a given number raised to a specified power. |
POWER(number, power) |
|
|
PPMT |
Returns the principal portion of a loan payment for a specified period. |
PPMT(rate, per, nper, pv, [fv], [type]) |
|
|
PROB |
Returns the probability that values in a range are between a lower and upper limit. |
PROB(x_range, prob_range, [lower_limit], [upper_limit]) |
|
|
PRODUCT |
Multiplies the specified values. |
PRODUCT(number1, [number2], ...) |
|
|
PROPER |
Capitalizes the first letter of a text string. |
PROPER(text) |
|
|
PV |
Calculates the present value of a loan or investment. |
PV(rate, nper, pmt, [fv], [type]]) |
Q-R
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
QUARTILE.EXC |
Returns the quartile of a dataset, based on percentile values from 0 to 1. |
QUARTILE.EXC(array, quart) |
|
|
QUARTILE.INC |
Returns the quartile for a dataset. |
QUARTILE.INC(array,quart) |
|
|
QUARTILEEXC |
Returns the quartile of a dataset, based on percentile values from 0 to 1. |
QUARTILEEXC(array,quart) |
|
|
QUARTILEINC |
Returns the quartile for a dataset. |
QUARTILEINC(array,quart) |
|
|
QUOTIENT |
Returns the integer portion of a division. |
QUOTIENT(numerator, denominator) |
|
|
RADIANS |
Converts degrees to radians. |
RADIANS(angle) |
|
|
RAND |
Returns a random number equal or greater to 0, and less than 1. |
RAND() |
|
|
RANDBETWEEN |
Returns a random number between two numbers. |
RANDBETWEEN(bottom, top) |
|
|
RANK.AVG |
Returns the rank of a number against a list of numbers. |
RANK.AVG(number,ref,[order]) |
|
|
RANK.EQ |
Returns the rank of a number against a list of numbers. Duplicate numbers receive the same rank. |
RANK.EQ(number,ref,[order]) |
|
|
RANKAVG |
Returns the rank of a number against a list of numbers. |
RANKAVG(number,ref,[order]) |
|
|
RANKEQ |
Returns the rank of a number against a list of numbers. Duplicate numbers receive the same rank. |
RANKEQ(number,ref,[order]) |
|
|
RATE |
Returns the interest rate on a loan per period. |
RATE(nper, pmt, pv, [fv], [type], [guess]) |
|
|
REPLACE |
Replaces characters in string with the specified characters and number of characters in the string. |
REPLACE(old_text, start_num, num_chars, new_text) |
|
|
REPT |
Repeats text a specified number of times. |
REPT(text, number_times) |
|
|
ROMAN |
Converts a number to a Roman numeral. |
ROMAN(number) |
|
|
ROUND |
Rounds a number to a specified number of digits. |
ROUND(number, num_digits) |
|
|
ROUNDDOWN |
Rounds a number down toward zero. |
ROUNDDOWN(number, num_digits) |
|
|
ROUNDUP |
Rounds a number up away from zero. |
ROUNDUP(number, num_digits) |
|
|
ROW |
Returns the row number of a reference. |
ROW(array, index) |
|
|
ROWS |
Returns the number of rows of a reference. |
ROWS(array) |
|
|
RRI |
Returns an equivalent interest rate for the growth of an investment. |
RRI(nper, pv, fv) |
|
|
RSQ |
Returns the square of the Pearson product moment correlation coefficient. |
RSQ(known_y's,known_x's) |
S
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
SEARCH |
Locates specified text in other text (not case-sensitive). |
SEARCH(find_text,within_text,[start_num]) |
|
|
SEC |
Returns the secant of an angle. |
SEC(number) |
|
|
SECH |
Returns the hyperbolic secant of an angle. |
SECH(number) |
|
|
SECOND |
Converts a serial number to a time in seconds (0-59). |
SECOND(serial_number) |
|
|
SERIESSUM |
Returns the sum of a power series. |
SERIESSUM(x, n, m, coefficients) |
|
|
SIGN |
Returns the sign of a real number. |
SIGN(number) |
|
|
SIN |
Returns the sine of a number. |
SIN(number) |
|
|
SINH |
Returns the hyperbolic sine of a number. |
SINH(number) |
|
|
SKEW |
Returns the skewness of a distribution for a dataset. |
SKEW(number1, [number2], ...) |
|
|
SKEW.P |
Returns the skewness of a distribution for a dataset based on a population. Used when determining the characterization of the degree of asymmetry of a distribution around its mean. |
SKEW.P(number1, [number2], ...) |
|
|
SKEWP |
Returns the skewness of a distribution for a data set based on a population. Used when determining the characterization of the degree of asymmetry of a distribution around its mean. |
SKEWP(number1, [number2], ...) |
|
|
SLN |
Returns the depreciation of an asset for one period using straight-line depreciation. |
SLN(cost, salvage, life) |
|
|
SLOPE |
Returns the slope of the linear regression line. |
SLOPE(known_y's, known_x's) |
|
|
SMALL |
Returns the smallest value in a list. |
SMALL(array, k) |
|
|
SQRT |
Returns a positive square root. |
SQRT(number) |
|
|
SQRTPI |
Returns the square root of (number * pi). |
SQRTPI(number) |
|
|
STANDARDIZE |
Returns a normalized value. |
STANDARDIZE(x, mean, standard_dev) |
|
|
STDEV.P |
Calculates standard deviation based on the whole population. |
STDEV.P(number1,[number2],...) |
|
|
STDEV.S |
Estimates standard deviation based on a sample. |
STDEV.S(number1,[number2],...) |
|
|
STDEVA |
Estimates standard deviation based on a sample of numbers, text, and logical values. |
STDEVA(value1, [value2], ...) |
|
|
STDEVP |
Calculates standard deviation based on the whole population. |
STDEVP(number1,[number2],...) |
|
|
STDEVPA |
Calculates standard deviation based on the whole population, including numbers, text, and logical values. |
STDEVPA(value1, [value2], ...) |
|
|
STDEVS |
Estimates standard deviation based on a sample. |
STDEVS(number1,[number2],...) |
|
|
STEYX |
Calculates the standard error or the error amount in the prediction of y for an x. |
STEYX(known_y's, known_x's) |
|
|
SUBSTITUTE |
Substitutes new text for old text in a text string. |
SUBSTITUTE(text, old_text, new_text, [instance_num]) |
|
|
SUBTOTAL |
Returns a subtotal for a list or database. |
SUBTOTAL(function_num,ref) |
|
|
SUM |
Sums the values of the specified cells. |
SUM(number1,[number2],...) |
|
|
SUMIF |
Adds the cells specified by a given criteria. |
SUMIF(range, criteria) |
|
|
SUMIFS |
Adds the contents of cells in a range that meets multiple criteria. |
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) |
|
|
SUMPRODUCT |
Returns the sum of the products of corresponding array components. |
SUMPRODUCT(array1, [array2], [array3], ...) |
|
|
SUMSQ |
Returns the sum of the squares for a series of values. |
SUMSQ(number1, [number2], ...) |
|
|
SUMX2MY2 |
Returns the sum of the difference of squares of corresponding values in two arrays. |
SUMX2MY2(array_x, array_y) |
|
|
SUMX2PY2 |
Calculates the sum of squares, and returns the sum of the squares of corresponding items. |
SUMX2PY2(array_x, array_y) |
|
|
SUMXMY2 |
Returns the sum of squares of differences of corresponding values in two arrays. |
SUMXMY2(array_x, array_y) |
|
|
SWITCH |
Returns the first matching value when comparing one value against a list of values. |
SWITCH(expression1, value1, [expression2, value2], ...) |
|
|
SYD |
Returns the sum-of-years depreciation of an asset for a specified period. |
SYD(cost, salvage, life, per) |
T
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
T |
Returns a value for referenced text. |
T(value) |
|
|
T.DIST |
Returns the student's left-tailed t-distribution. |
T.DIST(x,deg_freedom, cumulative) |
|
|
T.DIST.2T |
Returns the student's two-tailed t-distribution. |
T.DIST.2T(x,deg_freedom) |
|
|
T.DIST.RT |
Returns the student's right-tailed t-distribution. |
T.DIST.RT(x,deg_freedom) |
|
|
T.INV |
Returns the inverse of the student's left-tailed t-distribution. |
T.INV(probability,deg_freedom) |
|
|
T.INV.2T |
Returns the inverse of the student's two-tailed t-distribution. |
T.INV.2T(probability,deg_freedom) |
|
|
TAN |
Returns the tangent of a number. |
TAN(number) |
|
|
TANH |
Returns the hyperbolic tangent of a number. |
TANH(number) |
|
|
TBILLEQ |
Returns the bond-equivalent for a Treasury bill. |
TBILLEQ(settlement, maturity, discount) |
|
|
TBILLPRICE |
Returns the price per $100 of face value for a Treasury bill. |
TBILLPRICE(settlement, maturity, discount) |
|
|
TBILLYIELD |
Returns the yield for a Treasury bill. |
TBILLYIELD(settlement, maturity, pr) |
|
|
TDIST |
Returns the student's left-tailed t-distribution. |
TDIST(x,deg_freedom, cumulative) |
|
|
TDIST2T |
Returns the student's two-tailed t-distribution. |
TDIST2T(x,deg_freedom) |
|
|
TDISTRT |
Returns the student's right-tailed t-distribution. |
TDISTRT(x,deg_freedom) |
|
|
TEXT |
Converts a number to text. |
TEXT(value, format_text) |
|
|
TEXTJOIN |
Combines multiple variables or strings into one string. |
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) |
|
|
TIME |
Converts a specified time into a serial number. |
TIME(hour, minute, second) |
|
|
TIMEVALUE |
Converts a specified time in text format into a serial number. |
TIMEVALUE(time_text) |
|
|
TINV |
Returns the inverse of the student's left-tailed t-distribution. |
TINV(probability,deg_freedom) |
|
|
TINV2T |
Returns the inverse of the student's two-tailed t-distribution. |
TINV2T(probability,deg_freedom) |
|
|
TODAY |
Returns the serial number of today's date. |
TODAY() |
|
|
TRANSPOSE |
Converts a range from vertical to horizontal, or horizontal to vertical. |
TRANSPOSE(Array) |
|
|
TREND |
Returns values along a linear trend. |
TREND(known_y's, known_x's, new_x's) |
|
|
TRIM |
Removes spaces from text. |
TRIM(text) |
|
|
TRIMMEAN |
Returns the mean by excluding data points from the top and bottom of the dataset. Only interior data points are used. |
TRIMMEAN(array, percent) |
|
|
TRUE |
Returns the value of true. |
TRUE() |
|
|
TRUNC |
Truncates a number into an integer. |
TRUNC(number, [num_digits]) |
U-V
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
UNICHAR |
Returns the Unicode character that is referenced by the specified numeric value. |
UNICHAR(number) |
|
|
UNICODE |
Returns the number (code point) that corresponds to the first character of the text. |
UNICODE(text) |
|
|
UNIQUE |
Returns a list of distinct values in a list or range. |
UNIQUE(value1, [value2], ...) |
|
|
UPPER |
Returns text converted to uppercase. |
UPPER(text) |
|
|
VALUE |
Converts text to a value. |
VALUE(text) |
|
|
VAR.P |
Calculates variance based on the entire population. |
VAR.P(number1,[number2],...) |
|
|
VAR.S |
Estimates variance based on a sample. |
VAR.S(number1,[number2],...) |
|
|
VARA |
Estimates variance based on a sample, including numbers, text, and logical value. |
VARA(number1,[number2],...) |
|
|
VARP |
Calculates variance based on the entire population. |
VARP(number1,[number2],...) |
|
|
VARPA |
Calculates variance of a population based on numbers, text, and logical values. |
VARPA(number1,[number2],...) |
W-Y
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
| Formula | Description | Syntax | Reference |
|---|---|---|---|
|
WEEKDAY |
Converts a serial number to a day of the week. |
WEEKDAY(serial_number,[return_type]) |
|
|
WEEKNUM |
Converts a serial number to a number that indicates that week of the year. |
WEEKNUM(serial_number,[return_type) |
|
|
WEIBULL.DIST |
Calculates variance based on the entire population, including numbers, text, and logical values |
WEIBULL.DIST(x,alpha,beta,[cumulative]) |
|
|
WEIBULLDIST |
Returns the Weibull distribution. |
WEIBULLDIST(x,alpha,beta,[cumulative]) |
|
|
WORKDAY |
Returns a serial number for a date that is a specified number of working days in the future or past. |
WORKDAY(start_date, days, [holidays]]) |
|
|
XNPV |
Calculates the net present value for a schedule of cash flows that are not periodic. |
XNPV(rate, values, dates) |
|
|
XOR |
Performs an Exclusive Or operation. Using two logical statements, it returns true if either statement is true. It returns false if both statements are true. |
XOR(logical1, [logical2],…) |
|
|
YEAR |
Returns the year as a number according to the internal calculation rules. |
YEAR(serial_number) |
|
|
YEARFRAC |
Calculates the difference between two date values, in fraction of years. |
YEARFRAC(start_date, end_date, [basis]) |
