HTML Calculated Column Formula

Question

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

Answer 1

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 >