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:
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.
Thanks for any guidance anyone can provide.
Stuart.
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.
Book1.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Douglas Quaid | ||||||||||
2 | Project title | Points | Additions | Deductions | Total | ||||||
3 | Rocket science 101 | 3 | 0.5 | 2.5 | |||||||
4 | Baking for beginners | 1 | 1 | 2 | |||||||
5 | Social media management | 2 | 0.5 | 1.5 | |||||||
6 | 6 | ||||||||||
7 | |||||||||||
8 | Alex Murphy | ||||||||||
9 | Project title | Points | Additions | Deductions | Total | ||||||
10 | Brain surgery for dummies | 3 | 0.5 | 3.5 | |||||||
11 | Productivity | 1 | 2 | 3 | |||||||
12 | Advanced marketing | 0.5 | 0.5 | ||||||||
13 | Product design | 2 | 2 | ||||||||
14 | 9 | ||||||||||
15 | |||||||||||
16 | Kyle Reece | ||||||||||
17 | Project title | Points | Additions | Deductions | Total | ||||||
18 | Innovation in design | 3 | 1 | 2 | |||||||
19 | Film production | 1 | 1 | ||||||||
20 | 3 | ||||||||||
21 | |||||||||||
22 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:E5,E10:E13 | E3 | =B3+C3-D3 |
E6 | E6 | =E3+E4+E5 |
E14 | E14 | =E10+E11+E12+E13 |
E20 | E20 | =E18+E19 |
Thanks for any guidance anyone can provide.
Stuart.