Adding Nested IF Statement to Formula

MelG

I have a formula that calculates the "Planned % Complete" based on start and end date, with no weighting. We use it as a general guide because there is no planned % complete provided by Smartsheet.

Formula: =IF(Start14 >= TODAY(), 0, IF(Finish14<= TODAY(), 1, (NETWORKDAY(Start14, MIN(TODAY(), Finish14)) / (NETWORKDAY(Start14, Finish14)))))

This has been working fine but I would like to add an element that averages the children rows at the parent level as calculating at the parent level is a bit skewed. 

Any ideas on how I can add an additional IF statement that says IF parent row, avg children, else calculate the above formula?

Robert S.

Hello MelG,

 

Thanks for the question. Since this statement should be checked first, it needs to be put at the beginning of the formula. There isn't a function that would say a cell "is a parent", however if you do a COUNT(CHILDREN( and it's greater than 0 then that means it's a parent. You can utilize that in order to make this possible. Here's an example of how this could look:

 

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Start@row >= TODAY(), 0, IF(Finish@row <= TODAY(), 1, NETWORKDAY(Start@row, TODAY()) / NETWORKDAY(Start@row, Finish@row))))

 

I changed the references in your original formula to make it slightly more efficient using @row. The @row feature will replace the row number in a cell reference that's in the same row as the formula. More on this feature can be found in this help center article (https://help.smartsheet.com/articles/2476491#row). I also slightly changed the last portion of the formula as the section that was MIN(TODAY(), Finish14) isn't needed, since earlier in the formula would have already caught rows where the finish date is before today's date therefor this part of the formula will always use today's date.