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

Conditional Formulas 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: Conditional formulas

Other Conditional Functions:

AND function	Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.
CHOOSE function	Uses index_num to return a value from the list of value arguments.
COUNT function	Counts the number of arguments that contain numbers.
COUNTA function	Counts the number of arguments that are not empty.
EXACT function	Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise.
FALSE function	Returns the logical value FALSE.
IF function	Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
ISBLANK	Value refers to an empty column reference.
ISERR	Value refers to any error value except #N/A.
ISERROR	Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL	Value refers to a logical value.
ISNA	Value refers to the #N/A (value not available) error value.
ISNONTEXT	Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank column reference.)
ISNUMBER	Value refers to a number.
ISTEXT	Value refers to text.
NOT function	Reverses the value of its argument.
OR function	Returns Yes if any argument is TRUE; returns No if all arguments are FALSE.
T function	Returns the text referred to by value.
TRUE function	Returns the logical value TRUE.

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

Text Formulas 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: Text formulas

Other Text Functions:

AND function	Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.
ASC function	For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.
AVERAGEA function	Calculates the average (arithmetic mean) of the values in the list of arguments.
CHAR function	Returns the character specified by a number.
CHOOSE function	Uses index_num to return a value from the list of value arguments.
CLEAN function	Removes all nonprintable characters from text.
CODE function	Returns a numeric code for the first character in a text string.
CONCATENATE function	Use the CONCATENATE function to join several text strings into one string.
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.
EXACT function	Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise.
FIND function	Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.
FIXED function	Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
IF function	Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
LEFT function	LEFT returns the first character or characters in a text string, based on the number of characters you specify.
LEN function	LEN returns the number of characters in a text string.
LOWER function	Converts all uppercase letters in a text string to lowercase.
MAXA function	Returns the largest value in a list of arguments.
Me function	Returns the current user name.
MID function	MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
MINA function	Returns the smallest value in the list of arguments.
OR function	Returns Yes if any argument is TRUE; returns No if all arguments are FALSE.
PROPER function	Capitalizes the first letter and any other letters that follow a non-letter character in a text string.
REPLACE function	REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
REPT function	Repeats text a given number of times.
RIGHT function	RIGHT returns the last characters in a text string, based on the number of characters you specify.
ROMAN function	Converts an arabic numeral to roman, as text.
SEARCH function	SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num.
T function	Returns the text referred to by value.
TEXT function	Converts a value to text in a specific number format.
TRIM function	Removes all spaces from text except for single spaces between words.
TRUE function	Returns the logical value TRUE.
UPPER function	Converts text to uppercase.
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.

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

Date and Time Information for the Calculated Column

I’ve had “Taming the Elusive ‘Calculated Column’” bookmarked forever. All in one place were the links I needed for figuring out the formulas and functions for calculated columns. Unfortunately the Microsoft pages those links point to have been “improved” and I don’t think they’re as helpful as they used to be. Examples of common formulas doesn’t list everything. I’ve found a list of all the functions, but it’s not grouped in any helpful way (as far as I can tell.)

Examples of Date and Time Formulas

I have my own examples that aren’t specified in the Microsoft Examples (they’re implied but never explicitly shown.)

To extract the date and time in different ways:

Column1	Formula		Description (result)
9/5/2012 12:03 PM	=TEXT(Column1,”YYYYmmDDHHmm”)		Show date in years, months, days, hours and minutes (201209051203)
9/5/2012 12:03 PM	=TEXT(Column1,”YYYY”)		Show the 4 digit year (2012)
9/5/2012 12:03 PM	=TEXT(Column1,”MM”)&”-“&TEXT(Column1,”MMMM”)		Show the numerical value of the month, in two digits, and the actual month name (09-September) I use this for sorting the months correctly.

Other Date Functions:

DATE function	Returns the sequential serial number that represents a particular date.
DATEDIF function	Calculates the number of days, months, or years between two dates.
DATEVALUE function	Returns the serial number of the date represented by date_text.
DAY function	Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
DAYS360 function	Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations.
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.
HOUR function	Returns the hour of a time value.
MINUTE function	Returns the minutes of a time value.
MONTH function	Returns the month of a date represented by a serial number.
SECOND function	Returns the seconds of a time value.
TIME function	Returns the decimal number for a particular time.
TIMEVALUE function	Returns the decimal number of the time represented by a text string.
TODAY function	Returns the serial number of the current date.
WEEKDAY function	Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
YEAR function	Returns the year corresponding to a date.

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