Calculate Manpower Needed for month based on different interval schedules
I'm trying to calculate the total "Monthly Manpower Needed" for several lines that have different intervals.
So every item that has 30 days listed, I want to calculate (time X manpower), for 60 days I need (time X manpower) *.5, for 90 days I need (time X manpower)*.33.
Right now I'm using a filter and just plugging in the equation I need for each based on intervals.
Answers
-
It looks a bit silly but it may be what you are needing. It is just "nested" IF statements.
=IF(Statement 1, "Answer One", IF(Statement 2, "Answer Two", IF(Statement 3, "Answer Three")))
=IF(Days@row = 30, Days@row, IF(Days@row = 60, Days@row * 0.5, IF(Days@row = 90, Days@row * 0.33)))
I would also recommend "locking" the Manpower column and also "Converting (the formula) to a Column Formula".
Hope this helps!
Heath Hilton
-
Thank you Heath!
It works for me for 2 items, but can't get it to work with 3 or more.
=IF([PM Intervals (Days)]@row = 30, ([Estimated PM Time (HR)]@row * [Manpower #]@row), IF([PM Intervals (Days)]@row = 60, (([Estimated PM Time (HR)]@row * [Manpower #]@row) * 0.5)))
^^Works*
=IF([PM Intervals (Days)]@row = 30, ([Estimated PM Time (HR)]@row * [Manpower #]@row), IF([PM Intervals (Days)]@row = 60, (([Estimated PM Time (HR)]@row * [Manpower #]@row) * 0.5)), IF([PM Intervals (Days)]@row = 120, (([Estimated PM Time (HR)]@row * [Manpower #]@row) * 0.25)))
^^Doesn't work. :(
-
It looks like you just went a bit heavy on the parenthesis. Try this one.
=IF([PM Intervals (Days)]@row = 30, [Estimated PM Time (HR)]@row * [Manpower #]@row, IF([PM Intervals (Days)]@row = 60, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.5, IF([PM Intervals (Days)]@row = 120, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.25🚩)))
It looks like you dropped 90 out of the formula but it isn't too bad if you want to put it back in. You would just insert it in between the "0.25" and ")))". Here is the formula with 90 back in there. I am marking where you would insert the additional portions (above with the red flag) and the two flags below that are marking the beginning and end of the extra formula parts.
=IF([PM Intervals (Days)]@row = 30, [Estimated PM Time (HR)]@row * [Manpower #]@row, IF([PM Intervals (Days)]@row = 60, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.5, IF([PM Intervals (Days)]@row = 120, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.25🚩, IF([PM Intervals (Days)]@row = 90, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.33)🚩)))
Also, here is the final formula without the fun bonus symbols but with 90 in there.
=IF([PM Intervals (Days)]@row = 30, [Estimated PM Time (HR)]@row * [Manpower #]@row, IF([PM Intervals (Days)]@row = 60, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.5, IF([PM Intervals (Days)]@row = 120, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.25, IF([PM Intervals (Days)]@row = 90, [Estimated PM Time (HR)]@row * [Manpower #]@row * 0.33))))
Hope this helps!
Heath Hilton
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!