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 360-day 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 straight-line 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 sum-of-years' 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 double-declining 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 base-10 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 four-quadrant 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 x-axis, 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 y-values 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 one-tailed probability of the chi-squared (X^2) distribution; the area in the right tail under the chi-squared distribution curve. |
CHIINV |
Returns the inverse of the one-tailed probability of the chi-squared (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 F-test. |
INTERCEPT |
Calculates the point at which a line will intersect the y-axis by using existing x and y values. |
LARGE |
Returns the k-th 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 k-th 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 k-th 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 t-value of the Student's t-distribution 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 two-tailed P-value of a z-test. |
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 t-distribution, 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 t-test. |
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.