At the outset, let me thank How-to Make an Excel Project Status Spectrum Chart from where I learnt this kind of chart and inspired to set this challenge. My challenge is almost the same except that this is not spectrum but three distinct range of RAG are there.
Below is a possible solution to Challenge 25 – Prepare a Project Status Chart with Sliders.
Let's assume that we have a table like below for Project Status where 0 (Minimum) is towards Red and 10 (Highest) is towards Green.
We need to prepare a table like below with Green, Red, Amber and put a constant value of 5 for these (You can give any value. I have taken a value of 5 just for the convenience. The final graph's bar width etc will look good or bad as per these values). The formula for Final Rating is =SUM(C2:E2)*B2/10-G2 and 0.25 is the width of slider. You can adjust the width as per your requirement.
Note – In the formula =SUM(C2:E2)*B2/10-G2, importance of factor 10 is that all the parameters can be given a maximum value of 10.
After having prepared the table, we need to take following steps –
1. Select A1:G7 > Insert tab > Stacked Bar Chart > You get a chart like below –
2. Right Click on Chart > Select Data > Switch Row and Column > You get a chart like below
3. Select the Rating Bar and press delete to get rid of Rating Series. Now, you get following chart –
4. Select Final Rating bar > Right Click > Format Data Series > Secondary Axis > Gap Width – 50% > Fill Color as No Fill > You will get following chart
5. Select Size bar > Right Click > Format Data Series > Secondary Axis > Gap Width – 50% > Fill Color as Black > You get following chart
6. In the Legend Box, double click Red and choose Fill Colour as Red. Double click Amber in Legend Box and choose Fill Colour as Amber. Double click Green in Legend Box and choose Fill Colour as Green. Now, you get following chart.
7. Change the Chart Title > Delete the top and bottom x axis. Delete the legend. Remove vertical bars which are part of the graph. Make y axis bold and do other required formatting. Now, you get the required chart which is final.
An Excel workbook illustrating the above solution has been uploaded at following location Solution – Challenge 25 – Prepare a Project Status Chart with Sliders