Calculate Manpower Needed for month based on different interval schedules

Valerie_WPA

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.

Heath Hilton

Valerie_WPA,

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".

Valerie_WPA

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. :(

Heath Hilton

Valerie_WPA,

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