Progress bar + color coding

Posted on by

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(&#91;%&#93;*10)+1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&(&#91;%&#93;*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(&#91;%&#93;*3)+1,"red","Orange","Lime","Lime")&"; width:"&(&#91;%&#93;*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.

24 thoughts on “Progress bar + color coding”

  1. Pingback: Links for 10/23/08 « Steve Mullen’s Blog

  2. Pingback: Websites tagged "progressbar" on Postsaver

  3. Pingback: Printable progress bars « Path to SharePoint

  5. Good job!
    who can you do that for the gantt view? say you have a task that pass the due date, and still not compeled?

  6. Pingback: Visualization – calculated color gradients « Path to SharePoint

  7. Pingback: Case study: course compliance report (Part II) « Path to SharePoint

  8. How can I add background image instead of background color

    point 1) How can I adjust my image width depend on a value

  9. Kindly break this down for me, I’m a total newbie and I think I’m doing quite well. Successfully done an IF statement for color coded pictures and bullets.
    Trying to achieve a star format dashboard where the middle circle shows the entire company performance and the outer circles represent the department. I’ve got this working, I’ve successfully hidden the column headers to it looks nice but I don’t know how to add text beside the bullets. I don’t know any HTML.
    The bullets show the traffic lights but I want to show the department name and possibly the percentage if possible right beside the bullet. Can you help?

    Thanks.

  11. What am I doing wrong? I get “The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.”

    I’d love to add the 11 colour progress bar, I’ve add the CEWP and the script from the “Using calculated columns to write HTML” page. 😦 you’re advice would be grately appreciated!

  12. Dear Christophe,
    im actually new to this but ive managed to understand and do some customized web parts, but..
    i was hoping you can break this a bit to me, i dont get it! AFAIK i should put a script in a web content, but in here you dont mention anything about a script going into a web content,
    and please dont redirect me to another page just explain this point to me.

    all appreciated,

    thanks in advance
    your the man

  13. Hey Christophe,

    Thanks for this, I’ve followed a lot of your blog postings. This one though I can\’t get to work. I assume that the error another poster got was to do with you using [%], I’m guessing that should be the name of my percent complete column? [% Complete]

    If I change [%] to [% Complete]. I don’t get the error, but I don’t get any colors either. Just the text of the [% Complete] column.

    Any ideas what I should check?

    Cheers…

    John

  14. Just a wee update to my previous question. I took out the last part “&TEXT([%],”0%”)&”

    And I get the bar to display with the right colours. But first of all, no idea why it doesn’t work with that and secondly, it seems to take a few seconds before displaying the bar every time I load the page.

    Something doesn’t seem right, any help greatly appreciated.

    Cheers…

    John

  15. I can get the bar to display with the right colors and text, but my progress bar fills in from right to left. What am I doing wrong?

  17. I’m able to get this working but I have two problems:

    Although the value of % complete is aligned to the left, the bars are aligned to the right. How can I get everything to align to the left like the screenshot you posted?

    Also, when sorting/filtering by the % Complete from largest to smallest, my 0-30% is listed higher than the higher percentages. After it shows those smaller values, it then started from 100%.

    Thanks Christophe!

  18. David, To get the left align working i added this at the start:

    So my script looked like:

    =” “&TEXT([%],”0%”)&””

Comments are closed.