How to indicate that a cell will be automatically calculated?

Jon Griffin

Ultimately, I want to indicate to users when a cell will be automatically calculated, so they don't overwrite the formula. I've figured it out in one case, but not in another. Both are shown below.

So, more specifically, any ideas on how to detect a blank cell when using =MAX(DESCENDANTS()) in a Date column?

I have a Project Header row with % Complete and End Date columns. I do not want dependencies turned on because I have multiple levels of indentation and only want the Project Header (top parent) row to have this behavior.

In the % Complete column, I have =IFERROR(AVG(DESCENDANTS()), "[Auto Calculated]") This works great because AVG(DESCENDANTS()) generates an error when all the descendants are blank.

In the End Date however, =MAX(DESCENDANTS()) returns a blank value/cell (no error) if there are no dates in the descendant rows, so I can't detect it with IFERROR().

The IF() function doesn't seem to evaluate DESCENDANTS() = "" or ISBLANK(DESCENDANTS()). For example, the following formulas produce, "#INCORRECT ARGUMENT SET"

=IF(MAX(DESCENDANTS() = "", "[Automatic]", MAX(DESCENDANTS())))

=IF(ISBLANK(DESCENDANTS(), "[Automatic]", MAX(DESCENDANTS())))

Thanks for any ideas!

Jon Griffin

Thanks for the ideas, guys. With your help, I figured it out!

This formula works for what I need:

=IF(COUNT(DESCENDANTS()) = 0, "[Auto Calculated]", MAX(DESCENDANTS()))

@Eric M Oliveira, yes, it is a Date column.

Eric M Oliveira

Hello,

Happy to help. If you're utilizing =MAX(DESCENDANTS()) to locate the max date value you'll want to make sure the formula is in a Date column or it will produce an error. =MAX(DESCENDANTS()) works when it is in the Parent row of the Date column or when it references the Parent row of a Date column, =MAX(DESCENDANTS(Date1)). To not receive and error you will want to ensure that the column type that the formula is in matches the column type of the values it is referencing. 

If you would like to produce an "[Automatic]" value if all descendants are blank but MAX(Descendants()) if not, you can achieve this utilizing an IF function in combination with a COUNT and COUNTIF function. The formula would look similar to this.

=IF(COUNT(DESCENDANTS()) = COUNTIF(DESCENDANTS(), " "), "[Automatic]", MAX(DESCENDANTS()))

The above formula checks the total amount of cells against those that are blank, if they are equal (all blank) it produces the desired text result, if not it produces the max descendant.

Have a wonderful day. 

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

@Eric M Oliveira I don't think this will work:

=IF(COUNT(DESCENDANTS()) = COUNTIF(DESCENDANTS(), " "), "[Automatic]", MAX(DESCENDANTS()))

More specifically the portion where you compare the count of the descendants to the count that are blank. The COUNT portion will not count blanks, so if all are blank, this part will return a zero. But then when you move to the COUNTIF portion to count how many are blank, you will generate an actual number. This means that if all are blank, you will not have a matching count in which case it will not trigger the "[Automatic]" output.

Referencing the below screenshot... The second column where you see 0 is the formula of "=COUNT(DESCENDANTS())" and the third column with a 6 is the formula of "=COUNTIFS(DESCENDANTS(), "")". As you can see, even though all are blank, they do not produce the same number.

You would instead want to compare the count of blanks to the count of a different column that will have data in every cell (possibly the [% Complete] column or a [Task Name] column.

=IF(COUNT(DESCENDANTS([% Complete]@row)) = COUNTIF(DESCENDANTS(), " "), "[Automatic]", MAX(DESCENDANTS()))

Jon Griffin

Thanks for the ideas, guys. With your help, I figured it out!

This formula works for what I need:

=IF(COUNT(DESCENDANTS()) = 0, "[Auto Calculated]", MAX(DESCENDANTS()))

@Eric M Oliveira, yes, it is a Date column.

Paul Newcome

@Jon Griffin I just took another look at your original post. Your original formulas should have worked with one exception (on both). You had a misplaced closing parenthesis. You forgot to close out the MAX/ISBLANK functions.

You have

MAX(DESCENDANTS() =

ISBLANK(DESCENDANTS(),

when it should have been

MAX(DESCENDANTS()) =

ISBLANK(DESCENDANTS()),

And then removing a closing parenthesis from the end. That would be where the #INCORRECT ARGUMENT SET error was coming from. My apologies for missing that the first time.

Please don't forget to mark your solution as "helpful" so that it gets flagged as the "best answer" since that is the solution you used. This way others searching for a similar solution can know that one may be found here.