SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:… – Stuart Pegg Jan 12 '12 at 17:57

I reformatted your formula to:

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

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",

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

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

Your Answer


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