Need Resource Deficiency Formula corrected

Hi there, Im wanting to track resource deficiency on my projects. I'm using Capacity planning templates for smartsheet and the deficiency formula is not giving me a correct formulation. Given that

  1. Total project Demand (HRS) = Sum of all hours per week from Level of Effort column
  2. Utilization Rate is Level of effort (HRS/Week) / Work Hours per Week *100 = in % (Averaged %)
  3. Effective capacity (HRS) = [Total Employees]# * [Work Hours per Week]# * [Utilization Rate (%)]#
  4. Potential Work Capacity=[Total Employees]# * [Work Hours per Week]#
  5. Resource deficiency formula i used is =ROUNDUP(IF([Total Project Demand (Hrs)]# > [Effective Capacity (Hrs)]#, ([Total Project Demand (Hrs)]# - [Effective Capacity (Hrs)]#) / ([Work Hours per Week]# * [Utilization Rate (%)]#), 0), 0)

If my work capacity (available) is 200 hrs and total projet demand is 100 hrs. Why is the Resource deficiency field showing I need 15 more staffers?

Really appreciate a quick turnaround on this matter. Thanks.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!