Calculated Columns in SharePoint 2016

16. March 2017 Tabrez Ajaz SharePoint , SharePoint Basics Comments (0)

Introduction:

Calculated columns are those that get their values from a formula. This formula can be as simple as grabbing the value in another column in the list to crunching some complex mathematical formulas in order to determine the value and many things in between. 

We can use calculated columns to concatenate string values, add numbers, determine the difference in times, and much, much more.

Calculated columns can drastically reduce manual data entry for column values, which are prone to user error. 

In this article, I'll cover conditional formulas, date and time formulas, mathematical formulas, text formulas and many advanced formulas.

In this article, I will go through each different category of formulas that can be used to create Calculated Columns in SharePoint 2016. The formulas used are a subset of those basic formulas that available to us in Excel, not all formulas. So calculated columns are subsets of Excel Formulas.

Calculated Columns are a class in SharePoint the SPFieldCalculated class. Formula is a property of SPFieldCalculated class

SPFieldCalculated.Formula

The formulas used by Calculated Columns to generate a value are a property of that class. So, when a formula is created, it's really setting this property. If you're developing solutions for SharePoint with code, this is a helpful concept to grasp a hold of.                                e.g. yourCustomField.Formula = "=IF([age]< 50, 'Yes','No')' ";

Generally, the Formulas fall one of the following categories:

  • Conditional Formulas
  • Date and Time Formulas
  • Mathematical Formulas
  • Text Formulas

1. Conditional Formulas - "A conditional formula will check if something is true or false and return some value based upon that evaluation." Conditional formulas are often associated words like (IF, AND, OR, NOT).

e.g. I am going to create a Custom List with 4 columns:

Demo Columns:   *SKU   *prodDept(Mens, Womens, Accessories)   *cost   *above100 (Formula: =IF(cost>100,"Yep","Nope")

Create new List name: Conditional

Create Following Columns:

See how your above100 column looks like:

Now I am going to test my list with calculated columns:

I put my list in QuickEdit mode: (and entered some values in first three columns and based on these three columns the values are calculated for 4th column i.e. above100

Wow, all my calculation task performed automatically.

 

2. Date and Time Formulas -

"A date and time formulas will be very helpful when we want to calculate the number of days, months or years between two dates."

e.g. If a new project has come to your organization and the project is started and after some days the project completed and delivered to the client, and now if you want to calculate the number of days to complete the project, then it will be very helpful.

ProjectStartDate --- ProjectCompleted =  DaysToComplete (Calculated value)  =DATEDIF(ProjectStartDate,ProjectCompleted,"d") where d=# of days take

 Demo: Date and Time Formulas:

For this demo I created a List name Projects, with following Columns:

DaysToComplete calculated column and returns number result:

Output:

The value of DaysToComplete is calculated based on the values of Project start and complete date.

3. Mathematical Formulas -

"A mathematical formulas will be very helpful when we want to perform some math operations for some columns and want to calculate some result based on different columns" We can perform add, subtract, multiplication, divide and much more operations on calculated columns. When using mathematical formulas you must keep in mind about rounding of result values.

e.g. Student Film Attendance per month in 2016

Demo:

Create a named "Student Film Attendance By Month 2016" create 12 Number columns for each month and create 2 calculated columns for calculating "HighestMonthAttendance" and "TotalAttendees2016"

For this demo I created a List name "Student Film Attendance By Month 2016", with following Columns:

=MAX([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])

=SUM([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])

 

Output:

 4. Text Formulas -

"A text formulas allow you to create columns that manipulate text in many different ways." You can perform following operations:

  • Concatenation
  • Case Manipulation
  • Column Combining (Combining value from multiple columns into a single value)
  • Value Comparison
  • String manipulation

 Demo:

In this demo, I used previously create List named "Student Film Attendance By Month 2016" and create a new Calculated column named "Message" with following formula:  =CONCATENATE("Congratulations ", [TotalAttendees2016]," people saw your movie: ", UPPER([Title])," last year!")

For this demo I am using previously created List name "Student Film Attendance By Month 2016", with following new Column:

Create a new view for directors message:

Finally, your Movie page should look as shown follows:

 

I hope you like this article, so please Subscribe my Newsletter.

Tags:

Add comment