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?

Find more posts tagged with

Manufacturing

Formulas

IT & Operations

Comments

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

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

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

Many thanks.

Jonathan Chan

Travis, Luis, J Craig:

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

Best.

Jon