Database functions
DAVERAGE function | averages the values of a column in a list or database that matches the conditions you specify |
DCOUNT Function | counts the cells containing numbers in the column of records in the database that match the conditions you specify |
DCOUNTA Function | counts nonblank cells in the column of records that match the conditions you specify. |
DGET Function | extracts a single record from the database that matches the specified conditions |
DMAX Function | returns the largest number in the column of records in the database that matches the conditions you specify |
DMIN function | returns the smallest number in the column of records in the database that matches the conditions you specify. |
DPRODUCT function | multiplies the values in the column of records in the database that matches the conditions you specify |
DSTDEVP function | calculates the standard deviation based on the entire population of selected database entries |
DSUM function | adds the numbers in the column of records in the database that match the conditions you specify |
DVAR function | estimates variance based on a sample from selected database entries |
DVARP function | calculates variance based on the entire population of selected database entries. |
Lookup AND Reference functions
ADDRESS function | Creates a cell reference as text, given specified row and column numbers. |
AREAS function | Returns the number of areas in a reference. |
CHOOSE function | Chooses a value or action to perform from a list of values, based on an index number. |
COLUMN function | Returns the column number of a reference. |
COLUMNS function | Returns the number of columns in an array or reference. |
FILTER | It extracts all record from the database that matches the specified conditions. |
FORMULATEXT | |
GETPIVOTDATA | |
HLOOKUP | |
HYPERLINK | |
INDEX function | Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. |
INDIRECT | |
LOOKUP | |
MATCH Function | Returns the relative position of an item in an array that matches a specified value in a specified order. |
OFFSET | |
ROW function | Returns the ROW number of a reference. |
ROWS function | Returns the number of rows in a reference or array. |
RTD | |
SORT | |
SORTBY | |
TRANSPOSE | |
UNIQUE | |
VLOOKUP | |
XLOOKUP | It searches a range or an array for a match and returns the corresponding item from a second range or array. |
XMATCH |
Logical functions
AND function | determines if all conditions in a test are TRUE |
FALSE function | returns the logical value false |
IF function | Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. |
IFERROR function | returns value_if_error if the expression is an error and the value of the expression itself otherwise. |
IFNA | It returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. |
IFS function | Checks whether one or more conditions are met and return a value corresponding to the first TRUE condition. |
NOT Function | changes false to true or true to false. |
OR function | Checks whether any of the arguments are TRUE and returns TRUE or FALSE. |
SWITCH function | evaluates an expression against a list of values and returns the result corresponding to the first matching value. |
TRUE function | returns the logical value true. |
XOR function | returns a logical Exclusive of all arguments. |
Text Functions
ASC | |
ArrayToText function | Returns a text representation of an array. |
BAHTTEXT function | Converts a number to text (baht). |
CHAR Function | Returns the character specified by the code number from the character set for your computer. |
CLEAN | |
CODE function | Returns a numeric code for the first character in a text string, in the character set used by your computer. |
CONCAT function | Concatenates a list or range of text strings. |
CONCATENATE function | Concatenates a list or range of text strings. |
DBCS | |
DOLLAR function | Converts a number to text, using currency format. |
FIND | |
FIXED function | Rounds a number to the specified number of decimals and returns the result as text with or without commas. |
LEFT | |
LEN | |
LOWER function | Converts all letters in a text string to lowercase. |
MID | |
NUMBERVALUE | |
PHONETIC | |
PROPER function | Converts a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase. |
REPLACE | |
REPT | |
RIGHT | |
SEARCH | |
SUBSTITUTE | |
T | |
TEXT | |
TEXTJOIN | |
TRIM | |
UNICHAR | |
UNICODE | |
UPPER function | Converts a text string to all uppercase letters. |
VALUE | |
VALUETOTEXT |
DATE AND TIME Functions
DATE function | Returns the number that represents the date in Microsoft Excel date-time code. |
DATEDIF | |
DATEVALUE function | Converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code. |
DAY function | Returns the day of the month, a number from 1 to 31. |
DAYS function | Returns the number of days between the two dates. |
DAYS360 function | Returns the number of days between two dates based on a 360-day year. |
EDATE function | Returns the serial number of the date which is the indicated number of months before or after the start date. |
EOMONTH function | Returns the serial number of the last day of the month before or after a specified number of months. |
HOUR function | Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.J.). |
ISOWEEKNUM function | returns the ISO week number in the year for a given date. |
MINUTE function | Returns the minute, a number from 0 to 59. |
MONTH function | Returns the month, a number from 1 (January) to 12 (December). |
NETWORKDAYS | |
NOW function | Changes false to true or changes true to false. |
SECOND function | Returns the second, a number from 0 to 59. |
TIME function | converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format. |
TIMEVALUE function | Converts a text time to an Excel serial number for a time, a number from 0 (12:00:00 AM) to 0.999988426 (11:59:59 PM). |
TODAY function | Returns the current date formatted as a date. |
WEEKDAY function | Returns a number from 1 to 7 identifying the day of the week of a date. |
WEEKNUM function | Returns the week number in the year. |
WORKDAY | |
YEAR function | Returns the year of a date, an integer in the range 1900 – 9999. |
YEARFRAC function | Returns the year fraction representing the number of whole days between start_date and end date. |
Information Functions
CELL function | Returns information about the formatting, location, or contents of the first cell, according to the sheet reading order, in a reference. |
ERROR.TYPE function | Returns a number matching an error value. |
INFO function | Returns information about the current operating environment. |
ISBLANK function | Checks whether a reference is to an empty cell, and returns TRUE or FALSE. |
ISERR function | Checks whether a value is an error other than #N/A, and returns TRUE or FALSE. |
ISERROR function | Checks whether a value is an error, and returns TRUE or FALSE. |
ISEVEN function | Returns TRUE if the number is even. |
ISFORMULA function | Checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE. |
ISLOGICAL function | Checks whether a value is a logical value, and returns TRUE or FALSE. |
ISNA function | Checks whether a value is=N/A and return TRUE or FALSE. |
ISNUMBER function | Checks whether a value is a number, and returns TRUE or FALSE. |
ISNONTEXT function | Checks whether a value is not text (blank cells), and returns TRUE or FALSE. |
ISODD function | Returns TRUE if the number is odd. |
ISREF function | Checks whether a value is a reference, and returns TRUE or FALSE. |
ISTEXT function | Checks whether a value is text, and returns TRUE or FALSE. |
SHEET function | Returns the sheet number of the referenced sheet. |
SHEETS function | Returns the number of sheets in a reference. |
TYPE function | Returns an integer representing the data type of a value. |
N function | It Converts non-number values to a number, dates to serial numbers, TRUE to 1, and anything else to 0 (zero). |
NA function | Returns the error value =NA (value not available). |
Math functions
ABS function | removes the sign of a number and returns just a positive number |
ACOS function | returns the arccosine of a number, in radians in the range 0 to Pi |
ACOSH function | returns the inverse hyperbolic cosine of a number |
ACOT function | returns the arccotangent of a number, in radians in the range 0 to Pi |
AGGREGATE function | returns an aggregate calculation like AVERAGE, COUNT, MAX, product, median, and … |
ASIN function | returns the arcsine of a number in radians, in the range -Pi/2 to Pi/2. |
ASINH function | returns the inverse hyperbolic sine of a number. |
ATAN function | Returns the arctangent of a number in radians, in the range Of -pi/2 to Pi/2. |
ATAN2 Function | Returns the arctangent of the specified x- and y- coordinates, in radians between -Pi and Pi, excluding -Pi. |
ARABIC function | Converts a Roman numeral to Arabic. |
ROMAN Function | Converts an Arabic numeral to Roman, as text. |
PI function | Returns the value of pi =3.14159265358979 accurate to 15 digits. |
COS function | Returns the cosine of an angle. |
COT function | Returns the cotangent of an angle. |
CSC function | Returns the cosecant of an angle. |
EVEN function | Rounds a positive number up and a negative number down to the nearest even integer. |
INT function | Rounds a number down to the Nearest integer. |
MOD Function | Returns the remainder after a number is divided by a divisor. |
MROUND function | Returns a number rounded to the desired multiple. |
ODD function | Rounds a positive number up and negative number down to the Nearest ODD integer. |
SEC function | Returns the secant of an angle. |
EXP function | Returns e raised to the power of a given number. |
LN function | Returns the natural logarithm of a number. |
BASE function | Converts a number into a text representation with a given radix(base). |
COSH Function | Returns the hyperbolic cosine of a number. |
COTH function | Returns the hyperbolic cotangent of a number. |
CSCH function | Returns the hyperbolic cosecant of an angle. |
Decimal function | Converts a text representation of a number in a given base into a decimal number. |
LOG function | Returns the logarithm of a number to the base you specify. |
LOG10 function | Returns the base-10 logarithm of a number. |
POWER function | Returns the result of a number raised to a power. |
RADIANS function | Converts degrees to radians. |
ROUND function | Rounds a number to a specified number of digits. |
SIGN function | Returns the sign of a number, 1 if the number is positive, zero if the number is zero, and -1 if the number is negative. |
ROUNDDOWN function | Rounds a number down, toward zero. |
ROUNDUP function | Rounds a number up, away from zero. |
ACOTH Function | Returns the inverse hyperbolic cotangent of a number. |
CEILING Function | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
FLOOR Function | Rounds a number down, to the nearest integer or to the nearest multiple of significance. |
ATANH Function | Returns the inverse hyperbolic tangent of a number. |
COMBIN Function | Returns the number of combinations for a given number of items. |
COMBINA Function | Returns the number of combinations with repetitions for a given number of items. |
Degrees Function | Converts radiants to degrees. |
GCD Function | Returns the greatest common divisor. |
FACT Function | Returns the factorial of a number, equal to 1*2*3*…* Number. |
FACTDOUBLE Function | Returns the double factorial of a number. |
CEILING.MATH Function | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
FLOOR.MATH Function | Rounds a number down, to the nearest integer or to the nearest multiple of significance. |
LCM Function | Returns the least common multiple. |
RAND Function | Returns a random number greater than or equal to 0 and less than 1, evenly distributed. |
RANDARRAY function | Returns an array of random numbers. |
LET function | Assigns calculation results to names. |
PRODUCT Function | Multiplies all the numbers given as arguments. |
QUOTIENT Function | Returns the integer portion of a division. |
MDETERM Function | Returns the matrix determinant of an array. |
MINVERSE Function | Returns the inverse matrix for the matrix stored in an array. |
MMULT Function | Returns the matrix product of two arrays, an array with the same number of rows as array1 and columns as array2. |
MULTINOMIAL Function | Calculates the ratio of the factorial of a sum of desired numbers to the product of factorials of those numbers. |
MUNIT Function | Returns the unit matrix for the specified dimension. |
SECH Function | Returns the hyperbolic secant of an angle. |
RANDBETWEEN Function | Returns a random number between the numbers you specify. |
SQRT Function | Returns the square root of a number. |
SQRTI Function | Returns the square root of (number* Pi). |
TAN Function | Returns the tangent of an angle. |
TANH Function | Returns the hyperbolic tangent of a number. |
SUM Function | Adds all the numbers in a range of cells. |
TRUNC Function | Truncates a number to an integer by removing the decimal, or fractional, part of the number. |
SUMPRODUCT Function | Returns the sum of the products of corresponding ranges or arrays. |
SUMSQ Function | Returns the sum of the squares of the arguments. |
SUMXMY2 Function | Sums the squares of the differences in two corresponding ranges or arrays. |
SUMX2MY2 Function | Sums the differences between the squares of two corresponding ranges or arrays. |
SUMX2PY2 Function | Returns the sum total of the sums of squares of numbers in two corresponding ranges or arrays. |
SIN Function | Returns the sine of an angle. |
SINH Function | Returns the hyperbolic sine of a number. |
SEQUENCE function | Returns A SEQUENCE of numbers. |
Engineering functions
BESSELI function | Returns the modified Bessel function In(x). |
BESSELJ function | Returns the Bessel function Jn(x). |
BESSELK function | Returns the modified BESSELK function Kn(x). |
BESSELY function | Returns the Bessel function Yn(x). |
BIN2DEC function | Converts a binary number to a decimal. |
BIN2HEX function | Converts a binary number to hexadecimal. |
BIN2OCT function | Converts a binary number to an octal. |
BITAND function | Returns a bitwise ‘And‘ of two numbers. |
BITLSHIFT function | Returns a number shifted left by shift amount bits. |
BITOR function | Returns a bitwise ‘or‘ of two numbers. |
BITRSHIFT function | Returns a number shifted right by shift amount bits. |
BITXOR function | Returns a bitwise ‘Exclusive or’ of two numbers. |
COMPLEX function | Converts real and imaginary coefficients into complex numbers. |
CONVERT function | Converts a number from one measurement system to another. |
DEC2BIN function | Converts a decimal number to binary. |
DEC2HEX function | Converts a decimal number to hexadecimal. |
DEC2OCT function | Converts a decimal number to octal. |
DELTA function | Tests whether two numbers are equal. |
ERF function | Calculates and Returns the error function. |
ERF.PRECISE function | Returns the error function. |
ERFC function | Returns the complementary error function. |
ERFC.PRECISE function | Returns the complementary error function. |
GESTEP function | Tests whether a number is greater than a threshold value. |
IMABS function | Returns the absolute value (modulus) of a complex number. |
IMAGINARY function | Returns the imaginary coefficient of a complex number. |
IMARGUMENT function | Returns the argument of an angle expressed in radians. |
IMCONJUGATE function | Returns the complex conjugate of a complex number. |
IMCOS function | Returns the cosine of a complex number. |
IMCOSH function | Returns the hyperbolic cosine of a complex number. |
IMCSC function | Returns the cosecant of a complex number. |
IMCSCH function | Returns the hyperbolic cosecant of a complex number. |
IMCOT function | Returns the cotangent of a complex number. |
IMDIV function | Returns the quotient of two complex numbers. |
IMEXP function | Returns the exponential of a complex number. |
IMLN function | Returns the natural logarithm of a complex number. |
IMPOWER function | Returns a complex number raised to an integer power. |
IMPRODUCT function | Returns the product of 1 to 255 complex numbers. |
IMREAL function | Returns the real coefficient of a complex number. |
IMLOG2 function | Returns the base-2 logarithm of a complex number. |
IMLOG10 function | Returns the base-10 logarithm of a complex number. |
IMSEC function | Returns the secant of a complex number. |
IMSECH function | Returns the hyperbolic secant of a complex number. |
IMSIN function | Returns the sine of a complex number. |
IMSINH function | Returns the hyperbolic sine of a complex number. |
IMSQRT function | Returns the square root of a complex number. |
IMSUB function | Returns the difference between two complex numbers. |
IMSUM function | Returns the sum of complex numbers. |
IMTAN function | Returns the tangent of a complex number. |
HEX2BIN function | Converts a Hexadecimal number to binary. |
HEX2DEC function | Converts a hexadecimal number to decimal. |
HEX2OCT function | Converts a hexadecimal number to octal. |
OCT2BIN function | Converts an octal number to binary. |
OCT2DEC function | Converts an octal number to decimal. |
OCT2HEX function | Converts an octal number to hexadecimal. |
STATISTIC functions
AVEDEV function | Returns the average of the absolute deviations of data points from their mean. |
AVERAGE function | Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers. |
AVERAGEA function | returns the average (arithmetic mean) of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. |
AVERAGEIF function | finds the average(arithmetic mean) for the cells specified by a given condition or criteria. |
AVERAGEIFS function | Finds average(arithmetic mean) for the cells specified by a given set of conditions or criteria. |
BETA.DIST function | Returns the beta probability distribution function. |
BETA.INV function | Returns the inverse of the cumulative beta probability density function. |
BINOM.DIST function | Returns the individual term binomial distribution probability. |
BINOM.DIST.RANGE function | Returns the probability of a trial result using a binomial distribution. |
BINOM.INV function | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |
CHISQ.DIST function | Returns the left-tailed probability of the chi-squared distribution. |
CHISQ.DIST.RT function | Returns the right-tailed probability of the chi-squared distribution. |
CHISQ.INV function | Returns the inverse of the left-tailed probability of the chi-squared distribution. |
CHISQ.INV.RT function | Returns the inverse of the right-tailed probability of the chi-squared distribution. |
CHISQ.TEST function | Returns the test for independence. |
CONFIDENCE.NORM function | Returns the confidence interval for a population mean, using a normal distribution. |
CONFIDENCE.T function | Returns the confidence interval for a population mean, using a Student T distribution. |
CORREL function | Returns the correlation coefficient between two data sets. |
COUNT function | Counts the number of cells in a range that contains numbers. |
COUNTA function | Counts the number of cells in a range that are not empty. |
COUNTBLANK function | Counts the number of empty cells in a specified range of cells. |
COUNTIF function | Counts the number of cells within a range that meet the given condition. |
COUNTIFS function | Counts the number of cells specified by a given set of conditions or criteria. |
COVARIANCE.P function | Returns population covariance, the average of the products of deviations for each data point pair in two data sets. |
COVARIANCE.S function | Returns sample covariance, the average of the products of deviations for each data point pair in two data sets. |
DEVSQ function | Returns the sum of squares of deviations of data points from their sample mean. |
EXPON.DIST function | Returns the exponential distribution. |
F.DIST function | Returns the (left-tailed) F probability distribution (degree of diversity) for two data sets. |
F.DIST.RT function | Returns the exponential distribution. |
COVARIANCE.P function | Returns population covariance, the average of the products of deviations foreach data point pair in two data sets. |
COVARIANCE.S function | Returns sample covariance, the average of the products of deviations for each data point pair in two data sets. |
DEVSQ function | Returns the sum of squares of deviations of data points from their sample mean. |
EXPON.DIST function | Returns the exponential distribution. |
F.DIST function | Returns the (left-tailed) F probability distribution (degree of diversity) for two data sets. |
F.DIST.RT function | Returns the exponential distribution. |
F.INV function | Returns the inverse of the (left-tailed) F probability distribution: if p = F.DIST(x,…), then F.INV(p,…) = x. |
F.INV.RT function | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. |
F.TEST function | Returns the inverse of the (left-tailed) F probability distribution: if p = F.DIST(x,…), then F.INV(p,…) = x. |
FISHER function | Returns the Fisher transformation. |
FISHERINV function | Returns the inverse of the Fisher transformation: if y = FISHER(x), then FISHERINV(y) = x. |
FREQUENCY function | Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array. |
GAUSS function | Returns 0.5 less than the standard normal cumulative distribution. |
GEOMEAN function | Returns the geometric mean of an array or range of positive numeric data. |
GROWTH function | Returns numbers in an exponential growth trend matching known data points. |
GAMMA function | Returns the Gamma function value. |
GAMMA.DIST function | Returns the gamma distribution. |
GAMMA.INV function | Returns the inverse of the gamma cumulative distribution. |
GAMMALN.PRECISE function | Returns the natural logarithm of the gamma function. |
GAMMALN function | Returns the natural logarithm of the gamma function. |
HARMEAN function | Returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals. |
HYPGEOM.DIST function | Returns the hypergeometric distribution. |
INTERCEPT function | Calculates the point at which a line will intersect the y-axis by using a best-fit regression line plotted through the known x-values and y-values. |
KURT function | Returns the Kurtosis of a data set. |
LARGE function | Returns the k-th largest value in a data set. For example, the fifth largest number. |
LINEST function | Returns statistics that describe a linear trend matching known data points, by fitting a straight line using the least squares method. |
LOGEST function | Returns statistics that describe an exponential curve matching known data points. |
LOGNORM.DIST function | Returns the lognormal distribution of x, where In(y) is normally distributed with parameters Mean and Standard_dev. |
LOGNORM.INV function | Returns the inverse of the lognormal cumulative distribution function of x where ln(x) is normally distributed with parameters Mean and Standard_dev. |
MAX function | Returns the largest value in a set of values. it also Ignores logical values and text. |
MAXA function | Returns the largest value in a set of values. it Does not ignore logical values and text. |