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)&"%;'> </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)&"%;'> </DIV><DIV style='position:absolute; top:0px;'>"&TEXT([%],"0%")&"</DIV></DIV>"
For other examples of conditional formatting, check this post about color coding.
Pingback: Links for 10/23/08 « Steve Mullen’s Blog
Pingback: Websites tagged "progressbar" on Postsaver
Pingback: Printable progress bars « Path to SharePoint
Thanks! It looks great!
who can you do that for the gantt view? say you have a task that pass the due date, and still not compeled?
Effie, see if one of these two examples inspires you:
Pingback: Visualization – calculated color gradients « Path to SharePoint
Pingback: Case study: course compliance report (Part II) « Path to SharePoint
How can I add background image instead of background color
point 1) How can I adjust my image width depend on a value
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?
what type of column would i be creating? calculated,lookup,etc
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!
Double check your spelling, it seems that you didn’t type the column name correctly.
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.
thanks in advance
your the man
Sorry Aabed, but we people working on collaborative tools hate to duplicate content! To learn more about this technique, you can follow the tutorial here:
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?
I would check the format of your column (text or number? If number, how is the % expressed? (90 in % units is not the same as 0.90)
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.
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?
I had to do it like this to get it working:
=” “&TEXT([% Complete],”0%”)&””
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%.
David, To get the left align working i added this at the start:
So my script looked like: