## 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?

### 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

.

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

eva danilowich 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.