Forum Discussion
Help in producing employee capacity pie-chart
Hi.
I'd like guidance on creating a data sheet with linked pie-charts that reflect employee capacity, to help me see at a glance who has/hasn't capacity to take on further projects and who is at maximum capacity. I'll try to break down my requirements:
- Each project would be assigned a point value between 0.5 and 3, with the former being a small project and the latter being a large project.
- The maximum capacity would be a point total of 9.
- There should also be the option to add/subtract points where there are mitigating factors affecting the size of the project.
- Ideally, I would like the pie chart to change from green, to amber to red depending on how close to capacity each employee is.
I have some basic grasp of formulas but would probably need most things spelling out. I've mocked up and attached a very rough prototype to to try to support my dodgy explanation.
Thank you.
3 Replies
stusam Perhaps you want to consider using Conditional Formatting (CF), rather than pie charts. Especially because you want colors to change based on the numbers in the chart.
I have attached a file with just two possible CF rules, and there are many more.
Thank you Riny_van_Eekelen . This looks like it would certainly do the trick. I was overcomplicating with the pie chart.
Could I ask, would it be possible to format the gradients to reflect the increase in workload 0-3 = Green gradient, 3.5-6 = amber gradient, 6.5-8.5 = red gradient, 9 = solid red? I attempted to do this in cell F5 of this https://myntuac-my.sharepoint.com/:x:/g/personal/stuart_samuels_ntu_ac_uk/EZae-qETVf1Lrf2hQnrjDGkBfz-vpBcft6CoXYrra47a_w?e=kHyxEF you sent, but it doesn't seem to have any effect. Would you be kind enough to advise?
Thanks again,
Stuart.
stusam I'm afraid it doesn't work like that. You can only have one data bar per cell, as far as I understand.
