Visualization – calculated color gradients..by Mark Milner

If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.

 

Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting - late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.

So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.

Method 1: nested IFs

This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.

Method 2: CHOOSE function

The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.

Method 3: pure calculation

So, how can we go even further? Well, colors can be identified by their name, but also by theirrgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)

Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)

The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”

To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:

1
="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"