1

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>"
Improve this question
1

1 Answer 1

Reset to default
2

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 >

Improve this answer
3
  • 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 Commented Dec 7, 2010 at 7:20
  • Don't forget to vote on good answers :) Commented Dec 13, 2010 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) Commented Dec 13, 2010 at 8:53