#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Using formula =MAX(children())

edited 12/09/19

Dears:

I'm talking about one Date formated column. When my Parent cell has that formula and I have 4 children's (2 filled and 2 unfilled) the formula returns the MAX value between the 2 filled cells. I want for my Parent the MAX from all four childrens. I need to return the MAX value only when the 4 childrens are filled. What can I do in order to avoid any result while there are any children without information?

• ✭✭✭✭✭✭

Luis,

Will you always know how many children there are (filled and unfilled)?

Assuming the answer is "yes - always 4"  then

=IF(COUNT(CHILDREN()) = 4, MAX(CHILDREN()), "Unfilled remain")

The formula for "no" is different and longer, but also possible.

Hope this helps.

Craig

• Employee

Try this:

=IF(COUNTIF(CHILDREN(), "") + COUNT(CHILDREN()) = COUNT(CHILDREN()), MAX(CHILDREN()))

This will count the number of children that contain a date (COUNT(CHILDREN())) and add it to the number of children that are blank (COUNTIF(CHILDREN(), ""). If this number is the same as the count of children that contain a date (meaning they all contain a date) then it will show the max. This will work regardless of the number of children.

Note - this will look for any value in the children, not just dates.

Enjoy!

• edited 03/03/16

Thank you both. I'll keep the Travis solution because it's more generic.

Many thanks.

• Travis, Luis, J Craig:

Thank you for the discussion, it enabled me to resolve my roadbloack as well.

Best.

Jon

This discussion has been closed.