Introduction to the formula function for calculating values in SPS

Document directory

• Simple Formula (for example, = 128 + 345)
• Formulas that contain column references (for example, = [revenue]> [cost])
• Formula for calling a function (for example, = average (1, 2, 3, 4, 5 ))
• Formulas containing nested functions (for example, = sum (if ([a]> [B], [a]-[B], 10), [c])
• Arithmetic Operators
• Comparison Operators
• Text Operators
• List or database execution sequence of operations in the formula

In SPS, you can use formulas and functions in the list or library to calculate data in multiple ways. By adding computing columns to the list or library, you can create formulas for data from other columns and execute functions to calculate the date and time, run mathematical equations, or process words. For example, in the task list, you can use a column to calculate the number of days required to complete each task based on the start date and completion date columns.

NoteThis section describes the basic concepts related to the use of formulas and functions. For specific information about a function, see the article about this function.

Content

Formula Overview

Function Overview

Use column reference in Formulas

Use constants in Formulas

Use operators in Formulas

Formula Overview

A formula is an equation used to calculate values in a list or database. The formula starts with equal sign (=. For example, in the following formula, the result is equal to 2 multiplied by 3 and then 5.

=5+2*3

You can use formulas in calculated columns, or use formulas to calculate the default values of a column. A formula can contain a function (function: a function is a pre-written formula. You can perform operations on one or more values and return one or more values. A function can simplify and shorten formulas in a worksheet, especially when a formula is used for a long or complex calculation .) , Column reference, operator (OPERATOR: a tag or symbol that specifies the type of calculation performed in the expression. There are mathematical, comparison, logic, and reference operators .) And constants (constants: values that are not calculated, so they do not change. For example, the number 210 and text "quarterly income" are constants. The values generated by expressions and expressions are not constants .), As shown in the following example.

=PI()*[Result]^2

Element	Description
Function	The PI () function returns the value of the circumference ratio pi 3.141592654.
Reference (or column name)	[Result] indicates the value in the "result" column of the current row.
Constant	Enter a number or text value directly in the formula, such as 2.
Operator	* The asterisk (asterisk) operator performs multiplication. The ^ (insert symbol) operator represents the power of a number.

The formula can use one or more elements in the preceding table. The following are examples of formulas sorted by complexity.

Simple Formula (for example, = 128 + 345)

The following formulas contain constants and operators.

Example	Description
= 128 + 345	Add 128 and 345
= 5 ^ 2	Calculate the square of 5

Formulas that contain column references (for example, = [revenue]> [cost])

The following formula references other columns in the same list or library.

Example	Description
= [Revenue]	Use the value in the "revenue" column.
= [Revenue] * 10/100	10% of the value in the "revenue" column.
= [Revenue]> [cost]	If the value in the "revenue" column is greater than the value in the "cost" column, "yes" is returned ".

Formula for calling a function (for example, = average (1, 2, 3, 4, 5 ))

The following formula calls built-in functions.

Example	Description
= Average (1, 2, 3, 4, 5)	Returns the average value of a set of values.
= Max ([Q1], [Q2], [Q3], [Q4])	Returns the maximum value of a group of values.
= If ([cost]> [revenue], "Not OK", "OK ")	If the cost is higher than the income, "not OK" is returned ". Otherwise, "OK" is returned ".
= Day ("15-apr-2008 ")	Returns the day from the date. This formula returns the number 15.

Formulas containing nested functions (for example, = sum (if ([a]> [B], [a]-[B], 10), [c])

The following formula specifies one or more functions as function parameters.

Example	Description
= Sum (if ([a]> [B], [a]-[B], 10), [c])	The IF function returns the difference value between column A and column B or 10. The sum function adds the return value of the IF function to the value in Column C.
= Degrees (PI ())	The PI function returns the number 3.141592654. The degrees function converts a radian value to an angle value. This formula returns a value of 180.
= Isnumber (find ("BD", [column1])	The find function searches for the string BD in column1 and returns the starting position of the string. If this string is not found, an error value is returned. If the find function returns a value, the isnumber function returns "yes ". Otherwise, it returns "no ".

 

Function Overview

A function is a predefined formula that uses a specific value called a parameter for calculation in a specific order or structure. Functions can be used to perform simple or complex computations. For example, the following round function instance can round the number in the "cost" column to two digits after the decimal point.

=ROUND([Cost], 2)

When learning functions and formulas, the following words are helpful:

StructureThe function structure starts with the equal sign (=), followed by the function name, left brackets, and function parameters separated by commas (,), and ends with the right brackets.

Function NameThe name of the function supported by the list or library. Each function references a specific number of parameters, processes these parameters, and returns a value.

ParametersParameters can be numbers, text, logical values (such as true or false), or column references. The specified parameter must be a valid value of this parameter. Parameters can also be constants, formulas, or other functions.

In some cases, you may need to use a function as a parameter of another function. For example, the following formula uses the nested average function to compare the result with the sum of the values of the two columns.

=AVERAGE([Cost1], SUM([Cost2]+[Discount]))

Valid Return ValueWhen a function is used as a parameter, its return value must be of the same type as the value used by the parameter. For example, if the parameter uses "yes" or "no", the nested function must return "yes" or "no"; otherwise, the list or library will display # value! Error value.

Nesting layer restrictionsA formula can contain a maximum of eight layers of nested functions. When function B is used as a parameter in function a, function B is the second-level function. For example, in the preceding example, the sum function is a second-level function because it is a parameter of the average function. The nested function in the sum function is a layer-3 function, and so on.

Note

• The list and library do not support the Rand and now functions.
• The today and me functions are not supported in the calculation column, but these two functions are supported in the default setting of a column.

Use column reference in Formulas

A reference identifies a cell in the current row and instructs the list or library where to search for the value or data to be used in the formula. For example, [cost] references the value in the "cost" column of the current row. If the value of the "cost" column in the current row is 100, = [cost] * 3 returns 300.

By referencing, you can use the data contained in different columns of the list or library in one or more formulas. You can reference columns of the following data types in the formula: Single Row text, number, currency, date and time, selection, YES/NO, and calculation column.

You can use the Column Display name to reference this column in the formula. If the name contains spaces or special characters, the name must be enclosed in square brackets. References are case insensitive. For example, you can use [unit price] or [unit price] in the formula to reference the "unit price" column.

Note

• Values in rows other than the current row cannot be referenced.
• Values in other lists or libraries cannot be referenced.
• The row ID cannot be used to reference the newly inserted row. Because this ID does not exist during computing.
• Other Columns cannot be referenced in the formula for creating default values for a column.

Use constants in Formulas

Constants are values that do not need to be calculated. For example, the date, the number 210, and the text "quarterly income" are constants. Constants can be of the following data types:

• String (example: = [last name] = "Smith ")

  String constants are enclosed in quotation marks and can contain a maximum of 255 characters.

• Number (example: = [cost]> = 29.99)

  A numeric constant can contain decimal places, either positive or negative.

• Date (example: = [date]> date (2007,7, 1 ))

  The date constant must use the date (year, month, day) function.

• Boolean (example: = If ([cost]> [revenue], "loss", "No loss ")

  "Yes" and "no" are Boolean constants. Boolean constants can be used in condition expressions. In the preceding 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 ".

Use operators in Formulas

Operator specifies the type of operation to be performed on the elements in the formula. The list and library support three types of operators: Arithmetic Operators, comparison operators, and text operators.

Arithmetic Operators

You can use the following Arithmetic Operators to perform addition, subtraction, multiplication, and other basic mathematical operations, and combine numbers or generate numerical results.

Arithmetic Operators	Meaning (example)
+ (Plus sign)	Addition (3 + 3)
-(Minus sign)	Subtraction (3-1) Negative (-1)
* (Asterisk)	Multiplication (3*3)
/(Forward slash)	Division (3/3)
% (Percent)	Percentage (20%)
^ (Insert symbol)	Power Operation (3 ^ 2)

Comparison Operators

You can use the following operators to compare two values. When you use these operators to compare two values, the result is a logical value "yes" or "no ".

Comparison Operators	Meaning (example)
= (Equal sign)	Equal to (A = B)
> (Greater than the number)	Greater than (A> B)
<(Yu no)	Less than (a <B)
> = (Greater than or equal to no)	Greater than or equal to (A> = B)
<= (Less than or equal to no)	Less than or equal to (a <= B)
<> (Not equal)	Not equal to (a <> B)

Text Operators

Use the Ampersand (&) to join or connect one or more text strings to generate a string of text.

Text Operators	Meaning (example)
& (And number)	Concatenates or joins two values to generate a continuous text value ("North" & "wind ")

List or database execution sequence of operations in the formula

The formula calculates the value in a specific order. The formula can start with equal sign (=. The equal signs are followed by the elements (operands) to be calculated. They are separated by operators. According to the specific order of each operator in the formula, the list and library calculate the formula from left to right.

Operator priority

If multiple operators are used in a formula, the list and database perform operations in the order shown in the following table. If a formula contains operators with the same priority, for example, a formula contains both a multiplication operator and a division operator, the list and libraries calculate the operators from left to right.

Operator	Description
-	Negative number (for example,-1)
%	Percentage
^	Power
* And/	Multiplication and division
+ And-	Addition and subtraction
&	Connect (connect two texts)
= <> <=> = <>	Comparison

Brackets

To change the order of evaluation, enclose the calculated parts in parentheses. For example, the result of the following formula is 11, because the list or library performs the addition operation after the multiplication operation. This formula is used to multiply 2 and 3, and then add 5. The resulting value is the final result.

=5+2*3

In contrast, if you use parentheses to change the syntax, the list or library first adds 5 and 2, and then multiply the obtained result by 3. The final result is 21.

=(5+2)*3

In the following example, the brackets 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 values in the column EC1 and EC2.

=([Cost]+25)/SUM([EC1]+[EC2])