IFError
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
Best Answers
-
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)
-
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?
-
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))
-
@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.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Answers
-
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.
-
Not sure, I am trying to prevent the divide by zero error in my Parent / Summary Task
-
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)
-
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?
-
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.
-
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?
-
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).
-
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))
-
OK, that should work until Smartsheet add Time Calculations and Earned Value.
Thanks
-
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.
-
I am not seeing where I can select Accepted Answer
-
@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.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
Got it! I was looking for the words literally.
Help Article Resources
Categories
Check out the Formula Handbook template!
