Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
