Max of Children Formula

Hi Formula Friends -

I'm trying to calculate the "next test date" based whether a cell is "Surveillance (Monthly)" or "Baseline (Initial)" or "Response (Weekly)".

In the formula, it must take into account the following:

If Test Strategy in Parent Row = Surveillance (Monthly) or Baseline (Initial), then the Next Test Date should be = Max(Children([Test Date]8 + 30 days)

If Test Strategy in Parent Row = Response (Weekly), then the Next Test Date should be = MAX(CHILDREN([Test Date]8 + 7 days)

The formula I have is currently: =IF(OR([Test Strategy]8 = "Surveillance (Monthly)", [Test Strategy]8 = "Baseline (Initial)", MAX(CHILDREN([Test Date]8) + 30), MAX(CHILDREN([Test Date]8 + 7))

How can I fix this?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are parenthesis issues throughout. Try this...

    =IF(OR([Test Strategy]8 = "Surveillance (Monthly)", [Test Strategy]8 = "Baseline (Initial)"), MAX(CHILDREN([Test Date]8)) + 30, MAX(CHILDREN([Test Date]8)) + 7)

    You forgot to close off your OR function, and then you needed to adjust the parenthesis so that you are adding the number of days to the result of the MAX function.

    MAX(CHILDREN([Test Date]8)) + 30


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!