# 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

Advertisements

Pingback: Text Formulas for the Calculated Column « SharePoint Diva

Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva