Scott Yokiel MOSS 2007 & .NET blog (and some bad spelling) - Other functions in a calculated column

Wednesday, September 10, 2008

(from http://office.microsoft.com/en-us/sharepointtechnology/HA101215881033.aspx)

Formulas overview

Formulas are equations that perform calculations on values in a list or library. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

You can use a formula in a calculated column and to calculate default values for a column. A formula can contain functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.), column references, operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), as in the following example.

=PI()*[Result]^2

Element	Description
Function	The PI() function returns the value of pi: 3.141592654.
Reference (or column name)	[Result] represents the value in the Result column for the current row.
Constant	Numbers or text values entered directly into a formula, such as 2.
Operator	The * (asterisk) operator multiplies, and the ^ (caret) operator raises a number to a power.

A formula might use one or more of the elements from the previous table. Here are some examples of formulas (in order of complexity).

Simple formulas (such as =128+345)

The following formulas contain constants and operators.

Example	Description
=128+345	Adds 128 and 345
=5^2	Squares 5

Formulas that contain column references (such as =[Revenue] >[Cost])

The following formulas refer to other columns in the same list or library.

Example	Description
=[Revenue]	Uses the value in the Revenue column.
=[Revenue]*10/100	10% of the value in the Revenue column.
=[Revenue] > [Cost]	Returns Yes if the value in the Revenue column is greater than the value in the Cost column.

Formulas that call functions (such as =AVERAGE(1, 2, 3, 4, 5))

The following formulas call built-in functions.

Example	Description
=AVERAGE(1, 2, 3, 4, 5)	Returns the average of a set of values.
=MAX([Q1], [Q2], [Q3], [Q4])	Returns the largest value in a set of values.
=IF([Cost]>[Revenue], "Not OK", "OK")	Returns Not OK if cost is greater than revenue. Else, returns OK.
=DAY("15-Apr-2008")	Returns the day part of a date. This formula returns the number 15.

Formulas with nested functions (such as =SUM(IF([A]>[B], [A]-[B], 10), [C]))

The following formulas specify one or more functions as function arguments.

Example	Description
=SUM(IF([A]>[B], [A]-[B], 10), [C])	The IF function returns the difference between the values in columns A and B, or 10. The SUM function adds the return value of the IF function and the value in column C.
=DEGREES(PI())	The PI function returns the number 3.141592654. The DEGREES function converts a value specified in radians to degrees. This formula returns the value 180.
=ISNUMBER(FIND("BD",[Column1]))	The FIND function searches for the string BD in Column1 and returns the starting position of the string. It returns an error value if the string is not found. The ISNUMBER function returns Yes if the FIND function returned a numeric value. Else, it returns No.