You can use formulas and functions in lists or libraries to calculate data in a variety of ways. By adding a calculated column to a list or library, you can create a formula that includes data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text. For example, on a tasks list, you can use a column to calculate the number of days it takes to complete each task, based on the Start Date and Date Completed columns.
Note: This article describes the basic concepts related to using formulas and functions. For specific information about a particular function, see the article about that function.
In this article
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, column references, operators, and constants, 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. |
Functions overview
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the following instance of the ROUND function rounds off a number in the Cost column to two decimal places.
=ROUND([Cost], 2)
The following vocabulary is helpful when you are learning functions and formulas:
Structure The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
Function name This is the name of a function that is supported by lists or libraries. Each function takes a specific number of arguments, processes them, and returns a value.
Arguments Arguments can be numbers, text, logical values such as True or False, or column references. The argument that you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the sum of two column values.
=AVERAGE([Cost1], SUM([Cost2]+[Discount]))
Valid returns When a function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument uses Yes or No, then the nested function must return Yes or No. If it doesn't, the list or library displays a #VALUE! error value.
Nesting level limits A formula can contain up to eight levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. In the example above for instance, the SUM function is a second-level function because it is an argument of the AVERAGE function. A function nested within the SUM function would be a third-level function, and so on.
Notes:
-
Lists and libraries do not support the RAND and NOW functions.
-
The TODAY and ME functions are not supported in calculated columns but are supported in the default value setting of a column.
Using column references in a formula
A reference identifies a cell in the current row and indicates to a list or library where to search for the values or data that you want to use in a formula. For example, [Cost] references the value in the Cost column in the current row. If the Cost column has the value of 100 for the current row, then =[Cost]*3 returns 300.
With references, you can use the data that is contained in different columns of a list or library in one or more formulas. Columns of the following data types can be referenced in a formula: single line of text, number, currency, date and time, choice, yes/no, and calculated.
You use the display name of the column to reference it in a formula. If the name includes a space or a special character, you must enclose the name in square brackets ([ ]). References are not case-sensitive. For example, you can reference the Unit Price column in a formula as [Unit Price] or [unit price].
Notes:
-
You cannot reference a value in a row other than the current row.
-
You cannot reference a value in another list or library.
-
You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
-
You cannot reference another column in a formula that creates a default value for a column.
Using constants in a formula
A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. Constants can be of the following data types:
-
String (Example: =[Last Name] = "Smith")
String constants are enclosed in quotation marks and can include up to 255 characters.
-
Number (Example: =[Cost] >= 29.99)
Numeric constants can include decimal places and can be positive or negative.
-
Date (Example: =[Date] > DATE(2007,7,1))
Date constants require the use of the DATE(year,month,day) function.
-
Boolean (Example: =IF([Cost]>[Revenue], "Loss", "No Loss")
Yes and No are Boolean constants. You can use them in conditional expressions. In the above example, if Cost is greater than Revenue, the IF function returns Yes, and the formula returns the string "Loss". If Cost is equal to or less than Revenue, the function returns No, and the formula returns the string "No Loss".
Using calculation operators in a formula
Operators specify the type of calculation that you want to perform on the elements of a formula. Lists and libraries support three different types of calculation operators: arithmetic, comparison, and text.
Arithmetic operators
Use the following arithmetic operators to perform basic mathematical operations such as addition, subtraction, or multiplication; to combine numbers; or to produce numeric results.
| Arithmetic operator | Meaning (example) |
|---|---|
| + (plus sign) | Addition (3+3) |
| – (minus sign) | Subtraction (3–1) |
| * (asterisk) | Multiplication (3*3) |
| / (forward slash) | Division (3/3) |
| % (percent sign) | Percent (20%) |
| ^ (caret) | Exponentiation (3^2) |
Comparison operators
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value of Yes or No.
| Comparison operator | Meaning (example) |
|---|---|
| = (equal sign) | Equal to (A=B) |
| > (greater than sign) | Greater than (A>B) |
| < (less than sign) | Less than (A<B) |
| >= (greater than or equal to sign) | Greater than or equal to (A>=B) |
| <= (less than or equal to sign) | Less than or equal to (A<=B) |
| <> (not equal to sign) | Not equal to (A<>B) |
Text operator
Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
| Text operator | Meaning (example) |
|---|---|
| & (ampersand) | Connects, or concatenates, two values to produce one continuous text value ("North"&"wind") |
Order in which a list or library performs operations in a formula
Formulas calculate values in a specific order. A formula might begin with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Lists and libraries calculate the formula from left to right, according to a specific order for each operator in the formula.
Operator precedence
If you combine several operators in a single formula, lists and libraries perform the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication operator and a division operator — lists and libraries evaluate the operators from left to right.
| Operator | Description |
|---|---|
| – | Negation (as in –1) |
| % | Percent |
| ^ | Exponentiation |
| * and / | Multiplication and division |
| + and – | Addition and subtraction |
| & | Concatenation (connects two strings of text) |
| = < > <= >= <> | Comparison |
Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula that is to be calculated first. For example, the following formula produces 11 because a list or library calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, the list or library adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force the list or library to calculate [Cost]+25 first and then divide the result by the sum of the values in columns EC1 and EC2.
=([Cost]+25)/SUM([EC1]+[EC2])
No comments:
Post a Comment