Sum of hours from various cells and subsequent calc of FTE based on certain criteria

Michaela Kamenska

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?

Paul Newcome

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.

Michaela Kamenska

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

Paul Newcome

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))