Using completion percentage and a if statement.

Jesse Putzier

Hello, I am trying to do a if statement to subtract a value if my completion percentage is 100%. Here is my formula.

=SUM(Duration5, Duration7, Duration10, Duration12, Duration15, Duration17, Duration20, Duration22, Duration25, Duration27, Duration30, Duration32, Duration35, Duration37, Duration40, Duration42) - IF([% Complete]5 = 1, 1) - IF([% Complete]10 = 1, 1) - IF([% Complete]15 = 1, 1) - IF([% Complete]20 = 1, 1) - IF([% Complete]25 = 1, 1) - IF([% Complete]30 = 1, 1) - IF([% Complete]35 = 1, 1) - IF([% Complete]40 = 1, 1) - IF([% Complete]7 = 1, 3) - IF([% Complete]12 = 1, 3) - IF([% Complete]17 = 1, 3) - IF([% Complete]22 = 1, 3) - IF([% Complete]27 = 1, 3) - IF([% Complete]32 = 1, 3) - IF([% Complete]37 = 1, 3) - IF([% Complete]42 = 1, 3)

Is there a easier way to input this such as if any of the selected cells =1 then subtract 1 or is this the only way to do this calculation. I have other line items that there are a lot more of and it would eat a lot of time to manually type it in this way.

David Joyeuse

Hi @Jesse P

I'm not sure I understand what you're trying to do here quite well, but it seems you want to add the duration of every Torque and Wood PE tasks that aren't completed tasks. But in this cse I don't get why you subsract 3 from Wood PE Tasks, while the duration in your screen shows 2.

Anyway, I would suggest using a SUMIFS statement. That ould be way easier.

=SUMIFS(DESCENDANTS(), DESCENDANTS([Task Name]@row), OR(@cell= 'Torque', @cell='Wood PE'), DESCENDANTS(Duration@row), <>1)

Should do the trick.

If I got it wrong, let me know.

Paul Newcome

You can replace the SUM function like so:

=SUMIFS(Duration:Duration, [Task Name]:[Task Name], OR(@cell = "Torque", @cell = "Wood PE"))

Then you can replace the % Complete part with this...

=SUMIFS([% Complete]:[% Complete], [% Complete]:[% Complete], @cell = 1, [Task Name]:[Task Name], OR(@cell = "Torque", @cell = "Wood PE"))

Subtracting the second from the first will give you this:

=SUMIFS(Duration:Duration, [Task Name]:[Task Name], OR(@cell = "Torque", @cell = "Wood PE")) - SUMIFS([% Complete]:[% Complete], [% Complete]:[% Complete], @cell = 1, [Task Name]:[Task Name], OR(@cell = "Torque", @cell = "Wood PE"))

If you did in fact mean to subtract 3 for every "Wood PE", we would need to modify the second bit a touch, but it is still the same concept.

Jesse Putzier

Hello Paul,

Yes I am basically tracking number of required hours and then subtracting those hours when a task is complete. Thanks I will try out this method this morning.

All the best.

Jesse Putzier

I was trying to use

=SUMIFS(Duration:Duration, [Task Name]:[Task Name], OR(@cell = "Torque"))

and

=SUMIFS(Duration:Duration, [Task Name]:[Task Name], OR(@cell = "Wood PE"))

When they are on their own they calculate correctly but when I try to them in their own individual cell I get a circular and blocked annotation. What am I missing?

Paul Newcome

When you are using them based on only a single requirement as in your most recent post, you actually do not need the OR function.

=SUMIFS(Duration:Duration, [Task Name]:[Task Name], @cell = "Torque")

I'm not sure I follow though when you say

"...but when I try to them in their own individual cell..."