Mathematical Information for the Calculated Column

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

Conditional Formulas for the Calculated Column

Mathematical Information for the Calculated Column

Advertisements

2 Responses to Mathematical Information for the Calculated Column

  1. Pingback: Text Formulas for the Calculated Column « SharePoint Diva

  2. Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: