Database and List Management Functions 

DAVERAGE 
Indicates the average of the values that meet the specified criteria. 
DCOUNT 
Counts the number of cells containing numbers that meet the specified criteria. 
DCOUNTA 
Counts nonblank cells containing numbers or text that meet the specified criteria. 
DGET 
Returns a single value that meets the specified criteria. 
DMAX 
Extracts the highest value that meets the specified criteria. 
DMIN 
Extracts the lowest value that meets the specified criteria. 
DPRODUCT 
Returns the product of the values that meet the specified criteria. 
DSTDEV 
Estimates the standard deviation of a population, based on a sample of selected entries from the database. 
DSTDEVP 
Returns the calculation of the standard deviation of a population, based on the sum of the whole population. 
DSUM 
Returns the total of the values that meet the specified criteria. 
DVAR 
Estimates the variance of a sample population based on the values that meet the specified criteria. 
DVARP 
Returns the calculation of the true variance of an entire population based on the values that meet the specified criteria. 
Date and Time Functions 

NOW 
Returns the current date and time in the form of a serial number. 
TODAY 
Returns the current date as a serial number. 
DATEVALUE 
Converts date text to a DATEVALUE serial number. 
DAY 
Returns the corresponding day of the month serial number or date text from 1 to 31. 
HOUR 
Returns the hour as a serial number integer between 0 and 23. 
MINUTE 
Returns the serial number that corresponds to the minute. 
MONTH 
Returns the corresponding serial number of the month of a date between 1 and 12. 
SECOND 
Returns the seconds portion of a serial time value. 
TIMEVALUE 
Returns the decimal number for a given time. 
YEAR 
Returns the corresponding year as a serial number in the form of an integer. 
WEEKDAY 
Returns the corresponding day of the week as a serial number. 
DAYS360 
Returns the number of days between two set dates based on a 360day year. 
DATE 
Returns the serial number that represents a date. 
TIME 
Returns the decimal value of a given time. 
Financial Functions 

IRR 
Returns the internal rate of return for a series of cash flows represented by numbers in the form of values. 
NPV 
Calculates the net present value of an investment from the discount rate and several future payments and income. 
MIRR 
Returns a modified internal rate of return for several periodic cash flows. 
SLN 
Returns the straightline depreciation on an asset. 
FV 
Returns the future value of an investment that makes payments as a lump sum or as a series of equal periodic payments. 
NPER 
Returns the total number of periods for an investment. This is based on a periodic constant payment and a constant interest rate. 
PMT 
Calculates the loan payment for a loan based on constant payments and constant interest rates. 
PV 
Returns the present value based on an investment. 
RATE 
Returns per period the interest of an annuity. 
ISPMT 
Calculates the interest paid during a defined period of an investment. 
SYD 
Based on a specified period, SYD returns the sumofyears' digits depreciation of an asset. 
DB 
Returns the asset depreciation for a period using the fixed declining balance method. 
DDB 
Returns the asset depreciation for a period using the doubledeclining balance method or another specified method. 
IPMT 
Returns the interest for a period of time based on an investment with periodic constant payments and a constant interest rate. 
PPMT 
Returns the principal payment for a period of an investment based on periodic constant payments and a constant interest rate. 
VDB 
For a period you specify, returns the depreciation of an asset. 
Information Functions 

NA 
An alternative representation of the error value #N/A. 
ERROR TYPE 
Returns the corresponding number value associated with an error type in Microsoft Excel. 
ISBLANK 
Returns TRUE if the cell is empty, FALSE if it contains data. 
ISERR 
Returns TRUE if value contains any error value except #N/A, FALSE if it does not. 
ISERROR 
Returns TRUE if value contains any error value (including #N/A), FALSE if it does not. 
ISLOGICAL 
Returns TRUE if value is a logical value, FALSE if it is not. 
ISNA 
Returns TRUE if value is #N/A, FALSE if it is not. 
ISNONTEXT 
Returns TRUE if value is not text, FALSE if it is. 
ISNUMBER 
Returns TRUE if value is a number, FALSE if it is not. 
ISREF 
Returns TRUE if value is a reference, FALSE if it is not. 
ISTEXT 
Returns TRUE if value is text, FALSE if it is not. 
N 
Returns a value converted to a number. 
TYPE 
Determines the type of value in a cell. 
Logical Functions 

FALSE 
Returns the value FALSE. May be typed directly into the cell as "FALSE". 
TRUE 
Returns the value TRUE. May be typed directly into the cell as "TRUE". 
NOT 
Returns the reverse value of its arguments; TRUE becomes FALSE and FALSE becomes TRUE. 
AND 
Returns TRUE if all the arguments are TRUE in the formula, and FALSE if any one argument is FALSE. 
OR 
Returns FALSE if all arguments are FALSE, and TRUE if at least one argument is TRUE. 
IF 
Returns a value if one condition is TRUE and returns another value if the condition is FALSE. 
Lookup and Reference Functions 

COLUMN 
Returns the column number(s) based on a given reference. 
ROW 
Returns the row number based on a reference. 
AREAS 
Returns the number of areas based on a reference. 
COLUMNS 
Returns the number of columns based on an array or reference. 
ROWS 
Returns the number of rows in a reference or array. 
TRANSPOSE 
Returns a horizontal range of cells as vertical or vice versa. 
HYPERLINK 
Creates a shortcut to jump to a document stored on a network server. 
INDIRECT 
Returns the contents of a cell using its reference. 
INDEX 
Returns the value of an element selected by the row number and column letter indexes. 
LOOKUP 
Looks in the first row or column of a range or array, and returns the specified value from the same position in the last row or column of the range or array. 
MATCH 
Returns the relative position of an item in an array that matches a specified value in a specified order, or the position of an item. 
ADDRESS 
Given specified row and column numbers, creates a cell address as text. 
CHOOSE 
Returns an item from a list of values.. 
HLOOKUP 
Searches for a specified value in an array or a table's top row. 
VLOOKUP 
Searches for a value in the leftmost column of a table and returns a value from the same row in a column number that you specify. 
OFFSET 
Returns a reference to a range that is a specific number of rows and columns from a cell or range of cells. 
Math and Trigonometry Functions 

PI 
Returns the approximate number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. 
RAND 
Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated. 
ABS 
Returns the absolute value of a number. 
ACOS 
Returns the arccosine of a number in radians in the range 0 to pi. 
ACOSH 
Returns the inverse hyperbolic cosine of a number. 
ASIN 
Returns the arcsine of a number in radians in the range pi/2 to pi/2. 
ASINH 
Returns the inverse hyperbolic sine of a number. 
ATAN 
Returns the arctangent of a number in radians in the range pi/2 to pi/2 
ATANH 
Returns the inverse hyperbolic tangent of a number. 
COS 
Returns the cosine of the given angle. 
COSH 
Returns the hyperbolic cosine of a number. 
DEGREES 
Converts radians into degrees. 
EVEN 
Returns a number rounded up to the next even integer for positive integers and rounded down to the next even integer for negative numbers. 
EXP 
Returns e (2.71828182845804) raised to the power of a specified number. 
FACT 
Returns the factorial of a number. 
INT 
Rounds a number down to the nearest integer. 
LN 
Returns the natural (base e) logarithm of a number. 
LOG10 
Returns the base10 logarithm of a number. 
MDETERM 
Returns the matrix determinant of an array. 
MINVERSE 
Returns the inverse matrix for the matrix stored in an array. 
ODD 
Returns a number rounded up away from zero to the nearest odd integer. 
RADIANS 
Converts degrees to radians. 
SIGN 
Determines the sign of a number. Returns 1 if the value is positive, 0 if the value is 0, and 1 if the value is negative. 
SIN 
Returns the sine of a given angle. 
SINH 
Returns the hyperbolic sine of a number. 
SQRT 
Returns a positive square root. 
TAN 
Returns the tangent of the given angle. 
TANH 
Returns the hyperbolic tangent of a number. 
LOG 
Returns the logarithm of a number of the base you specify. 
ROMAN 
Converts an Arabic numeral to Roman, as text. 
TRUNC 
Truncates a number to an integer by removing the fractional part of a number. 
PRODUCT 
Multiplies all the numbers given as arguments and returns the product. 
SUM 
Adds all the numbers in a range of cells. 
SUMSQ 
Returns the sum of the squares of the arguments. 
ATAN2 
Returns the fourquadrant arctangent of the specified x and y coordinates in radians between pi and pi excluding pi. A positive result represents a counterclockwise angle from the xaxis, a negative result represents a clockwise angle. 
CEILING 
Returns a number rounded up, away from zero, to the nearest multiple of significance. 
COMBIN 
Returns the number of combinations for a given number of items. 
FLOOR 
Returns a number rounded down, toward zero, to the nearest multiple of significance. 
MMULT 
Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. 
MOD 
Returns the remainder of a division operation (modulus). 
POWER 
Returns the result of a specified number raised to a specified power. 
ROUND 
Round a number to a specified number of digits. 
ROUNDDOWN 
Rounds a number down, towards zero. 
ROUNDUP 
Rounds a number up, away from zero. 
SUMX2MY2 
Returns the sum of the difference of squares of corresponding values in two arrays. 
SUMX2PY2 
Returns the sum of the sum of squares of corresponding values in two arrays. 
SUMXMY2 
Returns the sum of squares of differences of corresponding values in two arrays. 
SUMIF 
Adds the cells specified by a certain criteria. 
SUMPRODUCT 
Multiplies corresponding components in the given arrays, and returns the sum of those products. 
Statistical Functions 

COUNTBLANK 
Counts the empty cells in a specified range. 
FISHER 
Returns the Fisher transformation at x. 
FISHERINV 
Returns the inverse of the Fisher transformation at y. 
GAMMALN 
Returns the natural logarithm of the gamma function. 
NORMSDIST 
Returns the standard normal cumulative distribution function. 
NORMSINV 
Returns the inverse of the standard normal cumulative distribution function. 
GROWTH 
Calculates predicted exponential growth by using existing data. 
LINEST 
Calculates a straight line that best fits your data using the least squares method. 
LOGEST 
Calculates an exponential curve that fits your data and returns an array of values that describes the curve. 
TREND 
Returns the yvalues along a linear trendline that best fits the values in a data set. 
AVEDEV 
Retuns the average of the absolute deviations of data points from their mean. 
AVERAGE 
Returns the average of its arguments. 
AVERAGEA 
Returns the average of the values in its list of arguments including text and logical values. 
COUNT 
Counts the number of cells that contain numbers (including dates and formulas that evaluate to numbers) within the list of arguments. 
COUNTA 
Counts the number of cells that are not empty. 
DEVSQ 
Returns the sum of the squares of deviations of a data set from their sample mean. 
GEOMEAN 
Returns the geometric mean of an array or range of positive data. 
HARMEAN 
Returns the harmonic mean of a data set. 
KURT 
Returns the Kurtosis of a data set. 
MAX 
Returns the largest value in a set of values. 
MAXA 
Returns the largest value in a set of values including text and logical values. 
MEDIAN 
Returns the median of the given numbers. 
MIN 
Returns the smallest value in a set of values. 
MINA 
Returns the smallest value in a set of values including text and logical values. 
MODE 
Returns the most frequently occuring, or repetitive, number in an array or range of data. 
SKEW 
Returns the skew of a distribution. 
STDEV 
Estimates standard deviation based on a sample. 
STDEVA 
Estimates standard deviation based on a sample. Includes text and logical values. 
STDEVP 
Estimates standard deviation based on a sample assuming that the arguments represent the total population. 
STDEVPA 
Estimates standard deviation based on a sample assuming that the arguments represent the total population. Includes text and logical values. 
VAR 
Returns an estimate for the variance of a population based on a sample data set. 
VARA 
Returns an estimate for the variance of a population based on a sample data set and may include text or logical values. 
VARP 
Calculates variance based on the entire population. 
VARPA 
Calculates variance based on the entire population and may include text or logical values. 
CHIDIST 
Returns the onetailed probability of the chisquared (X^2) distribution; the area in the right tail under the chisquared distribution curve. 
CHIINV 
Returns the inverse of the onetailed probability of the chisquared (X^2) distribution. 
CHITEST 
Returns the test for independence of the characteristics in a table. 
CORREL 
Returns the correlation coefficient between two data sets. 
COUNTIF 
Counts the number of cells in a range that meet a given criteria. 
COVAR 
Returns the covariance, the average of products of deviations, for each data point pair. 
FREQUENCY 
Calculates how often values occur within a range of values and then returns a vertical array of numbers. 
FTEST 
Returns the result of an Ftest. 
INTERCEPT 
Calculates the point at which a line will intersect the yaxis by using existing x and y values. 
LARGE 
Returns the kth largest value in a data set. 
PEARSON 
Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from 1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets. 
PERCENTILE 
Returns the kth percentile of values in a range. 
PERMUT 
Returns the number of permutations for a given number of objects that can be selected from a range of numbers. 
QUARTILE 
Returns the quartile of a data set. 
RSQ 
Returns the r^2 value of a linear regression line. 
SLOPE 
Returns the slope of a regression line through data points in KNOWN_Y'S and KNOWN_X'S. 
SMALL 
Returns the kth smallest value in a data set. 
STEYX 
Returns the standard error of the predicted y value for each x in the regression. 
TINV 
Returns the tvalue of the Student's tdistribution as a function of the probability and the degrees of freedom. 
TRIMMEAN 
Returns the mean of the interior of a data set. 
PERCENTRANK 
Returns the rank of a value in a data set set as a percentage of the data set. 
RANK 
Returns the rank of a number in a list of numbers. 
ZTEST 
Returns the twotailed Pvalue of a ztest. 
CONFIDENCE 
Returns the confidence interval for a population mean. 
CRITBINOM 
Returns the minimum number yields a binomial distribution less than or equal to the specified criteria 
EXPONDIST 
Returns the exponential distribution. 
FDIST 
Returns the F probability distribution. 
FINV 
Returns the inverse of the F probability distribution. 
FORECAST 
Calculates or predicts a future value by using existing values. 
GAMMAINV 
Returns the inverse of the gamma cumulative distribution. 
LOGINV 
Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard deviation. 
LOGNORMDIST 
Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard deviation. 
NEGBINOMDIST 
Returns the negative binomial distribution. 
NORMINV 
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. 
POISSON 
Returns the Poisson distribution. 
STANDARDIZE 
Returns a normalized value from a distribution characterized by MEAN and STANDARD_DEV. 
TDIST 
Returns the percentage points (probability) for the student tdistribution, where a numeric value (x) is a calculated value of t for which the percentage points are to be computed. 
PROB 
Returns the probability that values in a range are between two specified limits. 
BETADIST 
Returns the cumulative beta probability density function. 
BETAINV 
Returns the inverse of the cumulative beta probability density function. 
BINOMDIST 
Returns the individual term binomial distribution probability. 
GAMMADIST 
Returns the gamma distribution. 
HYPGEOMDIST 
Returns the hypergeometric distribution. 
NORMDIST 
Returns the normal cumulative distribution for the specified mean and standard deviation. 
TTEST 
The probability associated with ttest. 
WEIBULL 
Returns the Weibull distribution. 
Text Functions 

CHAR 
Returns the character specified by a number. 
CLEAN 
Removes all nonprintable characters from text. 
CODE 
Returns a numeric code from the first character in a text string. The opposite of the CHAR function. 
LEN 
Returns the number of characters in a text string. 
LENB 
Returns the number of characters in a text string expressed in bytes. 
LOWER 
Converts all letters in a text string to lowercase. 
PROPER 
Capitalizes the first letter of each word in a text string or sentence. 
T 
Returns the text referred to by a value. 
TRIM 
Removes all spaces from text except single spaces between words. 
UPPER 
Converts text to uppercase. 
VALUE 
Converts a text string that represents a number to a number. 
DOLLAR 
Converts a number to text using Currency format, with the decimals rounded to the specified place. 
USDOLLAR 
Converts a number to text using US Dollar format, with the decimals rounded to the specified place. 
LEFT 
Returns the first character(s) in a text string. 
LEFTB 
Returns the first character(s) in a text string based on a specified number of bytes 
RIGHT 
Returns the last character(s) in a text string. 
RIGHTB 
Returns the last character(s) in a text string based on a specified number of bytes. 
FIXED 
Rounds a number to a specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. 
CONCATENATE 
Joins several text strings into one text string. 
EXACT 
Compares two text strings and returns TRUE if they are exactly the same, and FALSE otherwise. 
REPT 
Repeats specified text a given number of times. 
TEXT 
Converts a value to text in a specific number format. 
FIND 
Locates one text string within another text string, and returns the number of the starting position of of FIND_TEXT from the leftmost character of WITHIN_TEXT. 
FINDB 
Returns the position of specified text within another specified text string based on the number of bytes each character uses from the first character of WITHIN_TEXT. 
SEARCH 
Returns the number of the character at which a specific character or text string is first found, reading from left to right. 
SEARCHB 
Returns the number of the character at which a specific character or text string is first found in bytes, reading from left to right. 
MID 
Returns a specific number of characters from a text string starting at the position you specify. 
MIDB 
Returns a group of characters based on a specified number of bytes from a text string starting at the position you specify. 
SUBSTITUTE 
Substitutes NEW_TEXT for OLD_TEXT in a string. 
REPLACE 
Replaces part of a text string with a different text string based on the number of characters you specify. 
REPLACEB 
Replaces part of a text string with a different text string based on the number of characters you specify in terms of bytes. 
Thank you!
Support
Comments
0 comments
Article is closed for comments.