Trouble with Calculated Column Logic

Stump the Panel » Site Managers and Site Collection Managers

Trouble with Calculated Column Logic

(12 posts)

Started 3 months ago by erugalatha
Latest reply from erugalatha

Tags:

calculated column code

erugalatha
Member

Hi,

I'm having a bit of trouble with a Calculated Column Formula.

I want the output to be according to the following criteria:

IF [Days Until Due] <= 0 THEN show red image
IF [Days Until Due] >0 BUT <=5 THEN show amber image
IF [Days Until Due] >5 THEN show green image.

This is what I have so far but when [Days Until Due] = 0 it shows amber (should show red) and when [Days Until Due] = 5 it shows green (should show amber):

="<DIV>"&IF([Days Until Due]<=0,"<IMG src='/_layouts/images/kpipeppers-2.gif' />",IF(AND([Days Until Due]<=5,[Days Until Due]>0),"<IMG src='/_layouts/images/kpipeppers-1.gif' />",IF([Days Until Due]>5,"<IMG src='/_layouts/images/kpipeppers-0.gif' />")))&"</DIV>"

Can somebody help?

Posted 3 months ago #
Dessie Lunsford
Moderator

Try it with this:

="<DIV>"&IF([Days Until Due]=0,"<IMG src='/_layouts/images/kpipeppers-2.gif' />",IF(AND([Days Until Due]>0,[Days Until Due]<=5),"<IMG src='/_layouts/images/kpipeppers-1.gif' />",IF([Days Until Due]>5,"<IMG src='/_layouts/images/kpipeppers-0.gif' />","<IMG src='/_layouts/images/kpipeppers-2.gif' />")))&"</DIV>"

You have to check for 4 conditions:

1) Does the value = 0?
2) Is the value >0 and <5?
3) Is the value >5?
4) If its anything else, meaning - the field is empty or less than zero, the last part of the formula will set the output to the same as if the value equaled zero (red image).

Does this make sense? You were really close, just needed to separate out the logic on the first check to add in the final check of "if all else fails...".

- Dessie

Posted 3 months ago #
erugalatha
Member

Thanks a lot for your help.

It still displays an amber image when Days Until Due = 0.

I can't figure out why though - have even check to see if the correct image is on the server and it is.

Could it be that the check for =0 needs to be in a different position in the logic?

Posted 3 months ago #
Dessie Lunsford
Moderator

It is possible...I've had to rearrange the logic so certain pieces happen first, on occasion, but When I looked at your formula and came up with the alternative I had posted, it did work for me.

What I'd suggest, is breaking it down into simpler terms in order to troubleshoot it.

Since we know that the result will always be one of four possible answers:

result1 = equals 0
result2 = equals 1 thru 5
result3 = greater than 5
result4 = less than zero or null

We can write our formula in a simple manner to display one of the results (no images). Above is our key, so for each possible answer, we just put the text of the answer name in the formula (result1, result2, etc.), and look to see what happens when we enter in different answers.

=IF([TestCol]=0,"result1",IF(AND([TestCol]>0,[TestCol]<=5),"result2",IF([TestCol]>5,"result3","result4")))

In the above, "TestCol" is just a numerical column in which I'm entering a number value.

The results I got on this were:

Entered value (0): display = result1
Entered value (1): display = result2
Entered value (3): display = result2
Entered value (5): display = result2
Entered value (6): display = result3
Entered value (7): display = result3
Entered value (empty): display = result4

Let me know if this helps,

- Dessie

Posted 3 months ago #
EndUserSharePoint
Key Master

Dessie - Interesting approach. It's almost as if you have built a test suite using the Agile method. I wonder if there is a generic test suite we could construct in order to test values in all of your Calculated Column articles.

Start with a very, very simple construct that all calculated columns should test for. Build from there. It would be a very useful tool, especially with the depth in which you personally are examining the functionality of the calculated column in SharePoint.

http://www.endusersharepoint.com/?cat=397

Mark

Posted 3 months ago #
Dessie Lunsford
Moderator

Mark,
I like the idea, but I'm not sure on how to develop it given the breadth that some formulas can get into. Perhaps a suite of sample excercises (Excel spreadsheet) that can have a "how-to" section covering how to write a test bed for each formula.

I'll have to play around with the idea.

- Dessie

Posted 3 months ago #
erugalatha
Member

Dessie, thanks again for your help above - finally got it to work - but not sure how as it is working now as per your first response. :/

I think a good start on the agile stuff would be to show how logic is constructed like the above in a Calculated Column - I found it very difficult to find the structure/syntax of the IF(AND(_,_)_) statement. Then when you start nesting them it becomes very difficult to follow. Though mainly because you end up fiddling around in a tiny multi-line text box when editing the calculated column.

The Microsoft Documentation on it is buried somewhere in Technet - it's just a joke.

STP site was my last hope after pulling out a lot of hair.

Posted 3 months ago #
Dessie Lunsford
Moderator

Well, I'm glad you got it working (that's the important part)!!

My tool of choice when creating some of the more complicated formulas, even those with basic "IF" functions, is Excel...if only because it has built in nesting highlighting, so you can more easily follow the structure.

I'll see if I can work on creating a "Structure" document in Excel that makes up where the Technet documentation leaves off, by guiding the user through how to create the nesting and hierarchies of chaining multiple functions together.

- Dessie

Posted 3 months ago #
erugalatha
Member

Cool - looking forward to it as I'm not done with pulling my hair out by a long-shot on these calculated columns!

One last question while we are on the subject of formulae in calculated columns:

To create the value in [Days Until Due] field above I calculate it as follows:

=[Required Closure Date] - Modified

This gives me a number but it is Rounded upwards to the nearest integer by SharePoint thus giving back incorrect information in the calculated column.

I tried using:

=ROUNDDOWN([Required Closure Date] - Modified)

but this gives an error saying the column does not exist. In the documentation it says that =ROUNDDOWN takes a numeric value - but in SharePoint it seems to expect a column.

I also tried using =INT() but it too gave the above error.

So how to make the calculated column round it's value down when I need to subtract two dates in the calculated column too?

Posted 3 months ago #
Dessie Lunsford
Moderator

I was able to get it working by the following:

First column: "Required Closure Date"
This is a "Date and Time" column with its data entry in "Date and Time" so we can test rounding ("Date Only" will always default to 12:00, resulting in the rounding to be inaccurate).

Second column: "Days Until Due"
Calculated column with the formula of: =[Required Closure Date]-[Modified]
Data returned type is "Single line of text".

Testing this now will produce numerical values with several decimal places due to how the "DateTime" object is serialized.

Examples:

Title: "Test 1"
Required Closure Date: 11/27/2008 11:00 PM
Modified: 11/25/2008 8:46 AM
Days Until Due: 2.59275463

Title: "Test 2"
Required Closure Date: 12/16/2008 9:30 AM
Modified: 11/25/2008 8:56 AM
Days Until Due: 21.023298611

Now, since we want it to be in whole numbers and round down to the nearest number (a simple "Round" operation would round up anything over .5 giving us the appearance of an extra day), we can use the "ROUNDDOWN()" function as you mentioned, to do this for us.

The "ROUNDDOWN()" function uses the format of:

=ROUNDDOWN("Number to round down", "Number of decimal places")

(Note the second half of the formula? I think this where you got the error from - you forgot to tell it how many decimal places to use)

So, modify the "Days Until Due" formula to be: =ROUNDDOWN(([Required Closure Date]-Modified),0).

After saving, the results should be updated into whole numbers rounded down to the nearest integer.

Title: "Test 1"
Required Closure Date: 11/27/2008 11:00 PM
Modified: 11/25/2008 8:46 AM
Days Until Due: 2

Title: "Test 2"
Required Closure Date: 12/16/2008 9:30 AM
Modified: 11/25/2008 8:56 AM
Days Until Due: 21

Let me know how it works for you.

- Dessie

Posted 3 months ago #
erugalatha
Member

It works very good now. I actually had the 0 in there for number of decimal places but when I took a closer look and put in [Modified] (with the square brackets this time) it worked. It seems when I choose "Add to Forumla" it puts in [] brackets around the fieldname but then when you submit that as your formula it removes the brackets from fieldnames with no spaces and goes to the error page. Then when you try to submit again it says the field does not exist. I don't have a better explanation other than that so it's just another SharePoint WTF? moment! :D

Thanks very much for your help.

There sure is a lot of work involved with these Calculated Columns. :/

Posted 3 months ago #
erugalatha
Member

Extra Note: The only reason I noticed this was before rounding down for overdue tasks where Required Closure Date is due today that I was getting -1 days in the afternoon on tasks due today. Which meant if Required Closure Date = 25/11/2008 and Today is 25/11/2008 and it is the afternoon then it would round down values over 0.5 to -1 giving -1 days for tasks that should have been 0 days.

Posted 3 months ago #

RSS feed for this topic

Reply

You must log in to post.