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())
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?
Comments
-
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
-
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!
-
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
