HTML Calculated Column Formula

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

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

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 >

• 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