1,804 articles and 14,860 comments as of Thursday, May 19th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

Monday, February 23, 2009

Taming the Elusive “Calculated Column” – Logic – Part 1

Comments: 11

This is the first installment of a ten part series on using logic in the SharePoint Calculated Column. We explore the basic foundations of how to use logic in the calculated column through step-by-step procedures that can be replicated when you need to create your own logical formulas.

Overview

In a previous article, we began with a discussion on the nature of the “IF” statement being a “True/False” logic operation:

If the answer to my question is “True”, do this, and if the answer is “False”, do that.”

We also took a look at how to work with “Nested” functions in order to test for several conditions by inserting a second question into the “TRUE” answer of our first question.

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),”Don’t Eat”)

Although this is indeed a valid example of how to test for multiple conditions, we should take a look at what was left out of this formula that would’ve made it more “complete”.

Getting Started

Using our example from last time, let’s start by reviewing the logic that takes place in the formula:

Is it true that I am hungry?

If so, then is it true that I am thirsty?

If so, I will eat and drink.

If not, I will eat.

If it is not true that I am hungry, I will not eat.

Working through the logic, it’s apparent that we were looking at this from the point-of-view of just being hungry. What we didn’t take in account for was the question of “What about if I were only thirsty?

Therein lays one of the inherent difficulties when working with nested operations. In order to account for each possible scenario, you must add in additional logical checks for each, which tends to make your formulas expand immensely (more on this later) with each additional question.

In our case, the formula only checks the second question if, and only if, the result of the first question is “TRUE”. If it is, the logic moves on to the next question to see if we are also “Thirsty”. If the question happens to be “FALSE” however, we never get a chance to even ask the question of “Thirsty”, so we need to remedy this by rewriting our logic in a fashion that will always ask the second question regardless of the answer from the first question.

Adding the IF

We do this by adding in our second question to the “FALSE” result of the first question.

Original formula:

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),”Don’t Eat”)

New formula:

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”), IF(Thirsty,”Drink”,”Don’t Eat or Drink”))

This changes our logic to:

Is it true that I am hungry?

If so, then is it true that I am thirsty?

If so, I will eat and drink.

If not, I will eat.

If it is not true that I am hungry, is it true that I am thirsty?

If so, I will drink.

If not, I will not eat or drink.

By doing this, we’ve in effect, asked the same (second) question twice – but, with different possible answers based on where (or when) it gets asked.

Based on our questions, we can look at the total possible results by examining the logic of our questions through a “Truth Table”:

A “Truth Table” is a testing tool that you can use to validate the expected results from a given logic problem by pre-loading each possible answer and giving a result for each combination of answers. Once setup, you can use it to do a run-through of a problem to see if it returns the expected results.

Most often, this tool is used by programmers when “penciling” out a piece of logic in an application to test its performing as expected.

Although a “Calculated Column” is not specifically an application or program (in the traditional sense), since we’re working with formulas that have to follow a “Logical” path to arrive at a result, tools like this can help in troubleshooting when the results you get aren’t what you expected.

The combinations of each possible answer will give us a list of what we could see as a final result.

TRUE TRUE = “Eat and Drink”

TRUE FALSE = “Eat”

FALSE TRUE = “Drink”

FALSE FALSE = “Don’t Eat or Drink”

If question 1 is True and Question 2 is True, display “Eat and drink”.

If Question 1 is True and Question 2 is False, display “Eat”.

If Question 1 is False and Question 2 is False, display “Don’t Eat or Drink”.

Tomorrow we will look at testing our results and display the output of the list. Stay tuned…

- Dessie

Dessie Lunsford
Points-of-Sharing

View all entries in this series: Dessie Lunsford-Logic Formulas»

Entries in this series:

I recommend SharePoint resources daily on my twitter account here.

Posted by Dessie Lunsford on Monday, February 23, 2009 at 5:00 pm
Filed under Dessie Lunsford · Tagged with Calculated Column, Logical Function, sharepoint

Please Join the Discussion

11 Responses to “Taming the Elusive “Calculated Column” – Logic – Part 1”

Thomas Olsson says:

June 10, 2009 at 9:05 am

Hi! I have a tricky question!
I have two columns called “Impact” and “Urgency” set to “choice” with three different options “low”, “medium”, “high”.

When a user sets for example “low” on the Impact and “low” on the Urgency I want to create a third column called PRIORITY that takes the to “low” values and sets a priority value.. 1-5
low +low = 1
low + medium = 2
medium + medium = 3
high + medium =4
high + high = 5

Is this possible!??????

/Thomas

Reply
- Paul Kensler says:
  
  June 22, 2010 at 1:55 pm
  
  I was searching for the same solution and came across this post; I came up with the answer and thought I’d post back if others stumble across it.
  Yes, this can be done using nested IF statements, BUT you can only have 7 nested IFs, so I sort of cheated. If you outline a table of your Impact and Urgency scores, you end up with 9 cells (my difference from the original question, is I score the highest priority as 1 – so the lower the number, the higher the priority)
  Low I + Low U = 5
  Low I + Med U = 4
  Low I + High U = 3
  Med I + Low U = 4
  Med I + Med U = 3
  Med I + High U = 2
  High I + Low U = 3
  High I + Med U = 2
  High I + High U = 1
  
  So I wrote the IF statements to account for the scores 1, 2, 4 and 5, and made the final “else” = 3. So any combination of the above Impact and Urgency scores either directly results in a logical match, or it defaults to 3 (which for my purposes was safe, because 3 = medium priority).
  
  =IF(AND(Impact=”Low”,Urgency=”Low”),5,IF(AND(Impact=”Low”,Urgency=”Medium”),4,IF(AND(Impact=”Medium”,Urgency=”Low”),4,IF(AND(Impact=”Medium”,Urgency=”High”),2,IF(AND(Impact=”High”,Urgency=”Medium”),2,IF(AND(Impact=”High”,Urgency=”High”),1,3))))))
  
  Hope this helps somebody!
  
  Paul
  
  Reply
Thomas says:

July 17, 2009 at 4:20 am

How is it possible to calculate with the column “name”.
If I try “=UPPER([Name])” I always get the error message “The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.
Troubleshoot issues with Windows SharePoint Services.”

But at the document library I have the column “Name”. I also used the internal names like “FileLeafRef” but I failed.

Best regards
Thomas

Reply