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())

Luis Martini
edited 12/09/19 in Archived 2016 Posts

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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    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

  • Travis
    Travis 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!

  • Luis Martini
    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.