IFError

Anthony Barthelemy

Good Day Smartsheet Community:

Can I add IFError to this Formula? I tried several times but I keep getting an incorrect or Unparseable

=ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2)

Thanks

Paul Newcome

If you just want it to produce a zero if that error happens, you would do something like this...

=IFERROR(ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2), 0)

Anthony Barthelemy

Thanks, that worked. I had my parenthesis in the wrong place.

I have another Formula Issue. If I use 0.5 (half Day) for Duration, my % Complete (Planned) jumps to 200%. However, it works just fine with whole numbers (i.e, 1 Day).

=IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0)

Any idea on whats causing that?

Paul Newcome

If you are establishing a half day duration, exactly when would you want to mark a task as 100%? That's the problem here. Smartsheet does not currently calculate time which is what you would need. if you were wanting the % Complete to change over the course of the day.

If you are wanting it to just cap out at 100% for those half day durations, then you could use an IF statement to say that if the original formula produces a number greater than 1, then output a 1, otherwise output the number the formula produces.

=IF(original formula > 1, 1, original formula)

=IF(IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0) > 1, 1, IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0))

Paul Newcome

@Ray B At the bottom of each post should be a small blurb asking whether or not the Comment answered your question (see screenshot below). Please disregard the formula in the screenshot. That is from another post. You can only select an "Accepted Answer" on posts that you created, so I wasn't able to capture that in this thread. I only showed the main body of the post so that you can see where it should be located.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Paul Newcome

Where are you trying to add it, and what do you want the end result to be? It can be added in a few different places within your provided formula.

Anthony Barthelemy

Not sure, I am trying to prevent the divide by zero error in my Parent / Summary Task

Paul Newcome

If you just want it to produce a zero if that error happens, you would do something like this...

=IFERROR(ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2), 0)

Anthony Barthelemy

Thanks, that worked. I had my parenthesis in the wrong place.

I have another Formula Issue. If I use 0.5 (half Day) for Duration, my % Complete (Planned) jumps to 200%. However, it works just fine with whole numbers (i.e, 1 Day).

=IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0)

Any idea on whats causing that?

Paul Newcome

Excellent. Happy to help! 👍️

Please don't forget to mark the most appropriate response to your original post as the "Accepted Answer" so that others know a solution has been found.

Paul Newcome

In regards to your new question:

Yes. It is because at that point you are dividing by a fraction.

1 divided by 1 = 1

1 divided by 2 = 1/2

1 divided by .5 = 2

You start going in the opposite direction.

What exactly are you trying to accomplish?

Anthony Barthelemy

I want to reflect the Duration Accurately and continue to measure Progress. Right now I use whole numbers even if the Task will only take half day to complete (0.5).

Paul Newcome

If you are establishing a half day duration, exactly when would you want to mark a task as 100%? That's the problem here. Smartsheet does not currently calculate time which is what you would need. if you were wanting the % Complete to change over the course of the day.

If you are wanting it to just cap out at 100% for those half day durations, then you could use an IF statement to say that if the original formula produces a number greater than 1, then output a 1, otherwise output the number the formula produces.

=IF(original formula > 1, 1, original formula)

=IF(IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0) > 1, 1, IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]2, TODAY() <= [Finish Date (Planned)]2), ROUND(NETWORKDAYS([Start Date (Planned)]2, TODAY()) / Duration2, 2), IF(TODAY() > [Finish Date (Planned)]2, 1, 0)), 0))

Anthony Barthelemy

OK, that should work until Smartsheet add Time Calculations and Earned Value.

Thanks

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response to your original post as the "Accepted Answer" so that others know a solution has been found.

Anthony Barthelemy

I am not seeing where I can select Accepted Answer

Paul Newcome

@Ray B At the bottom of each post should be a small blurb asking whether or not the Comment answered your question (see screenshot below). Please disregard the formula in the screenshot. That is from another post. You can only select an "Accepted Answer" on posts that you created, so I wasn't able to capture that in this thread. I only showed the main body of the post so that you can see where it should be located.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Anthony Barthelemy

Got it! I was looking for the words literally.

Paul Newcome

Haha. No worries.