Sum of hours from various cells and subsequent calc of FTE based on certain criteria
Hello,
I would like to ask for help. I've got a sheet that can automatically calculate the FTEs of people assigned to projects but I need a little tweaking. There is a current layout:
Currently, the FTEs are calculated based on what region is the global lead and that takes into account the "Lead MM - MDR Oversight Hours" column as follows (with appropriate changes in formulas for different regions):
=IF([Global Lead]@row = "NA", ([MM hours (NA)]@row + [Lead MM - MDR Oversight Hours]@row) / (2080 * [Duration (Years) (hidden)]@row), [MM hours (NA)]@row / (2080 * [Duration (Years) (hidden)]@row))
What I only realised is that if the "Assigned MDR" = "Assigned Lead MM" and/or "Assigned MSR" = "Assigned Lead MM", the respective "MDR Hours Grand Total" and "MSR Hours Grand Total" need to be put into equation.
I'm not sure how to formulate it - can you please advise?
Answers
-
How exactly would you want them incorporated. The basic idea is using an IF statement nested in, but the exact syntax is going to be based on how it needs to be included.
-
So the idea is to add that if Assigned MDR = "Assigned Lead MM" AND/OR Assigned MSR = "Assigned Lead MM", it will take those respective hours into account. But if Assigned MDR or Assigned MSR =/= "assigned Lead MM", then those hours would not be pulled into equation.
Eg in the screenshot: I'd want the formula to pull (555+124+158+202)/(2080 * [Duration (Years) (hidden)]@row
But if e.g. Assigned MDR = "John Smith" (currently the 158hrs), I'd want the formula only to pull (555+124+202)/(2080 * [Duration (Years) (hidden)]@row , without Hours allocated to John Smith
-
Ok. SO if I understand you correctly, you are wanting to modify this portion:
([MM hours (NA)]@row + [Lead MM - MDR Oversight Hours]@row)
To include MDR Hours Grand Total and/or MSR Hours Grand Total depending on whether or not one or both of them is "Assigned Lead MM"?
This is incorporating some basic IF statements and "adding" the outputs.
([MM hours (NA)]@row + [Lead MM - MDR Oversight Hours]@row + IF([Assigned MDR]@row = "Assigned Lead MM", [MDR Hours Grand Total]@row) + IF([Assigned MSR]@row = "Assigned Lead MM", [MSR Hours Grand Total]@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!