Calculation field formulas on Sharepoint

Sharepoint can use calculate formula like as Excel, so that field value can be calculated with other field values. Using formula, calculation of date, string, numbers and logics are all possible. For example, with two fields as start date and end date, duration can be automacally calculated and displayed in calculated field.

About Formula

Formula for calculated field can be entered within field configuration. Same as Excel, the formula start from equal character (=). As Excel's A1, B2, other fields can be referenced with field name.


Functions can be also used same as Excel. For example, [IF] for determin by field value, [Date] to transform to string to Date or [Substitute] to crop the part of text string.


[SUM] function, which is most frequently used in Excel, can be used for calculated field as well. But it can only refer to values in same item ("row" in excel) and can not refer to other items ("column" in excel), so it might not be useful as excel.

 

Example of formulas which results in Yes/No (Boolean)


MoriApps Simple Workflow also uses calculation field for conditional approval tasks to determin whther the approval task is required or not. It uses the calculation field to result in Yes/No value (Boolean). We here explain three example.

Example One

Thare are [Category] field and if [Category] is [Computer] or [Keyboard], the field [Requre IT approval] become [Yes].

1. Create [Category] field with Choice type, and add choices like  [Computer], [Keyboard], [Desk] and [Chair].
2. Create [Require IT approval] field as calculated field and enter the formula as below.

=OR(Category="Computer",Category="Keyboard")

Example Two

Thare are [Price] field and if [Price] is more than 50000, the field [Requre IT approval] become [Yes].

1. Create [Price] field with Number or Currency.
2. Create [Require IT approval] field as calculated field and enter the formula as below.

=Price > 50000

 

Example Three

Thare are [Category] field and [Price] field and if [Category] is [Computer] or [Keyboard] and [Price] is more than 50000, the field [Require IT approval] become [Yes].

1. Create [Category] field with Choice type, and add choices like  [Computer], [Keyboard], [Desk] and [Chair].
2. Create [Price] field with Number or Currency.
3. Create [Require IT approval] field as calculated field and enter the formula as below.

=AND(OR(Category="Computer",Category="Keyboard"),Price > 50000)