Return a Minimum Date with A Condition

Emma W

Hi there

I am trying to adjust summary formulas so that they only return information from rows considered In Scope (so that data does not have to be removed from Not In Scope rows).

Can anyone help with a formula that would return the MIN date from this group, only for the rows In Scope, ie Out of Scope is checked / true.

Thanks in advance

Emma

Paul Newcome

You will need to incorporate the COLLECT function.

=MIN(COLLECT(CHILDREN(), CHILDREN([Not In Scope]@row), @cell <> 1))

Emma W

Thanks so much for coming back to me Paul. Is there a reason why this returns no value?

Paul Newcome

Do you have any blanks in any of the child rows? Technically a blank is "less than" a date, so the MIN function would return the blank. to avoid this, we can add another range/criteria set to the COLLECT function to ignore blanks and only pull from cells that are not blank.

=MIN(COLLECT(CHILDREN(), CHIDLREN(), @cell <> "", CHILDREN([Not In Scope]@row), @cell <> 1))

Emma W

Hi Paul

No blanks, but I do get a different result with that formula this time - #UNPARSEABLE.

To confirm, what I want the formula to do is return the Min Scheduled Start Date to me for [Pre requisites of the Workshop], but only for In Scope Items, ie all those not checked [Not In Scope], so I would expect the result to be 05/08/21.

Am I asking the question the wrong way around? I didn't want to make [Not In Scope] [In Scope] because not many things will be Out of Scope.

Thank you for your help with this.

Emma W

I think I've cracked it!

=MIN(COLLECT(CHILDREN(), CHILDREN([Not In Scope]@row), @cell = 1 = 0))

Paul Newcome

The way I was writing it with "@cell <> 1" is basically saying the boxes that were not checked. I am not sure why it was not working for you as I have used this quite a few times in the past.

I'm glad you got it figured out though!