Calculating Weighted Percentage of Projects

Question

Hello,

I'm hoping someone can point me in the right direction.

I want to be able to visualize the amount of work each Project Manager has in order to best balance the workload when assigning new projects.

In order to do this, I've created a Project Tier system based on the complexity and importance of each project. I've assigned each project as either Tier 1, 2, or 3 as highlighted in the image below:

I then was able to successfully count the numbers of each tier per Project Manager in the separate metrics sheet I have:

Now from here I want to be able to utilize a kind of weighted count or average function to get a more accurate picture of the amount of work each PM has instead of just the number of projects.  So it would be something like: Projects in Tier 1 are counted once, Tier 2 counted twice, and Tier 3 counted thrice. These would all be added together separately for each PM. And then I would get a single number for each PM which I would then easily turn into a percent graph widget on our dashboard.

Hope this makes sense. Thanks for any help you can give.

sleepysapling · Answer

Thank you for the tips. I swapped the rows/columns and was able to do the SUM formula for the simple math and made my Dashboard chart. Much appreciated

Kerry St. Thomas · Answer

First thing I'd do is transpose your KPI sheet: Make the names individual rows, and then Tier 1 / Tier 2 / Tier 3 columns. This will enable scalability because you'll be able to do your lookup formulas as column formulas (meaning you won't have to rewrite as you add PMs).  It'll also enable you to create a fourth column - your weighted average. You'll be able to create formula that's essentially Tier1@row + Tier2@row*2 + Tier3@row *3 - that you can then use to create your dashboard viz.

Good luck and happy Smartsheeting!