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

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 hadTaming 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