8/06/2010

SharePoint: Creating Calculated Column Formulas the Easy Way Using Excel

The following works in both 2007 and 2010.

The MSDN documentation on calculated columns:

 

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?

image

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:
      image
     
  3. Highlight the names you entered and the empty cells in the next column
      image
     
  4. From the Formulas Ribbon tab, click “Create from Selection”, select only “Left column” and click OK
      image
     
  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 
      image
     
  6. Enter some test data:
    image
     
  7. Now write an equation using normal Excel rules, but use cell names instead of cell addresses:
         = Qty * Price 
    image
    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%:
      image
    IntelliSense helps again… now finish the formula:
      image  
     
  9. Do some testing now:
      image    image
     
  10. Let’s change the formula a bit to save some repetition:
      image
     
  11. If is is hardware they get 30%:   (click the image below if you don’t see all of the formula)
    image 
    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:
    image   image    image
     
  13. Now copy the equation from Excel and paste it into SharePoint:
      image
     
  14. and the result:
      image

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.

image

 

Functions of all kinds:

Let’s finance a car:

image

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

 

.

7 comments:

Ryan said...

re:>The [TODAY] and [ME] functions do not work in a calculated column, but can be used in columns referenced by a calculated column

Are you sure about that Mike?

Mike Smith said...

Ryan,

> Are you sure about that Mike?

Here's is the error message you get if you try to add them:

"Calculated columns cannot contain volatile functions like Today and Me."

But they can be used as a Default value for other columns, or in view Filters.

Mike

Anonymous said...

Brilliant! This worked for me trying to disect then concanenate an email address. The expression wasn't advanced, but would have taken hours of trial and error in SharePoint browser.

Salman said...

how can I show calculated column in create new form ,actually i want to apply formula if field is blank.

Mike Smith said...

Salman,

That's not really a calculated column question, so the article above won't help much. You have several options to "to apply formula if field is blank".

One is to set a default for the column, and use a calculated default. But keep in mind that the formula cannot reference other columns. ("Error: The formula contains reference(s) to field(s). ")

Another is to edit the new and the edit forms using SharePoint Designer and add some JavaScript to set / update the calculated value.

If you have the Enterprise Edition and are using 2010 or later, you can customize the form using InfoPath and get very creative with defaults and calculated values.

Mike

Unknown said...

This is the coolest tip I've come across in a long, long time! Thank you!

Vijendran said...

Thank you. It really helped a lot, I was trying to formulate a 6 level IF loop and was not working from yesterday for one condition. Never thought of using Excel to design the formula, it really helped to identify the issue and fix it.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.