This post is an answer to Owen’s question about progress bars:

“How can I do conditional red, yellow & green coloring? Can’t I just do a nested IF statement? [...] I’d like the bar to move and change color, all based on the value of the % Complete column.”

Though nested IF statements are an option, in this case the CHOOSE function will provide a more elegant solution, easier to read.

The first formula below has a choice of 11 colors. The choice will be decided by “INT([%]*10)+1″, an integer function of the % complete column (0% returns 1, 100% returns 11).

Update [October 29] I have added a non-breaking space to both formulas. This is needed in Firefox.

="<DIV style='position:relative;'><DIV style='background-color:"&CHOOSE(INT([%]*10)+1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([%]*100)&"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"&TEXT([%],"0%")&"</DIV></DIV>"

Another example with only three colors:

="<DIV style='position:relative;'><DIV style='background-color:"&CHOOSE(INT([%]*3)+1,"red","Orange","Lime","Lime")&"; width:"&([%]*100)&"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"&TEXT([%],"0%")&"</DIV></DIV>"

For other examples of conditional formatting, check this post about color coding.

Advertisement