Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I'm trying to use a HTML calculated column so that I can display a RAG status bar for my tasks in SharePoint. I had it working until I added the IF containing % complete. Can you tell me where I'm going wrong please?

="<DIV style='width:100%;background-color:"&IF(Slippage>30,"red",IF(AND(Slippage>=-7,Slippage<=-29),"orange",IF(AND(Slippage>=-7,Slippage<=29),"orange",IF(Slippage<7,"green","green",IF(% Complete=100,"blue","blue")))))&";'></DIV>"
share|improve this question
    
This is a cross post of the following MSDN question: social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/… –  Stuart Pegg Jan 12 '12 at 17:57
add comment

1 Answer

I reformatted your formula to:

="<DIV style='width:100%;background-color:"&
  IF(Slippage>30,"red",
    IF( AND(Slippage>=-7,Slippage<=-29),"orange",
      IF( AND(Slippage>=-7,Slippage<=29),"orange",
        IF(Slippage<7,"green","green",              
          IF(% Complete=100,"blue","blue")
        )
      )
    )
  )&";'></DIV>"

You need to get your logic straight, this will work (if i get your idea right):

="<DIV style='width:100%;background-color:"&
  IF([% Complete]=100,"blue",
    IF( OR( Slippage>30, Slippage<-30) ,"red",
      IF( OR (AND(Slippage>=7,Slippage<=29),AND(Slippage<=-7,Slippage>=-29)),"orange",
        "green"
      )
    )
  )&";'></DIV>"

It now reads:

if (`% complete=100`) then `blue` else
{
  if (slippage > 30 || slippage< -30) then `red` else
  {
     if ( 7<=slippage<=29 || -29<=slippage<=-7 ) then `orange` else
     {
       `green`
     } 
  }
}

For complex column-names (containingspaces) you need to write them between brackets: [name with space]
Remove linebreaks and spacing before pasting in SharePoint.


tip: use 4 spaces to indent code and it will allow < and >

share|improve this answer
    
Thanks very much - looks more logical but ti doesn't seem to be showing as Blue when % Complete = 100 - is this possible? I kind of need that to override the other formulae –  user1956 Dec 7 '10 at 7:20
    
Don't forget to vote on good answers :) –  Dribbel Dec 13 '10 at 8:40
    
The first test in the set is for complete=100, thus that should trigger the blue color. Is the column name correct? Is the % realy 100 (or almost and rounded up?) Try it with only the first if-statement. Try building your formula in parts (start with a simple one and expand on that) –  Dribbel Dec 13 '10 at 8:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.