Mathematical Information for the Calculated Column
September 6, 2012 2 Comments
This is a continuation of an attempt to group the information needed for Calculated Columns in a meaningful way. Some items appear on more than one list since this exercise can be subjective.
Examples of common formulas: Mathematical Formulas
Other Mathematical Functions:
ABS function | Returns the absolute value of a number. |
AND function | Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE. |
AVERAGE function | Returns the average (arithmetic mean) of the arguments. |
AVERAGEA function | Calculates the average (arithmetic mean) of the values in the list of arguments. |
CEILING function | Returns number rounded up, away from zero, to the nearest multiple of significance. |
CHAR function | Returns the character specified by a number. |
CHOOSE function | Uses index_num to return a value from the list of value arguments. |
CODE function | Returns a numeric code for the first character in a text string. |
COUNT function | Counts the number of arguments that contain numbers. |
COUNTA function | Counts the number of arguments that are not empty. |
DOLLAR function | Converts a number to text using currency format, with the decimals rounded to the specified place. |
EVEN function | Returns number rounded up to the nearest even integer. |
FALSE function | Returns the logical value FALSE. |
FLOOR function | Rounds number down, toward zero, to the nearest multiple of significance. |
IF function | Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. |
INT function | Rounds a number down to the nearest integer. |
MAX function | Returns the largest value in a set of values. |
MAXA function | Returns the largest value in a list of arguments. |
MEDIAN function | Returns the median of the given numbers. |
MIN function | Returns the smallest number in a set of values. |
MINA function | Returns the smallest value in the list of arguments. |
MOD function |
Returns the remainder after number is divided by divisor. |
MODE function | Returns the most frequently occurring, or repetitive, value in the argument list. |
ODD function | Returns number rounded up to the nearest odd integer. |
OR function | Returns Yes if any argument is TRUE; returns No if all arguments are FALSE. |
PI function | Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. |
PRODUCT function | Multiplies all the numbers given as arguments and returns the product. |
ROMAN function | Converts an arabic numeral to roman, as text. |
ROUND function | Rounds a number to a specified number of digits. |
ROUNDDOWN function | Rounds a number down, toward zero. |
ROUNDUP function | Rounds a number up, away from 0 (zero). |
SIGN function | Determines the sign of a number. |
SQRT function | Returns a positive square root. |
SUM function | Adds all the numbers in the specified arguments. |
TEXT function | Converts a value to text in a specific number format. |
TRUE function | Returns the logical value TRUE. |
TRUNC function | Truncates a number to an integer by removing the fractional part of the number. |
USDOLLAR function | Converts a number to text using currency format, with the decimals rounded to the specified place. |
VALUE function | Converts a text string that represents a number to a number. |
Specialized Mathematical Functions:
EXP function | Returns e raised to the power of number. |
LN function | Returns the natural logarithm of a number. |
POWER function | Returns the result of a number raised to a power. |
Accounting:
DDB function | Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. |
FV function | Returns the future value of an investment based on periodic, constant payments and a constant interest rate. |
IPMT function | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. |
ISPMT function | Calculates the interest paid during a specific period of an investment. |
NPER function | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
NPV function | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). |
PMT function | Calculates the payment for a loan based on constant payments and a constant interest rate. |
PPMT function | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. |
PV function | Returns the present value of an investment. |
RATE function | Returns the interest rate per period of an annuity. |
SLN function | Returns the straight-line depreciation of an asset for one period. |
SYD function | Returns the sum-of-years’ digits depreciation of an asset for a specified period. |
Probability Theory and Statistics:
AVEDEV function | Returns the average of the absolute deviations of data points from their mean. |
BETADIST function | Returns the cumulative beta probability distribution function. |
BETAINV function | Returns the inverse of the cumulative beta probability distribution function. |
BINOMDIST function | Returns the individual term binomial distribution probability. |
CHIDIST function | Returns the one-tailed probability of the chi-squared distribution. |
CHIINV function | Returns the inverse of the one-tailed probability of the chi-squared distribution. |
CONFIDENCE function | Returns the confidence interval for a population mean with a normal distribution. |
CRITBINOM function | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |
DEVSQ function |
Returns the sum of squares of deviations of data points from their sample mean. |
EXPONDIST function | Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. |
FDIST function | Returns the F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity. |
FINV function | Returns the inverse of the F probability distribution. If p = FDIST(x,…), then FINV(p,…) = x. |
FISHER function | Returns the Fisher transformation at x. This transformation produces a function that is approximately normally distributed rather than skewed. |
FISHERINV function | Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. |
GAMMADIST function | Returns the gamma distribution. |
GAMMAINV function | Returns the inverse of the gamma cumulative distribution. |
GAMMALN function | Returns the natural logarithm of the gamma function, Γ(x). |
GEOMEAN function | Returns the geometric mean of positive data. |
HARMEAN function | Returns the harmonic mean of a data set. |
HYPGEOMDIST function | Returns the hypergeometric distribution. |
KURT function | Returns the kurtosis of a data set. |
LOG function | Returns the logarithm of a number to the base you specify. |
LOG10 function | Returns the base-10 logarithm of a number. |
LOGINV function | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. |
LOGNORMDIST function | Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with the parameters mean and standard_dev. |
NEGBINOMDIST function | Returns the negative binomial distribution. |
NORMDIST function | Returns the normal distribution for the specified mean and standard deviation. |
NORMINV function | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. |
NORMSDIST function | Returns the standard normal cumulative distribution function. |
NORMSINV function | Returns the inverse of the standard normal cumulative distribution. |
POISSON function | Returns the Poisson distribution. |
SKEW function | Returns the skewness of a distribution. |
STANDARDIZE function | Returns a normalized value from a distribution characterized by mean and standard_dev. |
STDEV function | Estimates standard deviation based on a sample. |
STDEVA function | Estimates standard deviation based on a sample. |
STDEVP function | Calculates standard deviation based on the entire population given as arguments. |
STDEVPA function | Calculates standard deviation based on the entire population given as arguments, including text and logical values. |
SUMSQ function | Returns the sum of the squares of the arguments. |
TDIST function | 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 … |
TINV function | Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom. |
VAR function | Estimates variance based on a sample. |
VARA function | Estimates variance based on a sample. |
VARP function | Calculates variance based on the entire population. |
VARPA function | Calculates variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation. |
WEIBULL function | Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure. |
Trigonometric functions:
ACOS function | Returns the arccosine, or inverse cosine, of a number. |
ACOSH function | Returns the inverse hyperbolic cosine of a number. |
ASIN function | Returns the arcsine, or inverse sine, of a number. |
ASINH function | Returns the inverse hyperbolic sine of a number. |
ATAN function | Returns the arctangent, or inverse tangent, of a number. |
ATANH function | Returns the inverse hyperbolic tangent of a number. |
COS function | Returns the cosine of the given angle. |
COSH function | Returns the hyperbolic cosine of a number. |
DEGREES function | Converts radians into degrees. |
RADIANS function | Converts degrees to radians. |
SIN function | Returns the sine of the given angle. |
SINH function | Returns the hyperbolic sine of a number. |
TAN function | Returns the tangent of the given angle. |
TANH function | Returns the hyperbolic tangent of a number. |
Date and Time Information for the Calculated Column
Text Formulas for the Calculated Column
Pingback: Text Formulas for the Calculated Column « SharePoint Diva
Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva