SharePoint: Creating Calculated Column Formulas the Easy Way Using Excel

The following works in both 2007 and 2010.

The MSDN documentation on calculated columns:

• 2007- http://msdn.microsoft.com/en-us/library/office/bb862071(v=office.12).aspx
• 2010 - http://msdn.microsoft.com/en-us/library/office/bb862071.aspx
• 2013 – Nothing published yet, but the 2010 article still applies.

 

Calculated Columns

Below is a screen capture of where you enter the formula for a Calculated column. See that big empty space to the left… wouldn’t it have been great if they had used that space for some notes, examples, or even links on how to create formulas?

Built in help?

Click the the Help button and then search for “Introduction to data calculations” and “Examples of common formulas”

 

A short FAQ on calculated columns:

• Calculated columns can only interact with data in the same “item” – an item is a single task, event, document, etc.
• Calculated columns cannot interact with another row, or summaries (total, etc) of all of the list
• The formulas you write are basically Excel compatible formulas – most will work! – see the links at the end of this article
• Calculated columns can be reused by creating them as Site Columns (but this column can only reference other Site Columns!)
• Column names with spaces or symbols must be enclosed in brackets “[Sale Amount']”
• Nesting is only supported for eight levels – Example of three levels: =IF(a>b,1, IF(b>c, 2, IF(c>d, 3, 99)))
• The [TODAY] and [ME] functions do not work in a calculated column, but can be used in columns referenced by a calculated column
• RAND and NOW are not supported in SharePoint 

Ok, got that out of the way…

 

The Easy Way to Write Formulas

 

Writing complex equations in a simple text editor is not easy. Lots of trial and error, and mostly error. Instead use Excel!  Name one cell for each column you are using in your calculation.

Example: We need a little challenge in this one… we’ve got a list of computer stuff for sale… you get a discount if you buy in quantity… the discount varies based on the type of item…

• Buy less than 10 items, no discount
• Buy 10 or more Hardware items and get a 30% discount
• Buy 10 or more Software items and get a 50% discount
• Buy 10 or more “Other” items and get a 20% discount

Steps:

1. Open Excel
    
2. Down column A list the names of columns:
    
    
3. Highlight the names you entered and the empty cells in the next column
    
    
4. From the Formulas Ribbon tab, click “Create from Selection”, select only “Left column” and click OK
    
    
5. If you want to confirm that you named your cells, click one of the cells in the second column and check for the cell in the formula bar 
    
    
6. Enter some test data:
   
    
7. Now write an equation using normal Excel rules, but use cell names instead of cell addresses:
        = Qty * Price 
   
   IntelliSense even helps by listing your cell names!
    
8. Now add our first discount test – Less than 10 gets no discount, more than 10 gets the minimum of 20%:
    
   IntelliSense helps again… now finish the formula:
       
    
9. Do some testing now:
        
    
10. Let’s change the formula a bit to save some repetition:
     
     
11. If is is hardware they get 30%:   (click the image below if you don’t see all of the formula)
     
    And if you want a translation, it would be something like this:
    The first comma of the IF is the “THEN” and the second comma is the “ELSE”:
         IF Qty<10 THEN 100% ELSE   IF Category=”Hardware” THEN 70% ELSE …
    
    This is standard Excel… nothing special for SharePoint!
     
12. And do some testing:
          
     
13. Now copy the equation from Excel and paste it into SharePoint:
     
     
14. and the result:
     

Writing equations of this sort is often is a two part process: break the problem down into pieces and then assemble and test each piece at a time. the challenge is ending up with a single formula as a single line of text.

 

What about spaces in column names?

When you follow the steps above, but with column names with space or symbols, Excel will create cell names with underlines.  “Sales Price” becomes “Sales_Price”. When you copy and paste these into SharePoint you will need to change the underlines back to spaces and enclose the names in square brackets like this: [Sales Price].  

Another example: 
   Excel will change   Total & Tax    into  Total___Tax    and you will need to change it back to    [Total & Tax] 

 

Text “math”

Use the ampersand to combine text columns. You will usually need to add spaces and commas to the results. Literal text goes in quotes.

 

Functions of all kinds:

Let’s finance a car:

See the resource links below for more examples.

 

 

A few resources:

 

Introduction to data calculations

http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx

 

Examples of common formulas

http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx

 

.